DateFormat,Date,SQL

[imfarhan]

Hi All,
I would like to run the following query using date range
Start Date = 01/10/2010 dd/mm/yyyy
End DAte = 31/03/2011 & 23:59:59 dd/mm/yyyy

When I run the Option-1 one using "Parameters" its run ok

Option-1
WHERE (((AdmitDate)<=DateAdd("d",1,[End Date]))
AND ((DischDate)>=DateAdd("d",-28,[Start Date]))

Option-2 (Problem)
When I run the follwoing query it doesn't bring more rows which I'm expecting

AND ((DischDate)>= DateAdd("d",-28,format("01/10/2010","dd/mm/yyyy"))
And (AdmitDate)<=DateAdd("d",1,Format("31/03/2011","dd/mm/yyyy") & " 23:59:59")

I think its the format problem even I tried using # using American format still the same problem mm/dd/yyy. My System|ControPanel|Regional setting is UK i.e dd/mm/yyyy

Thanks for you help
Regards
F

 

[Norie]

Why are you using the Format function for the dates in the second example?

Apart from that you should have a look at the Between operator if the criteria is a date range.