[AccessD] Just cannot get this SQL syntax to work

Bill Benson (vbacreations) vbacreations at gmail.com
Wed Jun 23 23:24:13 CDT 2010


I hope I can make this question understandable. I have a table called
TblFilterList, which has only one field, Item. It holds SQL statements such
as  [MyField] = "ABERDEEN"

When I change a combobox's value on my form, such as one named
SITE_STATION_NAME, I build a SQL statement which has the text,

	Delete from tblFilterList 
      where Item Like "[MyField] =*"

This does not work. I made a select query in the design window to try to fix
the syntax and get it to work, and I cannot.

Pls help me write the sql syntax which will delete any records such as

	[MyField] = "Aberdeen"
	[MyField] = #2/1/2010#
	[MyField] = 10

In other words , Like   [MyField] =*

A little more background:
I use the records in Item column of TblListFilters to build a larger WHERE
clause for use in all the other comboboxes' Rowsource properties; this has
proved successful in making them all interdependent without making them
cascading according to any particular order. The operation is performed in
the AfterUpdate event.

What I want to do is clear out any record in TblListFilters specifying that
given control's contribution to the master WHERE clause which will get
built. 

The reason I am trying

	Delete from tblFilterList 
      where Item Like "[MyField] =*"

is because there are other filters in TblListFilters based on other
operations for the same fields, which do not use the equal sign, such as

     [MyField] Like "A*"
     [MyField] Between "A" AND "C"

I don't want every reference to [MyField] deleted, I only want any those
which specify an exact value to be removed.

After I remove the references which specify an exact value, I will then
insert a new record into TblFilterList based on the new value in the Combo,
using sql that depends on the fieldtype, such as (varies with combo of
course)

Insert Into TblFilterList [Item] Values ('[SITE_STATION_NAME] = "ABERDEEN"')
Insert Into TblFilterList [Item] Values ('[SHIP_DATE] = #1/1/2010#)
Insert Into TblFilterList [Item] Values ('[Units] = 4)

Etc...




More information about the AccessD mailing list