[AccessD] Just cannot get this SQL syntax to work

Bill,

I have double-checked this, and the query you gave:

SELECT *
FROM TblFilterList
WHERE TblFilterList.Item Like "*[[MyField]] =*"

... should be correct.  It should not return all records from the table. 
Are you sure?

Using your original examples, these should be returned:
    [MyField] = "Aberdeen"
    [MyField] = #2/1/2010#
    [MyField] = 10

... and these should not be included:
    [MyField] Like "A*"
    [MyField] Between "A" AND "C"

Regards
Steve


--------------------------------------------------
From: "Bill Benson (vbacreations)" <vbacreations at gmail.com>
Sent: Thursday, June 24, 2010 4:59 PM

> Tested with a select statement instead of a delete statement,
>
> SELECT *
> FROM TblFilterList
> WHERE TblFilterList.Item Like "*[[MyField]] =*";
>
> That seems to return EVERYTHING from the table ... therefore, I have to
> conclude it does not work?
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steve Schapel
> Sent: Thursday, June 24, 2010 12:47 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [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)
>>
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.829 / Virus Database: 271.1.1/2957 - Release Date: 06/23/10
> 02:36:00
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com