query if

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

Guest

I'm working with a query and would like to know how to do the following:

If field [item] starts with the letter X or Z then field [label] would equal
"1" else "2"

This is what I have so far in my query but the syntax must be wrong:

label: If [item] = *X or *Z then [label] = "1" else [label] = "2"

Thanks.
 
I'm working with a query and would like to know how to do the following:

If field [item] starts with the letter X or Z then field [label] would equal
"1" else "2"

This is what I have so far in my query but the syntax must be wrong:

label: If [item] = *X or *Z then [label] = "1" else [label] = "2"

Thanks.
Click to expand...

label: IIf [item] Like "X*" or [Item] Like "Z*", "1","2")

Look up IIF and Like in VBA help.
 
A couple of things --
There can be only one label to a field so if you have multiple record
everything gets the same label.
You can out put the data in two diffrent fields like --
1 2
X-ray
Zebra
Apples
Oranges
Xeon
Zoo

You would use this --
1: IIf([item] Like "X*" Or [item] Like "Z*" [item], Null)
and second output field --
2: IIf([item] Like "X*" Or [item] Like "Z*", Null, [item])
 
rml said:
I'm working with a query and would like to know how to do the following:

If field [item] starts with the letter X or Z then field [label] would equal
"1" else "2"

This is what I have so far in my query but the syntax must be wrong:

label: If [item] = *X or *Z then [label] = "1" else [label] = "2"
Click to expand...


SELECT item, IIf(item LIKE "[XZ]*", "1", "2") As Label
FROM sometable
 
Back
Top