Access/ASP SQL problem

[PeteCan]

I am having problems with an SQL query from an .asp page to an access database, can someone please help!!!.

I have the following SQL which works out which records were raised between the user specified start and end dates

strSQL="Select ProblemDate, StartTime, Service, Descrip, BusText, ILNumber FROM ILDatabase WHERE ProblemDate BETWEEN #" & StartDate & "# AND #" & EndDate &"#" & " ORDER BY ILNumber DESC"

I now want to tie this down further so that I only get records raised between a 'StartTme' on the StartDate, and an 'EndTme' on the EndDate.

I've tried several variations but end up with errors such as

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

CAN ANYONE TELL ME WHAT I NEED TO DO

Thanks

 

[scriverb]

Use can use the TimeValue Function to strip off the Time only from the StartDate and EndDate. I have added to your SQL string the addition of the Time criteria. If this is now what you were looking for please get back with me with more explaination.

strSQL=&quot;Select ProblemDate, StartTime, Service, Descrip, BusText, ILNumber FROM ILDatabase WHERE (ProblemDate BETWEEN #&quot; & StartDate & &quot;# AND #&quot; & EndDate &&quot;#) AND (ProblemTime >= TimeValue(StartDate) and ProblemTime <= TimeValue(EndDate))&quot; & &quot; ORDER BY ILNumber DESC&quot;

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[PeteCan]

The StartTime and CloseTime fields are seperate columns in the database to the StartDate and EndDate fields.
I've amended your code to be as follows

ProblemDate is a cloumn name in the DB
StartDate & EndDate are user specified
StartTme & EndTme are both set within the code at 08:00

strSQL=&quot;Select ProblemDate, StartTime, Service, Descrip, BusText, ILNumber FROM ILDatabase WHERE (ProblemDate BETWEEN #&quot; & StartDate & &quot;# AND #&quot; & EndDate &&quot;#) AND (StartTime >= TimeValue(StartTme) and CloseTime <= TimeValue(EndTme))&quot; & &quot; ORDER BY ILNumber DESC&quot;

But still getting

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

 

[cmmrfrds]

Did you do a
Response.Write strSQL
to show that the sql statement is formatted correctly?

Paste in your recordset Open statement if you need further assistance.

I recommend bypassing the ODBC driver and using the Microsoft OLE-DB provider for Access i.e. Jet 4.0
The ODBC is just an extra layer that can introduce it's own set of problems.

 

[PeteCan]

This is the response.write for sql statement

Select ProblemDate, StartTime, Service, Descrip, BusText, ILNumber FROM ILDatabase WHERE (ProblemDate BETWEEN #05/15/2003# AND #05/16/2003#) AND (StartTime >= StartTme and CloseTime <= EndTme) ORDER BY ILNumber DESC

This is the db open

Dim cnnSearch
Set cnnSearch = Server.CreateObject(&quot;ADODB.Connection&quot
cnnSearch.Open &quot;Problem&quot;

Dim objRS
set objRS=Server.CreateObject(&quot;ADODB.Recordset&quot
objRS.Open StrSQL, cnnSearch

 

[cmmrfrds]

(StartTime >= StartTme and CloseTime <= EndTme)

Are StartTme and EndTme in the ILDatabase table?

If they are parms then make sure they get resolved while building the SQL statement just like you did with the other date range.

 

[PeteCan]

StartTime and EndTime are fields within the database.

StartTme and EndTme are variables I set within the asp before doing the SQL.

 

[cmmrfrds]

Resolve the 2nd set of dates like you did the first set.

strSQL=&quot;Select ProblemDate, StartTime, Service, Descrip, BusText, ILNumber FROM ILDatabase WHERE (ProblemDate BETWEEN #&quot; & StartDate & &quot;# AND #&quot; & EndDate &&quot;#) AND (StartTime >= #&quot; & TimeValue(StartTme) & &quot;# and CloseTime <= #&quot; & TimeValue(EndTme) & &quot;#) ORDER BY ILNumber DESC&quot;

 

[PeteCan]

Ok, I've coded as suggested and the following SQL statement is generated

Select ProblemDate, StartTime, Service, Descrip, BusText, ILNumber FROM ILDatabase WHERE (ProblemDate BETWEEN #05/18/2003# AND #05/19/2003#) AND (StartTime >= #08:00:00# and CloseTime <= #07:59:00#) ORDER BY ILNumber DESC

Which looks fine and doesn't generate any errors, however it doesn't pick up any records either!!.

There is one record which has a ProblemDate of 18/05/03, a startTime=18:00 and CloseTime=05:00

Help this is driving me mad!

 

[ahmun]

What format are StartTime and CloseTime in the database?

You might try double-checking that the data stored in the database matches the format of the variables collected from your .asp page.

The # operator in the query (i.e.

#08:00:00#

) may be evaluated as

January 1, 1900 08:00:00

(or whatever is the default &quot;earliest date&quot; that the server uses) since you didn't specify a date.

If this is an Access Database, have you tried using its query builder/wizard to create a successful query and then copy the SQL generated?

Earnie Eng
If you are born once, you will die twice
If you are born twice, you will die once

 

[scriverb]

I noticed that you posted a date of 18/5/03. That format of dd/mm/yy is contrary to the date representation that are being used. I have seen numerous posts in TT that have struggled with this situation. Try changing your Regional and Language Options to mm/dd/yy see what happens.

Let me know if this changes your outcome.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???