iQueryFilter.whereclause problem (VBA Dinosaur content)

Discussion created by ed_rollason on Sep 30, 2011
Latest reply on Oct 3, 2011 by ed_rollason
Hi all,

I am aware that VBA is dead but I am stuck using Arc 9.2 and the project is quick and dirty so I'm going with what I know. Also apologies if this is in the wrong forum, I haven't posted since the old forums for discontinued.

The Background:
I have a personal geodatabase in which there is a point featureclass with a unique text identification field called "Asset_ID". Also within the pDB is a large table containing the details of the assets, also with an Asset_ID field. The FC is joined to the table using this field.

I need my user to be able to select a point within the GIS and then delete the point AND the associated data record using VBA (there are some other tools associated with the data which are all in VBA so I'm sticking to what I know).

Using various threads in the old forum and my own experience I can delete the selected record and I have written code to get the Asset_ID value of the selected feature and then access the joined table. I am attempting to use iQueryFilter.Whereclause to select the matching record from the joined table using the SQL query and passing it the Asset_ID value from the selected feature as a string variable.

The problem:
The code works fine when I feed it a hard-coded string variable (for example ND_001). But when I try and replace the string with the variable the query returns FALSE and it selects everything for deletion.

I have checked the variable value and this is correct, and it is definitely looking at the correct table, which does contain a field called "Asset_ID". I am therefore assuming that there is something wrong with my SQL query - this is backed up by the fact that giving it a string value works.

Could anyone help?

I attach the full code. However, the query is below:

' Make the query filter
Set pQueryFilt = New QueryFilter

pQueryFilt.WhereClause = "Asset_ID = '" & AssetID_Val & "'"
' Asset_ID is the name of the field - this is a text field
' AssetID_Val is the variable - this will be a text string e.g. "ND_001"
' the SQL is copied from a previous thread at present but I have tried several with no success!


' Perform the selection
pTableSel.SelectRows pQueryFilt, esriSelectionResultNew, False

' Report how many rows were selected
Set pSelSet = pTableSel.SelectionSet
MsgBox pSelSet.Count

If pSelSet.Count = 0 Then
MsgBox "Unable to delete background data - no matching record found"
Exit Sub
End If

Set pTable = pStdAloneTbl
pTable.DeleteSearchedRows pQueryFilt