Date Query

  • Thread starter Thread starter Scarlet via AccessMonster.com
  • Start date Start date
S

Scarlet via AccessMonster.com

Hello Everyone!

I have a bit of a perplexing Query that I am trying to put together,
hopefully you may be able to shed some light on it!

Ok here is the scenerio......I have a mock hotel database, the four fields
are CUSTID, TRANSACTIONDATE, ARRIVAL, and YEARUSAGE. What I am trying to
calculate is all customers where the transaction date for them to come back
to the hotel again is within 7 days of the present years arrival date. For
example, a customer checks in on Jan 1st 2006, I want to find all the people
where transaction date falls within 7 days of that check in date for a return
in 2007.

Any additonal info or clarification from me will be happily provided, thanks!!
!
 
Scarlet,

First, initialize two variables, say BegWin (beginning of time window) and
EndWin, both being type Date.

BegWin = the ARRIVAL date last year + a year, minus 7 days. EndWin is 14 days
later than BegWin. Hence:

BegWin = DateAdd("yyyy",-1, ARRIVAL) - 7
EndWin = BegWin + 14

In your query, you can say

WHERE TRANSACTIONDATE Between BegWin And EndWin

Hope this helps. See the DateAdd() function for details, and be sure to look
at the examples.

Sam
 
Sam,

Awesome! Got it working!
Scarlet,

First, initialize two variables, say BegWin (beginning of time window) and
EndWin, both being type Date.

BegWin = the ARRIVAL date last year + a year, minus 7 days. EndWin is 14 days
later than BegWin. Hence:

BegWin = DateAdd("yyyy",-1, ARRIVAL) - 7
EndWin = BegWin + 14

In your query, you can say

WHERE TRANSACTIONDATE Between BegWin And EndWin

Hope this helps. See the DateAdd() function for details, and be sure to look
at the examples.

Sam
Hello Everyone!
Click to expand...
[quoted text clipped - 11 lines]
Any additonal info or clarification from me will be happily provided, thanks!!
!
Click to expand...
Click to expand...
 
Back
Top