[AccessD] Just cannot get this SQL syntax to work

Bill,

Try it like this...

DELETE FROM tblFilterList
WHERE Item Like "*[[MyField]] =*"

Regards
Steve


--------------------------------------------------
From: "Bill Benson (vbacreations)" <vbacreations at gmail.com>
Sent: Thursday, June 24, 2010 4:24 PM
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Subject: [AccessD] Just cannot get this SQL syntax to work

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