Parameter from Form??

[Peter Allanach]

Can anyone advise me if the query criteria below is
correct? "Between ([Forms]![Form1]![Text0]) And Date()
+#17:00:00#" It does not produce any results. Is the use
of the Form to obtain a date from the user valid?

 

[Gary Walter]

Can anyone advise me if the query criteria below is
correct? "Between ([Forms]![Form1]![Text0]) And Date()
+#17:00:00#" It does not produce any results. Is the use
of the Form to obtain a date from the user valid?

Hi Peter,

Are you working in a non-US date environment?

You might check out Allen Browne's excellent web page
on this topic:

http://members.iinet.net.au/~allenbrowne/ser-36.html

Just saving his SQLDate function in a module

'*** quote ***
Function SQLDate(varDate As Variant) As String
If IsDate(varDate) Then
SQLDate = "#" & Format$(varDate, "mm\/dd\/yyyy") & "#"
End If
End Function
'*** unquote ***

and using in WHERE clause my solve your dilemna.

BETWEEN SQLDate(Forms!Form1!Text0)
And Date() + #17:00:00#

Of course...Form1 has to be open when you
run the query.

Good luck,

Gary Walter

 

[Gary Walter]

Can anyone advise me if the query criteria below is
correct? "Between ([Forms]![Form1]![Text0]) And Date()
+#17:00:00#" It does not produce any results. Is the use
of the Form to obtain a date from the user valid?

Hi Peter,

Also...as Allen points out on his web page,
if Text0 is unbound (and working in US environment),
the solution may be as simple as setting the
Format property of Text0 to "Short Date"
so Access knows it is a date.

Good luck,

Gary Walter