Sorting database results by date

Im mainly just poking around with this project in the hopes that I can turn it into something semi-useful, but I am having a problem with pulling data from my database.

I have a DB called "entryform" that has a table called "Results"with the following fields:

ID
Firstinput
Time
Customer_code
Remote_computer_name
User_name
Browser_type
Timestamp

(I basically let Frontpage create the database for me just so I could get started...)

I have a form called entryform.asp that lets me put values in for Firstinput, Time, and Customer_code and it works great. What I am trying to do now is create a way to query the database for a date range, which is stored in the "firstinput" field. I tried the following SQL statement:

"SELECT * FROM Results WHERE (firstinput > '7/1/2003' AND firstinput < '7/31/2003')>"

But I keep getting this error:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '(firstinput > '7/1/2003' AND firstinput < '7/31/2003')>'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

What am I missing here...I know it has to be something simple....

Thanks

BTW, is there a simpler way to do sort for a date range by using text boxes or some other method? Sorry for the newbie questions, but we all have to start somewhere... *grin*

I think the problem is the final ">" after the ")" which is being interpreted as another comparison in
"SELECT * FROM Results WHERE (firstinput > '7/1/2003' AND firstinput < '7/31/2003')>"
It looks like a closing HTML tag but shouldn't be there.

One other possible problem (but I suspect the main problem is the abovementioned) could be the date format. SQL Server is sometimes flexible in the formats it accepts, but from my experience it ALWAYS accepts YYYYMMDD format, like 20030701 and 20030731.
If you use a dateformat like YYYYMMDD I have yet to have it throw an error.

I tried changing it to remove that final ">" symbol, but it still doesnt like me very much. Also, Im not using SQL server - at least I dont think I am. My database is just an Access 2000 database...does this make a difference?

The delimiter for date data is a #, not a ' (Edit: In MS Access only)

Try "SELECT * FROM Results WHERE (firstinput > #7/1/2003# AND firstinput < #7/31/2003#)>"

If I am correct the correct syntax would be:

"SELECT * FROM Results WHERE firstinput > #7/1/2003# AND firstinput < #7/31/2003#"

at least that is how I have always done it. Let me know if this helps.

I think I figured it out...for some reason, Frontpage keeps adding extra syntax when you do a Database Results region and try to use a custom query. It adds >,", and all sorts of other funky stuff. I tried using the HTML view to remove the extra characters, but it would remove my changes when I saved it.

Notepad to the rescue.

I opened my file in notepad and changed the query line to read:

fp_sQry="SELECT * FROM Results WHERE (Sch_Date > '2003/07/01') AND (Sch_Date < '2003/07/31')"

(I changed some field names along the way too...*grin*)

After making these changes, I added two entries in July, and one in August then I loaded the page that is supposed to show the July entries ONLY and voila! Im going back to create an August page just to make sure that it works ok...

Incidentally, no one else caught this yet, but my query would actually be inaccurate since I want to show all entries for July...this query would exclude July 1 and July 31, so I modified to read

fp_sQry="SELECT * FROM Results WHERE (Sch_Date >= '2003/07/01') AND (Sch_Date <= '2003/07/31')"

so that it would actually include the 1st and 31st...

Thanks for the help everyone - it got me pointed in the right direction, and Im sure I will be back with more questions as time goes on.

Sorry, I was concentrating on the error and not the logic (>=) but it's better that you got it.
For the August page, you may prefer to do a single page which takes a variable as the parameter to automatically generate the dates using one query (called by something like entryform.asp?month=7&year=2003) to save time later.

And for Frontpage, it isn't the first time I've heard or seen this despicable behavior, so ... be careful out there...

Thanks...I have actually started using FrontPage for the bulk of building the database forms and results pages, but then I use EasyASP for modifying the code...works out pretty nicely!
I dont think that the "entryform.asp?month=7&year=2003" will work since it seems to be saving my date field as a text entry, but thats ok - I *think* I can just create a seperate page for each month, then have each page display the corresponding results from the database...I'll play with it some more just to be sure and if I find anything helpful, Ill be back...

Just sorry I wasn't clear enough, but it probably won't be a problem anyway.
You would need to construct the proper date on the basis of the Get Variables which will be text (?month=jul&year=2003 would be equally valid), using a combination of Case and/or IfThen constructs, since you will need "variable dates" to create valid dates based on the month (and eventually leap year).
The advantage would be that you won't ever need to go add additional months, you can create a "perpetual page", but it might not be within the scope or necessities of this problem.

Yeah, this is a pretty small project - but its a good startup/practice project.

On a side note, Im really getting peeved with FrontPage - for example, when I do a database results page, it uses the following code for each cell in a table:

<td><font face="Verdana" size="2"><!--webbot bot="DatabaseResultColumn" startspan
s-columnnames="ID,Sch_Date,Time,Customer_Code,B3,Remote_computer_name,User_name,Browser_type,Timestamp,Notes"
s-column="Timestamp" b-tableformat="TRUE" b-hasHTML="FALSE" clientside
local_preview="<font size="-1">&lt;&lt;</font>Timestamp<font size="-1">&gt;&gt;</font>"
preview="<font size="-1">&lt;&lt;</font>Timestamp<font size="-1">&gt;&gt;</font>" --><%=FP_FieldVal(fp_rs,"Timestamp")%><!--webbot
bot="DatabaseResultColumn" endspan -->
</font>
</td>


When all I really need is this:

<td>
<font face="Verdana" size="2"><center><B><%=FP_FieldVal(fp_rs,"TRL")%></font>
</td>

Talk about unnecessary code...sheesh

Actually, since you are working on learning good practices, I would suggest defining a style sheet at the beginning for td {font-family:Verdana,Helvetica,sans-serif; font-size:small; font-weight:bold; text-align:center}
and being done with it with NO repetition, also since the <B> tag is never closed. Not serious, but not good coding in any case.
So all you would really need is:
<td><%=FP_FieldVal(fp_rs,"TRL")%></td>
If there were multiple tables, use a class.

Gotcha. Makes it easier to define it once, but I remember the good ole days of HTML where you had to define the font properties for everything manually...
One more question for ya - I would reallllllly like to sort the results by the Sch_Date field, but when I try to use FP to do this, it doesnt give me the option...is there a quick and dirty way to do it? Im still reading about it, but hopefully its a quick fix...

Thanks!

fp_sQry="SELECT * FROM Results WHERE (Sch_Date >= '2003/07/01') AND (Sch_Date <= '2003/07/31') ORDER BY Sch_Date"
or
fp_sQry="SELECT * FROM Results WHERE (Sch_Date >= '2003/07/01') AND (Sch_Date <= '2003/07/31') ORDER BY Sch_Date DESC"
depending on oldest or most recent first is the goal.
There are lots of fun things you can do with ASP and Order By, but that'll have to be for another day...

Found it change:
fp_sQry="SELECT * FROM Results WHERE (Sch_Date >= '7/1/2003') AND (Sch_Date <= '7/31/2003')"

to

fp_sQry="SELECT * FROM Results WHERE (Sch_Date >= '7/1/2003') AND (Sch_Date <= '7/31/2003')ORDER BY Sch_Date ASC"

Argh...you beat me to it....