correct syntax like or contains ??

[Tammy]

Hello smart people!

I have a query that i'm trying to filter down.

I am looking for everything in column notes that contains subscriber;
however some of my notes fields say more than subscriber; some of them say
things like subscriber in louisiana; how can i write my query to where it
picks up each record that has the word subscriber in it? the word "contains"
didn't work and the word "like" only picked up where the only word in the
record is subscriber.

 

[Rob Parker]

Like "*subscriber*"

Rob

 

[B.O.B.]

This might depend on the version of Access that you're using. I'm responding
for Access 2003 and earlier. You have the right idea, just a little tweak to
the syntax should get you going. Assuming you're using Access with a Jet
database, the syntax would be:

Note Like "%subscriber%"

in your SQL query's WHERE clause. In the query designer grid it would be:

Like "*subscriber*"

The query designer in Access translates the * to % behind the curtain for a
Jet database.

Hope this helps.

 

[Tammy]

thank you.

then along those same lines; how do i make it not print those subscribers on
another query where the expiration date has passed.

fields are name address expires notes (notes has "subscribe")

 

[Rob Parker]

Hi Tammy,

Not sure that I'm exactly sure what you're asking for. But assuming that
the "Expires" field is a date/time field, you can check whether that date
has passed by using the built-in Date() function in your criterion. Eg.
entering ">Date()" (without the quotes) in the criterion field will return
all records where the Expires date is in the future. If you want to check
against a specific date (eg. return records with an Expires date on or
after 1 Jan 2003), you can enter the date using the # charcter as a
delimiter - so, for the example I gave, you'd enter ">=#1/1/2003#" (again
without the quotes).

HTH

Rob

PS. Using "name" as a field name is likely to cause you problems, since
Name is a reserved word in Access. For a lisst of all these (there are
lots) see http://allenbrowne.com/AppIssueBadWord.html

 

[Tammy]

i guess i didn't explain it very well.

i want the query to pull all the records in my customers table with a field
named code = "0" and then at the same time NOT PULL where field named where
notes = "*subscribers*" and also if notes = "*subscribers*" and expire
=<=11/21/08

so i want it to pull everything from table customers where code ="0" and NOT
PULL where notes ="*subscribers*" and if notes ="*subscribers*" where expire
= <= 11/21/08.

Does that make sense? I hope I'm saying that right, I don't want to see the
subscribers that have expired 11/21/08 or before but i want to see everything
else. if the subscribers expiration date is greater than 11/21/08 i still
want to see it.

 

[Tammy]

thank you.

This might depend on the version of Access that you're using. I'm responding
for Access 2003 and earlier. You have the right idea, just a little tweak to
the syntax should get you going. Assuming you're using Access with a Jet
database, the syntax would be:

Note Like "%subscriber%"

in your SQL query's WHERE clause. In the query designer grid it would be:

Like "*subscriber*"

The query designer in Access translates the * to % behind the curtain for a
Jet database.

Hope this helps.

 

[Rob Parker]

Tammy, try this:

In the query design grid, in one criteria row enter, the following for these
fields:
Code 0
Notes Not Like "*subscriber*"

In the next criteria row, enter the following for these fields:
Code 0
Notes Like "*subscriber*"
Expires >#11/21/2008#

That will give, in SQL view of your query, something like:
SELECT tblCustomers.*
FROM tblCustomers
WHERE (((tblCustomers.Code)=0) AND ((tblCustomers.Notes) Not Like
"*subscriber*")) OR (((tblCustomers.Code)=0) AND ((tblCustomers.Notes) Like
"*subscriber*") AND ((tblCustomers.Expires)>#11/21/2008#));

It will return all records for Code = 0, except for those where Notes
contains "subscriber" and the expiry date is on or before 11/21/08.

Hopefully that's what you want. If it's not, please post some sample data
for all combinations of the fields you want, and indicate whether each
record should or should not be returned.

HTH,

Rob

 

[Tammy]

Yes! I think that's exactly what I need. Thank you very much.