SQL, display different field

[PB90]

I would like to use an sql to fill a form. If field a is blank I want to display field b instead. Can I do this?

 

[ZmrAbdulla]

Look for "IIF Function" in the help file

________________________________________________________________________
Zameer Abdulla
Visit Me
A person who misses a chance and the monkey who misses its branch can't be saved.

 

[PB90]

I tried:

Expr1: IIf(IsNull([tblPendReq]![intRmID]),[tblRoomName]![txtRoomName],[tblRoomType]![txtRoomType])

I have 3 tables; PendReq (primary table for this form)
RoomName(key=intRmKey), & RoomType(key=intRmTypeID).
PendReq has a tracking Number, RoomType, and room Name fields. (RoomName can be blank)

When I link the keys for all subtables, I only get records where RoomName has a value. When I don't link the key to roomName, I get a record for each PendReq, duplicated for each RoomName in RoomName table.

What I want is to show each record in PendReq. When RoomName is present, I want to show that, otherwise I want to show RoomType.

 

[PHV]

Have a look at LEFT JOIN

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886