Comparing Dates through Query

September 21st, 2004, 1:14 pm

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.

[ATNO/TW]

September 21st, 2004, 2:02 pm

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.

September 22nd, 2004, 6:57 am

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.

[ATNO/TW]

September 22nd, 2004, 7:12 pm

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.

September 23rd, 2004, 7:50 am

Thanks I will wait for your reply.

[ATNO/TW]

September 23rd, 2004, 8:06 am

Well, here's where we should focus:

Line number On/Off

Code: Select all

Syntax error in date in query expression '(Followdate<=#::selectedDate::#)'

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.

September 24th, 2004, 6:04 am

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

[ATNO/TW]

September 24th, 2004, 6:38 am

Excellent - thanks for the update.