Dealing with null in a query

[rikklaney1]

Morning ladies and gentlemen. I have a query with 27 fields. they can be "p", "q" or null. I'm trying to create a field that will give me a yes or no based on whether ANY of them are "p". I've tried this

firstdone: IIf([a101] And [a102]="p","no","yes")

which works fine until I hit a null value then it gives the wrong answer. So I tried this

firstdone: IIf([a101] And Nz([a102],"q")="p","no","yes")

But I still get a wrong answer. Anyone have any suggestions on a way to do this? Thanks.

 

[plog]

Pretty sure you have laid out your tables incorrectly. Instead of 27 fields in the existing table, you need 27 records in another table. Actually fewer because the NULL values don't need records.

Currently you have this:

ExistingTable
ExistingID, a101, a102, a103, a104, ....
13, p, p, q, p
22, NULL, p, NULL, q

Instead, you should have this:

PQTable
PQID, ExistingID, FieldNumber, FieldValue
1, 13, 101, p
2, 13, 102, p
3, 13, 103, q
4, 13, 104, p
...
111, 22, 102, p
112, 22, 104, q

With that structure the query to determine if you have any p values is simple:

SELECT ExistingID
FROM PQTable
WHERE FieldValue='p'
GROUP BY ExistingID

 

[arnelgp]

If((a101 & a102 & "") like "*p*", "yes", "no")

 

[rikklaney1]

Thanks arnelgp. That did it.