Problems using DATEDIFF in SELECTS'S

[holty]

You may have read my other post, I am having problems with using DATEDIFF in a SELECT statement.

When I query this statement in Access I get the correct results, here is the statement.

SELECT Count(tblAdvert.VWModelID) FROM tblAdvert WHERE DateDiff ("d", [tblAdvert].[Date], NOW()) <= 14 AND tblAdvert.VWModelID = 2

When I query it using ASP and the same statement I get:-

Expected end of statement

/webpub/index.asp, line 285

strSQL = "SELECT Count(tblAdvert.VWModelID) FROM tblAdvert WHERE DateDiff ("d", [tblAdvert].[Date], NOW()) <= 14 AND tblAdvert.VWModelID = 2"
----------------------------------------------------------------------------^


So I change the "d" to be ""d"" and now get:

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

Same results if I change the "d" to be day also.

Has anyone else done a DATEDIFF in a select statement? Anyideas where I'm going wrong?

Thanks

[Roy Sinclair]

It isn't intuitive but:

SELECT Count(tblAdvert.VWModelID) FROM tblAdvert WHERE DateDiff (d, [tblAdvert].[Date], NOW()) <= 14 AND tblAdvert.VWModelID = 2

Try it with NO quotes around the d.

[holty]

Roy,

Tried it mate - still got the [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Its wierd that it works fine in access when querying the data but not in ASP......

[landon11]

you might can try:

strSQL = "SELECT Count(tblAdvert.VWModelID) FROM tblAdvert WHERE DateDiff ('d', [tblAdvert].[Date], NOW()) <= 14 AND tblAdvert.VWModelID = 2"

or

dim d
d = "d"
strSQL = "SELECT Count(tblAdvert.VWModelID) FROM tblAdvert WHERE DateDiff ('" & d & "', [tblAdvert].[Date], NOW()) <= 14 AND tblAdvert.VWModelID = 2"


I did not test this

[Roy Sinclair]

I tested what I posted but using SQL Server and it worked just fine. Evidently Access is wanting something else entirely.

[holty]

Was thinking of a way around this, so I looked into the DateAdd function.

Here is my code:

Dim 14DaysAgo
14DaysAgo = DateAdd("d", -14, Now())
strSQL = "SELECT Count(tblAdvert.VWModelID) FROM tblAdvert WHERE tblAdvert.Date > '" & 14DaysAgo & "' AND tblAdvert.VWModelID = 2;"


I still get an error though regarding the exception. Any ideas?

[whammy]

Ok, let's have an overview of your database structure... can you give us the field names/datatypes of the fields in question?

That way I can create my own test db and see what's up with your SQL statements, i.e.:...

tblAdvert
=======

VWModelID|datatype|length
Date|datatype(I'm assuming datetime)

or just post your db here (assuming it doesn't have any references to your website that hackers could exploit!!!)

Then we can really get to the root of the problem. ;)

[whammy]

Ok, I created a table with my assumptions above, and the first thing that happened is Access didn't like the name "Date" for a field.

Try [Date] instead - or [tblAdvert].[Date] - as you tried in previous posts... but apparently there was something else wrong.

I would actually go as far as to tell you to rename this field to AdDate, or something - so we can eliminate the possibility of a reserved word being used.

:)

[dominicall]

Here's a url for Access/SQL reserved words...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_9oj7.asp

whammy - might be worth making this a sticky.... reserved word problems seem to keep coming up every now and then.

dominicall

[whammy]

I'll keep that in mind... can you private message me that so I don't forget it?

:D

[holty]

Cheers guys, I think we've cracked it!

dim d
d = "d"
strSQL = "SELECT Count(tblAdvert.VWModelID) FROM tblAdvert WHERE DateDiff ('" & d & "', [tblAdvert].[AdDate], NOW()) <= 14 AND tblAdvert.VWModelID = 2"

It returned the correct value :)

Think it may be a good idea about the reserved words!!