Query on Occupany

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I wish to determine what beds are available on a user specified date. I can
query a specific date [Requested Date(dd/mm/yyyy)], however I can not get
anything more than results of the beds booked on that date, whereas I would
like that data PLUS results showing which beds were booked previously but are
still occupied on the user specified date. I'm quite the n00b to SQL, so
instead I have been entering combinations of <= and > etc with my
Expected_Discharge and Admission_Date fields (both date/time) and
unfortunately still havent found the results I know are there waiting. If you
need more info to help me just ask :)

Thanks!
 
WHERE [Requested Date(dd/mm/yyyy)] Between [Admission_Date] and [Expected_Discharge]

In the query grid
Field: OnDate: [Requested Date(dd/mm/yyyy)]
Criteria: Between [Admission_Date] and [Expected_Discharge]

More efficient would be
Field: Admission_Date
Criteria: Admission_Date <= [Requested Date(dd/mm/yyyy)]

Field: Expected_Discharge
Criteria: >= [Requested Date(dd/mm/yyyy)]

SQL where clause

WHERE Admission_Date <=[Requested Date(dd/mm/yyyy)]
AND Expected_Discharge >= [Requested Date(dd/mm/yyyy)]


This makes the assumption that you always have a value in BOTH fields. If
Expected_Discharge can be null then the criteria will have to be modified.
 
Thanks so much!!! I tried the grid criteria first, both cases produced blank
results, so I opted for the SQL way and IT WORKED PERFECTLY :)

I knew it'd be something simple and easily managed. :P

Thanks again :)
 
Back
Top