Newbie can't get date range search from form to work

[Lee Falataco]

Please help! I'm banging my head against a wall here. This is my
first stab at ASP.

I'm able to pass vars from a form to a search results page from Access
for all fields EXCEPT date. I understand I need to use "#" around the
date. Here is my form:

This is my query code:
rs_cal.Source = "SELECT * FROM Events WHERE eventTitle LIKE '%" +
Replace(rs_cal__varEvent, "'", "''") + "%' AND location LIKE '" +
Replace(rs_cal__varLocation, "'", "''") + "%' and start_date LIKE #" +
Replace(rs_cal__varStartDate, "'", "''") + "# ORDER BY start_date,
start_time, eventTitle"


<form action="search_calendar_results.asp" method="get">
<table width="90%" border="0">
<tr>
<td><input name="txtStartDate" type="text"
id="txtStartDate" size="10" maxlength="10">
</td>
</tr>
</table>
<input type="submit" name="Submit" value="Submit">
</form>



When I get to my search_results.asp page, the asp string says:

[url]http://www.noefc.info/search_calendar_results.asp?txtEvent=&txtStartDate =02%2F20%2F2004&Submit=Submit[/url]

This is my query code:
rs_cal.Source = "SELECT * FROM Events WHERE start_date LIKE #" +
Replace(rs_cal__varStartDate, "'", "''") + "# ORDER BY start_date,
start_time"

But the date range doesn't work in search. I keep getting 0 records
found.

When I set up the default, I set up as:
Dim rs_cal__varStartDate
rs_cal__varStartDate = "01/01/1900"

So it's searching on 1900 date. Why isn't it searching on the date
var I pass?

Any help would be great! TIA!!!

[Ray at]

LIKE is only valid for text comparisons, not dates or numerics. Try:

and start_date=#" & rs_cal_varStartDate & "# ORDER BY..."


One way of getting a bit of help with queries is to build your query in
Access and then view it in SQL view to see what it comes up with.

Also note that if your date/time column in Access has times and you just use
a date value, only the dates with a time of midnight will be returned.

Also check out this article: [url]http://www.aspfaq.com/show.asp?id=2040[/url]

Ray at home



"Lee Falataco" <leekaye@yahoo.com> wrote in message
news:667ce401.0405261434.62731ccd@posting.google.c om...

> Please help! I'm banging my head against a wall here. This is my
> first stab at ASP.
>
> I'm able to pass vars from a form to a search results page from Access
> for all fields EXCEPT date. I understand I need to use "#" around the
> date. Here is my form:
>
> This is my query code:
> rs_cal.Source = "SELECT * FROM Events WHERE eventTitle LIKE '%" +
> Replace(rs_cal__varEvent, "'", "''") + "%' AND location LIKE '" +
> Replace(rs_cal__varLocation, "'", "''") + "%' and start_date LIKE #" +
> Replace(rs_cal__varStartDate, "'", "''") + "# ORDER BY start_date,
> start_time, eventTitle"
>
>

[Lee Falataco]

Thanks! I tried the trick of viewing SQL code in Access, cut and
pasted and with a little tweaking was able to get the var passed.
Appreciate your help!

Lisa

"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in message news:<OGhkIt3QEHA.3944@tk2msftngp13.phx.gbl>...

> LIKE is only valid for text comparisons, not dates or numerics. Try:
>
> and start_date=#" & rs_cal_varStartDate & "# ORDER BY..."
>
>
> One way of getting a bit of help with queries is to build your query in
> Access and then view it in SQL view to see what it comes up with.
>
> Also note that if your date/time column in Access has times and you just use
> a date value, only the dates with a time of midnight will be returned.
>
> Also check out this article: [url]http://www.aspfaq.com/show.asp?id=2040[/url]
>
> Ray at home
>
>
>
> "Lee Falataco" <leekaye@yahoo.com> wrote in message
> news:667ce401.0405261434.62731ccd@posting.google.c om...

> > Please help! I'm banging my head against a wall here. This is my
> > first stab at ASP.
> >
> > I'm able to pass vars from a form to a search results page from Access
> > for all fields EXCEPT date. I understand I need to use "#" around the
> > date. Here is my form:
> >
> > This is my query code:
> > rs_cal.Source = "SELECT * FROM Events WHERE eventTitle LIKE '%" +
> > Replace(rs_cal__varEvent, "'", "''") + "%' AND location LIKE '" +
> > Replace(rs_cal__varLocation, "'", "''") + "%' and start_date LIKE #" +
> > Replace(rs_cal__varStartDate, "'", "''") + "# ORDER BY start_date,
> > start_time, eventTitle"
> >
> >