Help needed with a Dates Access Query

[Douglas]

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

[Pavel Romashkin]

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

[Pieter Linden]

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