2 Criteria in 1 Query

[banks13cu]

I need to be able for a Query to take information from column if it is present but if it isn't then take it from a different column. For example...

Table has a Room Number column and a Birthday column. I need the query to sort by Birthday unless the Room Number is present. For query named Room_103 there is a Room Number entered in the table 103 and it pulls that one. But if there isn't a Room Number entered in the table then it defaults on the Birthday and pulls from the table if the Birthday falls between 4/1/2004 and 5/31/2004. However, if there birthday is 4/2/2004, but you enter 104 in the Room Number column of the table it takes them out of Room_103 query and puts them into Room_104 query.

If you have any ideas, please help me...

thanks

 

[melaniecarr23]

Can you create a sortby field in your query which would do this for you?

sortby: iif(RoomNum is null,birthday,roomnum)

This would generate a field with the values you want, and just sort on that.