Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Searching an access database via date

Status
Not open for further replies.
peter11

peter11

Instructor
Joined
Mar 16, 2001
Messages
334
Location
US
I have created a way to search my site using dropdown menus. I am currently trying to search using a text box where the mm/dd/yy can be entered. Below is the code for the search page and template. Can anyone debug this?

Search Page<HTML>

<HEAD>
<TITLE>Employee Search</TITLE>
</HEAD>
<BODY>
<H2>Please enter your search text below</H2>
<FORM ACTION=&quot;date_search.cfm&quot; METHOD=&quot;POST&quot;>

First name: <INPUT TYPE=&quot;text&quot; NAME=&quot;searchdate&quot;><BR>

<P>
<INPUT TYPE=&quot;submit&quot; VALUE=&quot;Search&quot;>
<INPUT TYPE=&quot;reset&quot; VALUE=&quot;Clear&quot;>
</FORM>
</BODY>
</HTML>


Template
<CFQUERY DATASOURCE=&quot;sports&quot; NAME=&quot;schedule&quot;>
SELECT *
FROM schedule, opponent, team
WHERE team.ID = schedule.SportID AND
opponent.ID = schedule.OpponentID

<CFIF searchdate IS NOT &quot;&quot;>
WHERE searchdate = #dateformat(date,&quot;mm/dd/yy&quot;)#
</CFIF>

ORDER BY Date
</CFQUERY>
<HTML>
<HEAD>
<TITLE>Employee List</TITLE>
</HEAD>
<BODY>

<TABLE BORDER=5>
<TR>
<TH COLSPAN=&quot;2&quot;>
<CFOUTPUT>Found #schedule.RecordCount# matches</CFOUTPUT>
</TH>
</TR>
<cfoutput query=&quot;schedule&quot;>
<TR>
<TD valign=&quot;top&quot; align=&quot;left&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>#TeamName#</font>
</TD>
<TD valign=&quot;top&quot; align=&quot;left&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>#dateformat(date,&quot;DDDD&quot;)#</font>
</TD>
<TD valign=&quot;top&quot; align=&quot;left&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>#dateformat(date,&quot;mm/dd/yy&quot;)#</font>
</TD>
<TD valign=&quot;top&quot; align=&quot;left&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><cfif timeformat(date,&quot;HH:mm&quot;) eq &quot;00:00&quot;>TBA<cfelse>#timeformat(date,&quot;h:mm&quot;)#</cfif></font>
</TD>
<TD valign=&quot;top&quot; align=&quot;left&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>#Site#</font>
</TD>
<TD valign=&quot;top&quot; align=&quot;left&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><cfif #Site# IS &quot;Away&quot;><a href=&quot;link.cfm?id=#opponentID#&quot; target=&quot;_blank&quot;>#OpponentName#</a>
<cfelse>
#OpponentName#
</cfif></font>
</TD>
</TR>
</cfoutput>
</TABLE>
</BODY>
</HTML>


 
Sort by date Sort by votes
What is is doing or not doing? What exactly is the problem? Calista :-X
Jedi Knight,
Champion of the Force
 
Upvote 0 Downvote
I get a mesage that says, Error resolving parameter SCHEDULE.DATE.

 
Upvote 0 Downvote
Try putting single quotes around it like this:

<CFQUERY DATASOURCE=&quot;sports&quot; NAME=&quot;schedule&quot;>
SELECT *
FROM schedule, opponent, team
WHERE team.ID = schedule.SportID AND
opponent.ID = schedule.OpponentID

<CFIF searchdate IS NOT &quot;&quot;>
WHERE searchdate = '#dateformat(date,&quot;mm/dd/yy&quot;)#'
</CFIF>

ORDER BY Date
</CFQUERY>

If that doesn't work, set your date to a variable like this:

<CFSET TempVar = dateformat(date,&quot;mm/dd/yy&quot;)>

Your WHERE clause would look like this:
WHERE searchdate = '#TempVar#'

Access is really fussy about dates, so you just have to work around it. Hope this helps! Calista :-X
Jedi Knight,
Champion of the Force
 
Upvote 0 Downvote
Neither of these worked. Is it possible that it has something to do with the access database. I have the format of the Date field set to:
&quot;General Date - 09/12/2001 4:15:00 PM&quot;
 
Upvote 0 Downvote
I have my database fields for dates as simple text fields. On closer inspection, I think I see something else. Don't use &quot;date&quot; as a variable name. It's a reserved word. Change it to &quot;MyDate&quot; or &quot;sched_date&quot; or whatever. Calista :-X
Jedi Knight,
Champion of the Force
 
Upvote 0 Downvote
Also, if you are using &quot;general date&quot;, then there is a time component. You'd have to use where date LIKE ... for that to work. I suggest you change the field to &quot;Short Date&quot;.

John Hoarty
jhoarty@quickestore.com
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

fdgsogc
  • Locked
  • Question Question
Displaying results of an SQL Pivot Query
Replies
1
Views
823
jdhilljr
jdhilljr
lollieleaver
  • Locked
  • Question Question
Dreamweaver CS4 - how can I make an Access database downloadable from my site?
Replies
2
Views
321
lollieleaver
lollieleaver
RosieGp
  • Locked
  • Question Question
How to read string of 0s and 1s
Replies
3
Views
970
LyndonOHRC
LyndonOHRC
crdamsel
  • Locked
  • Question Question
Connect CF to MS Access Code
Replies
2
Views
350
crdamsel
crdamsel
clearwave
  • Locked
  • Question Question
Timesheet entry form that auto-fills the Date range?
Replies
4
Views
473
clearwave
clearwave

Part and Inventory Search

Sponsor

Back
Top