My Query isn't working right

[Charmed7]

Does anyone know if there are problems with queries in Access 2000?

I have a standard Select Query and I have three fields with Criteria filled in. For some reason it's not working right (I've worked long enough with queries to know how they work)

For instance, my Provider has listed in the query '40668' And '40163', my date has =<#04/01/04# and my invoice type has 'new' And 'ren'. But when I veiw my data, only New is showing up. And then worse, I remove my New and Ren criteria, and NOTHING SHOWS UP! So I took all the criteria away, got all my data to show up. Added one criteria, it worked, added 2 and it didn't work.

Do you think I need to reinstall access 2000? or is this something Microsoft may have a patch to? or is there something else that I wouldn't know about?

Thanks for your help.

 

[WayneRyan]

Charmed,

If you're really sure, maybe a Compact/Repair might help.

If not, post a sample, and we'll see.

Wayne

 

[joeselch]

From your example, are you really trying to retrieve records where:
Provider = '40668' AND Provider = '40163'

A single record cannot have two values assigned to a single field.

Perhaps you should be using an OR condition instead.
This would return only those records where the provider is either '40668' or '40163'.

 

[maxwell#9]

Use "In" in stead of "And".

Looks like this:
In("40668", "40163")

It's more efficient that "Or" and you can add as many values to the expression as you need.

You won't need the quotation marks if the field is numeric.

 

[WayneRyan]

Max,

Good catch!

Wayne