ASP Help! mismatch error

[mdwebster]

Trying to display all the records where printdate is between variables first date and last date but get 'Data type mismatch in criteria expression' - please help im going nuts!


strSQL = "SELECT * FROM tbl_jobs WHERE accounthandler='" & "DT" & "' AND type='" & "Press" & "' AND printdate>='" & firstdate & "' AND printdate<='" & lastdate & "'

objRS.Open strSQL, objconn

[Daemonspyre]

1) You are missing a last double quote on the end

2) Are DT and Press variables or constants? If they are constants, then you don't need to add them using concatenates.

strSQL = "SELECT * FROM tbl_jobs WHERE accounthandler='DT' AND type='Press' AND printdate>='" & firstdate & "' AND printdate<='" & lastdate & "'"

objRS.Open strSQL, objconn

[mdwebster]

Originally Posted by Daemonspyre

1) You are missing a last double quote on the end

2) Are DT and Press variables or constants? If they are constants, then you don't need to add them using concatenates.

strSQL = "SELECT * FROM tbl_jobs WHERE accounthandler='DT' AND type='Press' AND printdate>='" & firstdate & "' AND printdate<='" & lastdate & "'"

objRS.Open strSQL, objconn

The statement does have a double quote on the end

strSQL = "SELECT * FROM tbl_jobs WHERE accounthandler='DT' AND type='Press' AND printdate>='" & firstdate & "' AND printdate<='" & lastdate & " ' "

and still returns the error. firstdate + lastdate are variables.

[Daemonspyre]

In your original post, it didn't. Sorry about that!

Instead of doing a objRS.Open, do a

response.write strSQL
response.flush

Look at your SQL statement. That will help you debug. It may be that you are firstdate and/or lastdate are not returning as dates that your DB likes.

HTH!

[ghell]

You could also try using CDate() to make sure that they are valid dates and FormatDateTime to make sure that if they are dates they are in the correct format.

Should all the things you have in 's be in 's? If it is a number or whatever it shouldnt be. Dates can be in ' or #

[degsy]

What database are you using?
What is the field data type?