Queries

[Guest]

I have a database of motel customers. It shows people's names, arrival date,
unit no etc. I want to ask it who is staying during a particular month only.
Not just who arrives after a particular date. How do I ask this in a query
please?

 

[Smartin]

I have a database of motel customers. It shows people's names, arrival date,
unit no etc. I want to ask it who is staying during a particular month only.
Not just who arrives after a particular date. How do I ask this in a query
please?

Create a query that includes arrival date and / or departure date. In
the criteria for the date field(s) put something like

Between #12/1/2006# And #12/31/2006#

 

[John Spencer]

Does the table have departure date or the length of the stay?
Without that information, there is no reliable solution.

Assumption: There is an arrival date and a checkout date.

SELECT *
FROM YourTable
WHERE ArrivalDate <= #12/31/2006# and CheckOutDate >= #12/1/2006#

In the query grid:
Field: ArrivalDate
Criteria <= #12/31/2006#

Field: CheckOutdate
Criteria: >= #12/1/2006#

IF you store length of stay instead of checkout date then you will need
to calculate the checkout date using the DateAdd function.

Also, if you want to ignore persons that departed on 12/1/2006 (in the
example) you will need to change the criteria line to

Field: CheckOutdate
Criteria: > #12/1/2006#

 

[Guest]

Thanks for your help. The database has the arrival date and the no of nights,
not the departure date as such.
I don't know anything about the date add function.How do I do that please?
Thanks for your help. Appreciate it heaps

 

[Guest]

Hi thanks for that I appreciate it. I don't have the departure date in the
database, just arrival date and no of nights stay.Thanks for your help.

 

[John Spencer]

You can calculate the departure date as follows.

Field: Departure: DateAdd("d",[Number of nights],[Arrival Date])
Criteria: >= #12/1/2006#

 

[Guest]

Thanks for all your help

You can calculate the departure date as follows.

Field: Departure: DateAdd("d",[Number of nights],[Arrival Date])
Criteria: >= #12/1/2006#

Thanks for your help. The database has the arrival date and the no of nights,
not the departure date as such.
I don't know anything about the date add function.How do I do that please?
Thanks for your help. Appreciate it heaps