Problem with WhereClause/DefinitionExpression - SQL statement (VBA)

Anonymous User

Original User: koboldmaquis@yahoo.de
Original Date: 2012-06-08T18:03:01-0600

Hi,

my WhereClause/DefinitionExpression - SQL statement isn't working. I'm getting the values for the SQL statement out of the ComboBox3 (UserForm) via the variable StrPLZ = ComboBox3.Value. I'm trying to use this variable in the SQL statement, but i get in ArcMap the following error:

ArcMap Drawing Error
One or more layers failed to draw:
POI: An invalid SQL statement was used.

My guess is, that the programm cant query/compare the string variable (StrPLZ) with the long-datatype field of the attribute table (PLZ) (see highlighted code below, Screenshot of Table).

Can anybody please help me

'Definition Query: Suche von POI über die Postleitzahl (Name des Buttons im GUI: POI suchen)
Private Sub CommandButton3_Click()

'Connect Schnittstellen
Dim pMxDocument As IMxDocument
Set pMxDocument = ThisDocument
Dim pMap As IMap
Set pMap = pMxDocument.FocusMap
Dim pLayer0 As ILayer
Set pLayer0 = pMap.Layer(0)

'Schnittstelle wechseln (QueryInterface),
'um für die Abfrage notwendige Eigenschaften und Methoden zu erhalten
Dim pDefQuery As IFeatureLayerDefinition
Set pDefQuery = pLayer0

'Anwendereingaben aus der ComboBox3, ComboBox4 und ComboBox5 in Variable schreiben
'benötigt, damit das Programm 'wei�', welche Features (POI) angezeigt werden sollen
Dim StrPLZ As String
Dim StrOberKat As String
Dim StrUnKat As String
StrPLZ = ComboBox3.Value
StrOberKat = ComboBox4.Value
StrUntKat = ComboBox5.Value

'Variable für Message Boxen deklarieren (Name des PLZ-Bereichs)
Dim StrText As String
StrText = TextBox1.Text

'gegebenenfalls vorhandene Auswahl entfernen
pDefQuery.DefinitionExpression = ""

'Definition Query durchführen
If StrPLZ = "Bitte Postleitzahl wählen" Then
MsgBox "Sie haben keine Auswahl getroffen. Alle POI werden angezeigt", vbOKOnly + vbExclamation, "Auswahl treffen"
pDefQuery.DefinitionExpression = ""
ElseIf StrUntKat = "Optional: Unterkategorie wählen" Then
MsgBox "Alle POI in der Oberkategorie '" & StrOberKat & "' im Postleitzahlbereich '" & StrPLZ & "' " & StrText & "werden angezeigt", vbOKOnly, "Hinweis"
pDefQuery.DefinitionExpression = "PLZ = '" & StrPLZ & "'"
End If


'Refresh
pMxDocument.ActiveView.Refresh
pMxDocument.UpdateContents

End Sub

ComboBox3.Clear
ComboBox3.Value = "Bitte Postleitzahl wählen"
ComboBox3.AddItem "44795"   'Bochum-Weitmar
ComboBox3.AddItem "44797"   'Bochum-Weitmar
ComboBox3.AddItem "44799"   'Bochum
ComboBox3.AddItem "44801"   'Bochum-Querenburg
ComboBox3.AddItem "44803"   'Bochum-Steinkuhl
ComboBox3.AddItem "45527"   'Hattingen
ComboBox3.AddItem "58454"   'Witten
ComboBox3.AddItem "58455"   'Witten
ComboBox3.AddItem "58456"   'Witten-Herbede
ComboBox3.AddItem "0"       'Keine PLZ Zuordnung möglich

ComboBox4.Clear
ComboBox4.Value = "Optional: Oberkategorie wählen"
ComboBox4.AddItem "Ã?ffentliche Einrichtungen"
ComboBox4.AddItem "Wohnheime"
ComboBox4.AddItem "Nahversorgung"
ComboBox4.AddItem "Dienstleistungen"
ComboBox4.AddItem "Gesundheitswesen"
ComboBox4.AddItem "Freizeiteinrichtungen/Sport/Wellness"
ComboBox4.AddItem "Gastronomie"
ComboBox4.AddItem "Kultur/Unterhaltung"
ComboBox4.AddItem "Sonstige"

[ATTACH=CONFIG]15079[/ATTACH]

[ATTACH=CONFIG]15080[/ATTACH]

table.jpg

userform.JPG

Anonymous User

Original User: Neil

If the field type is text then the value must be enclosed in single quotes.  If the field is numeric then the value is not enclosed in single quotes.