Microsoft Access - Combining Query Expressions

Asked By D on 04-Apr-12 03:06 PM
I have an expression that formats and combines two fields (TableNumber and SeatNumber) into another field (BidderNumber) which works fine.  However, I also need to add to this expression If SeatNumber is Null then BidderNumber is Null.  I can't seem to get the two expressions combined properly.  Here is what I have in the query builder...

BidderNumber: Format([TableNumber],"00") & "" & Format([SeatNumber],"00" And IIf([Seat Number],""),"")

I have also tried using the word Null instead of the "" and can't get it to work.

Devil Scorpio replied to D on 04-Apr-12 04:59 PM

If you are comparing field SeatNumber, then you need to IS NULL keyword.

You have to use the follwing query to get NULL in case SeatNumber is NULL

BidderNumber: IIF([SeatNumber] IS NULL, NULL, Format([TableNumber],"00") & "" & Format([SeatNumber],"00"))

D replied to Devil Scorpio on 04-Apr-12 05:09 PM
Perfect - thank you
Somesh Yadav replied to D on 05-Apr-12 12:22 AM

here is a sample example,

Combining expressions:-

Complex expressions can be built by combining expressions with the AND and OR operators.

For example, the following expression would select all the houses that have more than 1,500 square feet and a garage for three or more cars:

"AREA" > 1500 AND "GARAGE" > 3

When you use the OR operator, at least one side of the expression of the two separated by the OR operator must be true for the record to be selected.

For example:

"RAINFALL" < 20 OR "SLOPE" > 35

Use the NOT operator at the beginning of an expression to find features or records that don't match the specified expression.
For example:

NOT "STATE_NAME" = 'Colorado'

NOT expressions can be combined with AND and OR.

For example, this expression would select all the New England states except Maine:

"SUB_REGION" = 'New England' AND NOT "STATE_NAME" = 'Maine'

Hope it helps you.