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.CreateOb ject ("ADODB.Connect ion")
oCon.Open "Provider=Micro soft.Jet.OLEDB. 4.0; Data Source=" & Server.MapPath( "webprs_db.mdb" ) & ";"
Set Rec = Server.CreateOb ject ("ADODB.Records et")
search = request.queryst ring("search")
agent = request.form("l istagent")
fdate = request.form("t xtfdate")
tdate = request.form("t xttdate")
if agent <> "" then
SQL = "SELECT * FROM WEB_PRODUCTIVIT YDONE WHERE PROD_USER like '%" & agent & "%' AND PROD_ENTERED BETWEEN '%" & fdate & "%' AND '%" & tdate & "%'"
else
SQL = "SELECT * FROM WEB_PRODUCTIVIT YDONE 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_SENDE R") & "</td>")
response.write( "<td bgcolor="& rowBColor&">" & Rec("PROD_SENDE RNAME") & "</td>")
response.write( "<td bgcolor="& rowBColor&">" & Rec("PROD_REFDE TAILS") & "</td>")
response.write( "<td bgcolor="& rowBColor&">" & Rec("PROD_CONCE RN") & "</td>")
response.write( "<td bgcolor="& rowBColor&">" & Rec("PROD_DATE" ) & "</td>")
response.write( "<td bgcolor="& rowBColor&">" & Rec("PROD_ENTER ED") & "</td>")
response.write( "<td bgcolor="& rowBColor&">" & "<a href=""prs_view rec.asp?recnum= " & Rec("PROD_RECNO ") & "&user="& rec("PROD_USER" ) &""">" & "<p align=center>" & " View " & "</td>")
8 4277 ck9663 2,878
Recognized Expert Specialist
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_PRODUCTIVIT YDONE
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_PRODUCTIVIT YDONE 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
ck9663 2,878
Recognized Expert Specialist
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_PRODUCTIVIT YDONE 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
Delerna 1,134
Recognized Expert Top Contributor
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Tim Graichen |
last post by:
Good morning,
I have a sub-form that displays records from a table as a continuous form.
The table has several hundred records, but the subform only displays five or
six records. The records do include a date field.
How can I force the sub form to display the most recent dates by default,
verses forcing the user to scroll through all of the records to the bottom
of the form in order to view the most recent entry or to add a new entry...
|
by: Joe User |
last post by:
Hi all....I have a feeling this is going to be one of those twisted query
questions, but here it goes anyways....
I want to generate a report that shows the chronology of events (represented
by field names). Essentially, I would like to sort the DATE FIELDS for each
record in the table by the order of the DATES in those DATE FIELDS.
For example:
record ID= 354
|
by: Mark |
last post by:
When my form goes to a new record, I have a procedure that copies the last
record added to the form's underlying table into the form. The intent is that a
series of new records may have the same data in many of the fields so I paste in
the same values of the previous record and then edit what needs edited in the
new record saving much retyping of the same data. Doing this however creates the
definite possibility of creating a duplicate...
|
by: Lyn |
last post by:
I need some guidance for a technique that will allow me to accomplish the
following...
I have a table in which each record contains a photograph. I would like to
display in a form a thumbnail size version of each photograph horizontally
across the form, then have the photgraphs "word wrap" (picture wrap ?) at
the right end of the form onto the next "line" of photographs. Does anyone
know how this can be done?
Obviously, displaying...
|
by: Helen |
last post by:
Hi
I'm using ASP.NET's standard validators to display error messages when
people don't fill in required fields on my form.
The error messages currently show as soon as the form is loaded, but
what I'd really like is for the error messages to only be displayed
after the validation fails. Is this a feature of the standard ASP.NET
validation or will I have to write custom validators if I want to do
that?
| |
by: Bosconian |
last post by:
I must be having a brain freeze because I can't figure out how to display
dates older than 1970.
I have birth dates stored in MySQL as "date" data types (exp. 1955-04-06).
I'd like to display as "April 4, 1955".
I've tried using date("F j, Y"), but only get "December 31, 1969".
I'm using PHP 4.3.8. Any suggestions would be appreciated.
|
by: samdev |
last post by:
I have a table that contains records that provide the following info:
vehicle type
route #
start time
travel time
end time
I need to display info for all records in a query and/or report BUT
need to separate the record by route that contains the minimum end
|
by: sejal17 |
last post by:
hello everyone,
I have a problem with displaying records between two dates.Below is my designing page:
start date: combobox of month(smonth) & combobox of year(syear)
end date : combobox of month(emonth) & combobox of year(eyear)
I want the records between this month and year.i have written the below query for that:
suppose,
smonth=01,emonth=08,syear=2000,eyear=2005
|
by: LayneMitch via WebmasterKB.com |
last post by:
Hello.
Got another one for you folks. I'm working on this problem that wants me to
1. Prompt for name
2. Use pop-up box with name
3. Display current date on page in format "October 30, 2000."
4. Display last modified date of doc.
Here is my attempt. What a headache :-(
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |