SQL Statement...

[dodgyone]

Could somebody please take a look at htefollowing SQL:

mySQL = &quot;SELECT * FROM [worklist] WHERE [worklist].[DateStart] >= &quot; & chr(39) & TwoDateFormOne & chr(39) & &quot; AND WHERE [worklist].[DateStart] <= &quot; & chr(39) & TwoDateFormTwo & chr(39) & &quot; ORDER BY TimeStart;&quot;

I get the error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '[worklist].[DateStart] >= '12/11/2001' AND WHERE [worklist].[DateStart] <= '13/11/2001''.
/intranet test/Testing/Worklist/SearchProcess.asp, line 97

But the SQL seems alrghit to me!

Thank You...

 

[cbokowski]

mySQL = &quot;SELECT * FROM [worklist] WHERE [worklist].[DateStart] >= &quot; & chr(39) & TwoDateFormOne & chr(39) & &quot; AND WHERE [worklist].[DateStart] <= &quot; & chr(39) & TwoDateFormTwo & chr(39) & &quot; ORDER BY TimeStart;&quot;

Shouldnt you only have the word &quot;WHERE&quot; only once?

 

[dodgyone]

I take out there WHERE and get the error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
/intranet test/Testing/Worklist/SearchProcess.asp, line 134

 

[cbokowski]

Sounds like the value of TwoDateFormOne or TwoDateFormTwo is not valid. What is the value of each of them?

 

[dodgyone]

The values are as follows:

JoinDateOne = StartDay2 & &quot; &quot; & StartMonth2 & &quot; &quot; & Right(StartYear2, 2)
TwoDateFormOne = DatePart(&quot;d&quot;, JoinDateOne) & &quot;/&quot; & _
DatePart(&quot;m&quot;, JoinDateOne) & &quot;/&quot; & _
DatePart(&quot;YYYY&quot;, JoinDateOne)

JoinDateTwo = EndDay1 & &quot; &quot; & EndMonth1 & &quot; &quot; & Right(EndYear1, 2)
TwoDateFormTwo = DatePart(&quot;d&quot;, JoinDateTwo) & &quot;/&quot; & _
DatePart(&quot;m&quot;, JoinDateTwo) & &quot;/&quot; & _
DatePart(&quot;YYYY&quot;, JoinDateTwo)

And the value for each passed over is the date (i.e. 13 November 2001 or as a value = StartDay2 StartMonth2 StartYear2)

Thanks...

 

[cbokowski]

I have found the easiest way for me to debug type mismatches is to run the asp in debug mode of VID, get the
value of mysql from the immediate window and paste it into access and try to run it. That should provide the same error.

 

[dodgyone]

The dates do come out fine though for each when doing a reponse.write on screen for them.

Not sure whether anything else is wrong but doesn't appear so!

 

[cbokowski]

try this for each date.
response.write isdate(TwoDateFormOne)

 

[dodgyone]

Both are set to true so all is fine there!

I'm looking to display the records between these two dates so could I use BETWEEN instead as is the case with Access?

Not sure whether >= and <= are used correctly in the SQL!

Thanks for taking the time to help me ;o)

 

[cbokowski]

Yes use between. also try putting # around your dates instead of single quotes '.

 

[dodgyone]

I've tried the following which works but no records are displayed on screen even though there are some in the DB for the date range:

mySQL = &quot;SELECT * FROM [worklist] WHERE [worklist].[DateStart] >= &quot; & &quot;#&quot; & TwoDateFormOne & &quot;#&quot; & &quot; AND [worklist].[DateStart] <= &quot; & &quot;#&quot; & TwoDateFormTwo & &quot;#&quot; & &quot;ORDER BY DateStart, TimeStart;&quot;

Nearly there ;o)

 

[cbokowski]

Does it run correctly in access?

 

[lobstah]

okay, if you copied the sql statement directly from your code (and didn't just retype it in here) then i see one problem:

mySQL = &quot;SELECT * FROM [worklist] WHERE [worklist].[DateStart] >= &quot; & &quot;#&quot; & TwoDateFormOne & &quot;#&quot; & &quot; AND [worklist].[DateStart] <= &quot; & &quot;#&quot; & TwoDateFormTwo & &quot;#&quot; & &quot;ORDER BY DateStart, TimeStart;&quot;

you need a space between the 2 characters i highlighted in red.

also, i would remove the extraneous &quot; & &quot; thus:

mySQL = &quot;SELECT * FROM [worklist] WHERE [worklist].[DateStart] >= #&quot; & TwoDateFormOne & &quot;# AND [worklist].[DateStart] <= #&quot; & TwoDateFormTwo & &quot;# ORDER BY DateStart, TimeStart;&quot;

not sure if that would make a diff in the processing, but it sure looks cleaner.

 

[dodgyone]

The following code brings up results but not all of them! When search between 12/11/01 AND 13/11/01 it only brings up the 13/11/01 records! I require the 12/11/01 to be displayed as well...

mySQL = &quot;SELECT * FROM [worklist] WHERE [worklist].[DateStart] BETWEEN &quot; & &quot;#&quot; & TwoDateFormOne & &quot;#&quot; & &quot; AND &quot; & &quot;#&quot; & TwoDateFormTwo & &quot;#&quot; & &quot;ORDER BY DateStart, TimeStart;&quot;

Why are there always problems after problems... it should work! (

 

[dodgyone]

It works but had to cheat a little...

Take the day for the from date and minus it by 1 before entering it into the date concatentation etc. Not ideal but at least it works!

Unless you have any clues ;o)

 

[jgau]

Hello,

Just one suggestion: do you use date format?
If so, shouldn't you use a CONVERT function?
What is the TwoDateFormOne format? It seems to be character. SQL statement needs to &quot;speak&quot; in the same language. That is why the variables TwoDateFormXxx should be converted into dates.