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