Where dateA > dateB and dateA < dateC in SQL ???

[thewizz]

Ho do i COmpare dates in access SQL ?
My DateA is a field in a Table
DateB & DateC are dates in a Form

(Im doing a FInd form that opens aother form on a button press & i pass the WHERE statement in the stLinkcriteria of OpenForm function)


thewizz

 

[KenReay]

Hi

If you are building the SQL string in code then

strSQL = &quot;...whatever.. WHERE DateA > #&quot; & DateB & &quot;# AND DateA < #&quot; & DateC & &quot;#;&quot;

Note however Access in SQL strings expects dates to be in US format, no matter what the locale settings of the PC, if you are not in the US you may need to slightly modify this to force US style mm/dd/yy so

Format(DateB,&quot;mm/dd/yy&quot and similarly for DateC







Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MichaelRed]

Also, it can be slightly simplified:

strSQL = &quot; WHERE [DateA] Between #&quot; & DateB & &quot;# and #&quot; & DateC & &quot;#;&quot;





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[thewizz]

PROBLEM SOLVED! Opss caps..
thanks it has worked !

thewizz

 

[r937]

> Note however Access in SQL strings expects dates
> to be in US format, no matter what the locale settings
> of the PC, if you are not in the US you may need to
> slightly modify this to force US style mm/dd/yy

that's not quite right

access has no problem whatsoever with the ISO standard format yyyy-mm-dd

in fact no database i'm aware of has any trouble with that format, so why take the chance on getting mm/dd mixed up with dd/mm?

nice that it's a standard, too

rudy

http://r937.com/