Searching a date field

[MetroToday]

I am trying to search for a date from my Access datebase.
The field name is 'DateFrom' and is in ShortDate format in Access.

I have a date search text field setup in ASP. And I am using the correct date format in it, still when I search for a date that does exist in ASP, it does not return anything from the database.

Below is the relevent code.

Code for the Form Part:

<FORM ACTION=&quot;SearchResults.asp?UID=<%=Server.URLEncode(Request.QueryString(&quot;UID&quot)%>&quot; NAME=frmDate METHOD=post>

DATE <INPUT TYPE=&quot;Text&quot; name=&quot;Date&quot; size = 10>
<INPUT TYPE=submit Value=&quot;Submit&quot;>
</form>

Code for the Searched results page is:

If Len(trim(Request.Form(&quot;Date&quot)) > 0 Then
SQLtxt = &quot;SELECT * FROM tbldata WHERE _
DateFrom=&quot; & (Request.Form(&quot;Date&quot)
End If



Now is there some type of date function I need to put?

 

[TomasCrossroads]

SQLtxt = &quot;SELECT * FROM tbldata WHERE _
DateFrom=&quot; & (Request.Form(&quot;Date&quot)

Dates need to be quoted:

SQLtxt = &quot;SELECT * FROM tbldata WHERE _
DateFrom='&quot; & (Request.Form(&quot;Date&quot) & &quot;'&quot;

I'm not sure how to do this in Access but to be absolutely sure you could convert the date format from your field &quot;DateFrom&quot; to the same format you use to compare.
For example: yyyy-mm-dd

hope this helps

 

[GabeC]

Try

if isdate(now()) then
SQLtxt = "SELECT * FROM tbldata WHERE _
DateFrom=" & (cdate(Request.Form("Date")))
end if

Thanks,

Gabe

 

[AWithers]

There are a few points to note here. In Access you need to add # at the start and end of the date (and the date might have to be in American Date format) e.g. #11/15/02# is 15 nov 2002,

If Len(trim(Request.Form(&quot;Date&quot)) > 0 Then
SQLtxt = &quot;SELECT * FROM tbldata WHERE _
DateFrom=#&quot; & (Request.Form(&quot;Date&quot)&&quot;#&quot;
End If

also although the date is formatted as a short date it is really stored an a number with a decimal place represnting the time and thus you may not get an exact match by using =. You really need to think about using the between.#startdate#.. and #enddate# construction or just compare against the datepart() of the date value you have

Hope this makes some sense

Andy

 

[MetroToday]

Perfect solution.

Many Thanks to the Awithers fellow. It worked right on.

Now I understand why we use # # for in betweeens.

Thanks all others who contributed