displaying record between two dates errors

Good Day Everyone,

Requesting for your expertise please, in my program the user has to enter the agent name and the from date and to date inorder to display the record. I have to use 2 conditions. First look for the record with the same agent and display all record for that agent but the date should be within the two dates enter. Below are my codes, when running i got the error message invalid data type.
In my database the PROD_ENTERED is date/time format and in the asp code i put a 2 text box where the use has to enter the 2 dates.

please help me. im not really good in asp i'm just a beginner.

thank you for any help or idea you can give.

********************************************

Set oCon = Server.CreateObject ("ADODB.Connection")
oCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("webprs_db.mdb") & ";"

Set Rec = Server.CreateObject ("ADODB.Recordset")


search = request.querystring("search")
agent = request.form("listagent")
fdate = request.form("txtfdate")
tdate = request.form("txttdate")


if agent <> "" then

SQL = "SELECT * FROM WEB_PRODUCTIVITYDONE WHERE PROD_USER like '%" & agent & "%' AND PROD_ENTERED BETWEEN '%" & fdate & "%' AND '%" & tdate & "%'"


else


SQL = "SELECT * FROM WEB_PRODUCTIVITYDONE ORDER BY " & sort
End if
Rec.Open SQL, oCon

if Rec.EOF OR Rec.BOF Then
response.write "<br><br>"

response.write "<p align=center><b>No Records Found ... </b></p>"

end if


Do While Not Rec.EOF
'AND DisplayNum < 5
r = n Mod 2
if r <> 0 then
'rowBColor = "#99CCFF"
rowBColor = "white"
else
'rowBColor = "#CCFFFF"
rowBColor = "#FFFFFF"
end if

If Rec("PROD_DATE") < Date-3 then
rowColor = "Red"
else If Rec("PROD_DATE") < Now()-2 then
rowColor = "Blue"
else
rowColor = "Green"
end if
end if

response.write("<tr>")
response.write("<td bgcolor="& rowBColor&">" & Rec("PROD_RECNO")& "</td>")
response.write("<td bgcolor="& rowBColor&">" & Rec("PROD_USER") & "</td>")
response.write("<td bgcolor="& rowBColor&">" & Rec("PROD_SENDER") & "</td>")
response.write("<td bgcolor="& rowBColor&">" & Rec("PROD_SENDERNAME") & "</td>")
response.write("<td bgcolor="& rowBColor&">" & Rec("PROD_REFDETAILS") & "</td>")
response.write("<td bgcolor="& rowBColor&">" & Rec("PROD_CONCERN") & "</td>")
response.write("<td bgcolor="& rowBColor&">" & Rec("PROD_DATE") & "</td>")
response.write("<td bgcolor="& rowBColor&">" & Rec("PROD_ENTERED") & "</td>")
response.write("<td bgcolor="& rowBColor&">" & "<a href=""prs_viewrec.asp?recnum=" & Rec("PROD_RECNO") & "&user="& rec("PROD_USER") &""">" & "<p align=center>" & " View " & "</td>")

Display the value of your variable SQL and see how your query looks like.I think the reason is your percent sign on your min and max value of your BETWEEN operator. Although SQL Server will explicitly convert the value of your textbox value to datetime/smalldatetime (depending on PROD_ENTERED's data type), the percent sign will give you a problem. Check the value of SQL anyway.

-- CK

may SQL is just

Dim SQL, tdate, fdate

On may access DB PROD_entered is date/time type.

How should declared the variable? please help and thank you

Use this:

SQL = String.Format(“SELECT * FROM WEB_PRODUCTIVITYDONE
WHERE PROD_USER like '%{0}%’
AND PROD_ENTERED BETWEEN '%{1}%’ AND ‘%{2}%’”, agent, fdate, tdate)

thank for the help but still i got and error.
Error Type:
Microsoft VBScript compilation (0x800A0408)
Invalid character
/prs/search_details.asp, line 91, column 20
SQL = String.Format(“SELECT * FROM WEB_PRODUCTIVITYDONE WHERE PROD_USER like '%{0}%’ AND PROD_ENTERED BETWEEN '%{1}%’ AND ‘%{2}%’”, sagent, fdate, tdate)
***********************
I really new on asp. if possible could you provide me a complete working script for asp that the user need to enter the agent name then enter the start date and end date and click search. aftet this it will seach the MSAccess database to display all the records for that agent between the two dates. just a simple plain codes, no need for designed justa a simple code. thank you very much God Bless and more power.

If not possible to post here please email me at rjlorenzo@yahoo.com

thank you

Did you try asking the guys in VB or Access Forum?

Anyway, I'm not an expert with VB but I think single quote means comments. So I don't know how it will affect your string variable. Did you managed to display the value of your SQL variable? If you can display it, it means it's a valid string. Copy that string and paste it on your Access query builder and see if it returns any result set.

-- CK

thank you very much to all, i'm now able to run the query, see below.

SQL = "SELECT * FROM WEB_PRODUCTIVITYDONE WHERE PROD_USER like '%" & sagent & "%' AND PROD_ENTERED between #" & fdate & "# and #" & tdate & "#;"

the problem now is for example, i entered aug 1, 2008 and aug 3, 2008 then it only show the data for aug 1 and aug 2. what i need is to displayed aug 1 to 3. Because i'm using BETWEEN it only show aug 1 and aug 2. Is there any way i could solved it.

thank you
RJ

add 1 to tdate before using it for the query

Thank you friend, Its work very well now.

Your the Man!

God Bless to All