By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,953 Members | 1,131 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,953 IT Pros & Developers. It's quick & easy.

Help needed with a Dates Access Query

P: n/a
Can somone help me contruct a simple query

Basically I want my query to extract records where the MOT_Due_Date is
between the 1st Day of the current month and the date 6 weeks from the
1st day of the current month.

My MOT_Due_Day field is in the format "dd<space>mmm" ie 08 Jun and is
a text field, not a date field

i.e.
Select * <- I actually name all the fields but * is easier to type
FROM Vehicle
WHERE MOT_Due_Date & " " & format(now(), "yyyy")
BETWEEN {this is the bit i cant figure out}
an example would be Where MOT_Due_Date BETWEEN 01/01/04 AND 12/02/04

Is the 1st Day of the current month : "01 " & Format(now(),"mmm yyyy")
??
I dont know how to calculate 6 weeks from the 1st Day of current
month, can someone help me out here?

Thanks in Advance if you can help
Much appreciated

Doug
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Maybe,

BETWEEN Month(Date()) AND Date() + 42

will do the trick?
Pavel

Douglas wrote:

Can somone help me contruct a simple query

Basically I want my query to extract records where the MOT_Due_Date is
between the 1st Day of the current month and the date 6 weeks from the
1st day of the current month.

My MOT_Due_Day field is in the format "dd<space>mmm" ie 08 Jun and is
a text field, not a date field

i.e.
Select * <- I actually name all the fields but * is easier to type
FROM Vehicle
WHERE MOT_Due_Date & " " & format(now(), "yyyy")
BETWEEN {this is the bit i cant figure out}
an example would be Where MOT_Due_Date BETWEEN 01/01/04 AND 12/02/04

Is the 1st Day of the current month : "01 " & Format(now(),"mmm yyyy")
??
I dont know how to calculate 6 weeks from the 1st Day of current
month, can someone help me out here?

Thanks in Advance if you can help
Much appreciated

Doug

Nov 12 '05 #2

P: n/a
Doug,

how about:

SELECT *
FROM Vehicle
WHERE MOT_Due_Day BETWEEN DateSerial(Year(Date),Month(Date),1) AND
DateAdd("w",DateSerial(Year(Date),Month(Date),1),6 );

Why is your Date Field a text field? Then you'll have to use CDate()
to convert it to a date.
?Cdate("8-Jun")
6/8/2004
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.