Using 2 conditions in query designer

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JoeA2006 - 03 Aug 2006 16:19 GMT

I have to have a criteria in a field where the data must not match the first
3 letters in the Location name unless the accounting unit is 19.
I have entered this expression in the criteria and I recieve an error.
Is this a syntax problem? What would be correct

<>Left([LOC_NAME],3) and ([ACU] NOT "19")

Reply to this Message

Allen Browne - 03 Aug 2006 16:55 GMT

Switch the query to SQL View (View menu), and change the WHERE clause so it
looks like this:

WHERE ([ACU] <> 19) OR ([ACU] Is Null) OR ([MyField] <> Left([Loc_Name,3))

In place of "MyField", substitute the name of the field that must not match
the left 3 characters of the Loc_Name field.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have to have a criteria in a field where the data must not match the
>first
[quoted text clipped - 3 lines]
>
> <>Left([LOC_NAME],3) and ([ACU] NOT "19")

Reply to this Message

JoeA2006 - 03 Aug 2006 18:44 GMT

That syntax works but I don't think my logic is correct. I am trying to
return the records where <>Left([Loc_Name],3)--(this column name is LocCode)
except in the case of the ACU = "19".
So the only records returned in which a LocCode matches the first three
letters of a location name would ACU 19. All the other records would have
LocCode that does not match the first three letters of Loc_Name.

> Switch the query to SQL View (View menu), and change the WHERE clause so it
> looks like this:
[quoted text clipped - 11 lines]
> >
> > <>Left([LOC_NAME],3) and ([ACU] NOT "19")

Reply to this Message

JoeA2006 - 03 Aug 2006 18:56 GMT

I got it. I need to have the ACU = 19 OR <> Left([Loc_Name,3))
Thanks

> Switch the query to SQL View (View menu), and change the WHERE clause so it
> looks like this:
[quoted text clipped - 11 lines]
> >
> > <>Left([LOC_NAME],3) and ([ACU] NOT "19")

Reply to this Message