[RESOLVED] Simple problem...I think

[Rincewind]

All i'm trying to do is retrieve records from the database which match a certain date, but for some reason i keep getting a 'type mismatch' error. I've writen it simply like this to show the syntax i have used.

temp = "6/12/2006"
GetData ("SELECT * FROM tblcosts WHERE start_date = '" & CDate(temp) & "'")

Could someone please to me what i'm doing wrong? I suspect its something stupidly simple. I've tried it with and without using 'cdate'

Cheers

[ganeshmoorthy]

Is it access...then use # around the date value or use DateValue function

[Rincewind]

Is it access...then use # around the date value or use DateValue function

I tried this and still get the same error.

temp = "6/12/2006"
GetData ("SELECT * FROM tblcosts WHERE start_date = '" & DateValue(temp) & "'")

The error is:

Unable to connect
data type mismatch in criteria expression

I know my code used to connect to the database is fine cos if i type

getdata("SELECT * FROM tblcosts")

it will display all the records.

[ganeshmoorthy]

it should be

"SELECT * FROM tblcosts WHERE start_date = DateValue ('" & Format(temp,"DD/MM/YYYY") & "')"

and what does this GetData...what is the data type of Start_date in your database...what database you are using...

[Rincewind]

Getdata is just a procedure which connects to an Access database via ADODB.

[Hack]

What is the field type into which you are trying to store this date?

[Rincewind]

in access it is date/time

[Hack]

in access it is date/time

Then you will need to encapsulate you date variable with the # signs as ganeshmoorthy pointed out in Post #2. This is something that Access requires.

[Rincewind]

Sorry for being dumb but i did try what i thought he meant and it says the line isn't writen properly. I've done it like this

GetData ("SELECT * FROM tblcosts WHERE start_date = '" & #temp# & "'")

Is that wrong yeah?

[si_the_geek]

It is.. they are supposed to be used in place of ' , eg:

GetData ("SELECT * FROM tblcosts WHERE start_date = #" & temp & "#")

[Rincewind]

Cool cheers guys...you're legends