467,134 Members | 948 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,134 developers. It's quick & easy.

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>")
Oct 7 '08 #1
  • viewed: 3856
Share:
8 Replies
ck9663
Expert 2GB
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
Oct 7 '08 #2
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
Oct 8 '08 #3
Use this:

SQL = String.Format(“SELECT * FROM WEB_PRODUCTIVITYDONE
WHERE PROD_USER like '%{0}%’
AND PROD_ENTERED BETWEEN '%{1}%’ AND ‘%{2}%’”, agent, fdate, tdate)
Oct 8 '08 #4
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
Oct 10 '08 #5
ck9663
Expert 2GB
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
Oct 10 '08 #6
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
Oct 11 '08 #7
Delerna
Expert 1GB
add 1 to tdate before using it for the query

Expand|Select|Wrap|Line Numbers
  1. tdate=cdate(tdate)+1
  2.  
Oct 11 '08 #8
Thank you friend, Its work very well now.

Your the Man!

God Bless to All
Oct 12 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by Bosconian | last post: by
3 posts views Thread by samdev | last post: by
9 posts views Thread by LayneMitch via WebmasterKB.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.