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...