Parameter datediff expression

[ciarra41]

I’m trying to pull dates by the date I enter and it will also pull from the previous 12 months not including the entire month from my date I entered only the date.
So, if I enter 4/5/2007 in a parameter query I would see data from the date I entered and the range 3/01/2006 thru 3/31/2007. I’ve tried this but I’m getting the same year.

Like "*" & [Enter:AssignDate] & "*" Or Between CDate(CStr("1/1/" & DatePart("yyyy",Date()))) And CDate(CStr(DatePart("m",Date())) & "/1")-1

 

[PHV]

Have a look at the DateSerial function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

If this is a date fields, you do not need like:

[tt]WHERE Table1.DateDate=[Enter:AssignDate] OR Table1.DateDate Between DateSerial(Year([Enter:AssignDate])-1,1,1) And DateSerial(Year([Enter:AssignDate]),Month([Enter:AssignDate]),0);[/tt]

 

[ciarra41]

Thanks that worked just fine, but know I have to put something in the parameter box or I'll get the message; "to complex...etc" that's why I need the like operator to bypass if I choose not to put anything. Is there a way I can bypass this without putting anything in the box?

Still have a star, thanks

 

[PHV]

Defines the parameter as a date (SQL code]:
PARAMETERS [Enter:AssignDate] DateTime;
SELECT ...
WHERE Table1.DateDate=[Enter:AssignDate] OR (Table1.DateDate Between DateSerial(Year([Enter:AssignDate])-1,1,1) And DateSerial(Year([Enter:AssignDate]),Month([Enter:AssignDate]),0))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886