Comparing Dates through Query

I am using MS Access Database. "Followdate" is a DATE field in this database. I want to search records where the date is less than any specified date.

Followdate=request.form("date")

sql = "select * from followquery where followdate<='" & followdate & "' "

I am getting this error

Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.

Can anybody correct my Query.

Well, let's step through what you are doing. First of all, you have a table called followquery and a DATE field called Followdate. I'm assuming that the Followdate column already has record entries that you want to query. You want to compare those records against any date less than a specified date. Apparently, that date will be supplied from a form entry. Have I followed you so far?

OK, so looking at your variable.
Followdate=request.form("date")

This is probably the first thing giving you headaches. First of all, I wouldn't name the variable the same as your database field. Let's call it selectedDate. So then we have:

selectedDate=request.form("date")

OK. so now we want to set up our query. The first part is easy

sql="select * from followquery where Followdate<=

(That part gets you almost there. Make sure you pay attention to what should be capitalized and what shouldn't - it's case sensative). Now let's put my variable substitution into the statement.

sql="select * from followquery where Followdate<="'&selectedDate&'" "

Now by appearances I'd think that would work. However it won't. This Knowledge base article explains why (although it's for front page it's basically the same reason)
http://support.microsoft.com/default.as ... -us;296653

Let's follow the instructions in step 13 only. Following that example we should have:

sql="select * from followquery where (Followdate<=#::selectedDate::#) "

That should work.


This explains more about the Data type mismatch error you are getting:

http://www.macromedia.com/support/ultra ... e_2752.htm

Not 100% positive that will work, but it should be close enough to get you in the right direction. Hope it helps.

I am still getting this error.

Date Action
Microsoft JET Database Engine error '80040e07'
Syntax error in date in query expression '(Followdate<=#::selectedDate::#)'.

/follow/control.asp, line 23

I also tried fixing the date expression by using the following

selectedDate=cdate("14/09/2004")

in place of request.form

but it did not work. what to do.

Hadn't forgotten about you. I was quite busy today and just really got back around until a bit ago. I was hoping someone else would have corrected what I probably overlooked by now. I'm sure it has to do with the date data type, but not quite sure what at the moment. If I think of anything, I'll let you know.

Thanks I will wait for your reply.

Well, here's where we should focus:



I think the query is close, but the date syntax is wrong. Unfortunately, that's where I always get screwed up is with syntax. And I'm not very familiar working with dates yet. I think if we can get the correct syntax there you'll be in business.

The Following worked

Followdate=request.form("date")

if IsDate(followdate) then
sql = "select * from followquery where followdate<=#" & followdate & "# "
end if

Thanks for your help as well bye

Excellent - thanks for the update.