Hello.

I've been trying to use a form value "vStartWeekOnWhatDay" to select only that data from the database that is >= the "vStartWeekOnWhatDay" value...


My select statement:
--------------------------------------------------------
sSQL = "SELECT * FROM List WHERE date >= '"& vStartWeekOnWhatDay & "' ORDER BY date"
--------------------------------------------------------


Error received:
--------------------------------------------------------
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
--------------------------------------------------------


Any ideas? I've tried cdate(), formatDate(x,x), even separating the date variable into "day"/"month"/"year"

Thank you for hte help.

please check what vStartWeekOnWhatDay variable stores.

sSQL = "SELECT * FROM List WHERE [date] >= '"& vStartWeekOnWhatDay & "' ORDER BY date"

In the above SQL statement, "date" field is of what datatype?


Om Prakash

thanks for the message...

The database "date" datatype is date/time (medium date)... I tried changing to short date with no luck either.

The variable "vStartWeekOnWhatDay" = 22/2/2005 (when I do a response.write)

thanks in advance.

Access expects a date to be enclosed in #s...

Jonax is correct...

Query should be:

sSQL = "SELECT * FROM List WHERE [date] >= #"& vStartWeekOnWhatDay & "# ORDER BY date"



Om Prakash

Thanks guys... after a bit of learning I figured it out based on Jonax's response about encapsulating the #dateVariable#... I'm just here to post my working code to help someone else (thank you both):

----------------------------------------------------------------

vStartWeekOnWhatDay = request.form("changeWeekToDate") 'grabs the date value from the form

if vStartWeekOnWhatDay = "" then
    'set to today if no form value has been set
    vStartWeekOnWhatDay = date()
end if

'I had to break the date down into mm/dd/yyyy

vStartWeekOnWhatDayDay = day(vStartWeekOnWhatDay)
vStartWeekOnWhatDayMonth = Month(vStartWeekOnWhatDay)
vStartWeekOnWhatDayYear = Year(vStartWeekOnWhatDay)

vStartWeekOnWhatDay = vStartWeekOnWhatDayMonth&"/"&vStartWeekOnWhatDayDay&"/"&vStartWeekOnWhatDayYear

sSQL = "SELECT * FROM List WHERE date >= # " & cDate(vStartWeekOnWhatDay ) & " # order by date"

----------------------------------------------------------------
~happy coding~