Dumb ASP/SQL Query

[Dante Hicks]

OK, I am using ASP and MS Access to display some records.

I have the following lines in ASP which I am using to set up the sql query.

dim eff_date
eff_date = rs.Fields("eff_date")
dim letcode
letcode = rs.Fields("letcode")
dim bsize
bsize = rs.Fields("bsize")
dim pcode
pcode= rs.Fields("pcode")
dim points
points = rs.Fields("points")	
	 
sql2 = "SELECT * from property_list where pcode = '" & pcode & "' and letcode =  '" & letcode & "' and bsize  = '" & bsize & "' and eff_date > " & eff_date & " order by eff_date asc"

Now, if I have a look at the sql2 variable I've created it's as follows :

SELECT * from property_list where pcode = 'HSE' and letcode = 'EM1' and bsize = '3' and eff_date > 25/05/02 order by eff_date asc

(the numbers/values can be ignored - they're from elsewhere). Which is exactly what I want. However, when I come to run the SQL I get _ALL_ records, not just the ones after 25/05/02. WHY? The eff_date column in access is a datetime datatype, and the order by bit of the query works fine (i.e. Access realises it is a date, and is capable of putting it in date-order), so why is the SQL effectively ignoring my "eff_date > 25/05/02" bit? Do I have set up the datatype differently or something? Am I having a brain haemorage or would the above work in MySQL?

[Mit]

Can't just remember, but i've seen similar to that before, tried putting the date in "s ?

[Dante Hicks]

Originally Posted by Mit

Can't just remember, but i've seen similar to that before, tried putting the date in "s ?

Yeah, but I just get :

Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

[Caesar2]

Originally Posted by Dante Hicks

... eff_date > " & eff_date & " order by eff_date asc"

should be:

... eff_date > #" & eff_date & "# order by eff_date asc"

[Dante Hicks]

Originally Posted by Caesar2

should be:

... eff_date > #" & eff_date & "# order by eff_date asc"

\o/

You rock, etc.

Ta.

[Caesar2]

no problem

it's one of the abnormalities in access like using:

WHERE columnname LIKE "*...*"

instead of

WHERE columnname LIKE '%...%'