What is wrong with this WHERE clause ?

[Guest]

WHERE CloseDate is not Null AND
CloseDate Between "#" & Format$(varStart(), 'mm/dd/yyyy hh:nn:ss AM/PM') & "# And #" & Format$(varEnd(), 'mm/dd/yyyy hh:nn:ss AM/PM') & "#"

 

[Ken Snell]

Assuming that varStart and varEnd are public functions that exist in your
database, this statement looks ok.

So, can you tell us what error you're getting? Kinda hard to help if we
don't know why you (or ACCESS) think it's wrong.

--
Ken Snell
<MS ACCESS MVP>

WHERE CloseDate is not Null AND
CloseDate Between "#" & Format$(varStart(), 'mm/dd/yyyy hh:nn:ss AM/PM') &

"# And #" & Format$(varEnd(), 'mm/dd/yyyy hh:nn:ss AM/PM') & "#"

 

[Van T. Dinh]

The first condition (CloseDate is not Null) is NOT necessary. If CloseDate
is Null, the 2nd condition will return Null which will be treated as not
True and therefore not selected.

That is not "wrong", though. If the varStart() and varEnd() are Public
functions that return Null or Date values, the WHERE clause looks OK
(provided that the Query is executed in Access).

You will have to tell us the symptoms of "what's wrong".

--
HTH
Van T. Dinh
MVP (Access)

WHERE CloseDate is not Null AND
CloseDate Between "#" & Format$(varStart(), 'mm/dd/yyyy hh:nn:ss AM/PM') &

"# And #" & Format$(varEnd(), 'mm/dd/yyyy hh:nn:ss AM/PM') & "#"