[RESOLVED] Simple problem...I think

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.

VB Code:

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

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

Quote:

---

Originally Posted by ganeshmoorthy

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

---

I tried this and still get the same error.

VB Code:

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

VB Code:

getdata("SELECT * FROM tblcosts")

it will display all the records.

it should be

VB Code:

"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...

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

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

in access it is date/time

Quote:

---

Originally Posted by Rincewind

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.

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

VB Code:

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

Is that wrong yeah?

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

VB Code:

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

Cool cheers guys...you're legends