Using 2 conditions in query designer

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Allen Browne said:
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

JoeA2006 said:
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")
Click to expand...
Click to expand...
 
I got it. I need to have the ACU = 19 OR <> Left([Loc_Name,3))
Thanks

Allen Browne said:
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

JoeA2006 said:
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")
Click to expand...
Click to expand...
 
Back
Top