Presence of Data in a query

[banks13cu]

I have a problem and am not sure how to solve it...

I am creating a database and two columns are Birthday and Room Number. I have it set now, that everyone is sorted by their Birthday into different queries, however I need to go further with this. I need to be able for it to sort everyone by Birthday, unless they have a Room Number entered in their record. If there is a room number I need it to sort it into that Room's query unstead of their Birthday.

If you have any suggestions please let me know....Thanks

 

[dhookom]

Can you give us an example of your data and queries and explain why you think you need separate room number queries?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[banks13cu]

I have a table that has general information in it such as name, address, birthday, etc. I have it queried into seperate rooms, which are divided by their birthday. (For example... if your birthday falls between 01/01/2005 and 03/31/2005 it queries into Room 100. If your birthday is between 4/1/2005 and 6/30/2005 then Room 101.) However, some students are moved up based on how fast they start walking and not their age. (For example, a child born on 3/15/2005 could be in Room 101 because he is crawling.) I want it to sort by their birthdays so you do not have to assign room numbers to everyone, but if there is a room number entered into a column in the table labeled room number then it will reassigned it to be put into that Room unstead of sorting by the birthday.

I hope this makes sense.... thanks

 

[dhookom]

I would probably create a function that accepts the birthdate and returns the room number. For instance:

Public Function GetRoom(datBOD as Date) as String
  Select Case datDOB
  Case #01/01/2005# to #3/31/2005#
    GetRoom = "100"
  Case #4/1/2005# To 6/30/2005#
    GetRoom = "101"
  Case ....
    GetRoom = ...
  End Select
End Function

Then in the query, use an expression like:
RoomNum: Nz([Room Number], GetRoom([Birthday ])

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]