converting date format in an access table with sql update

I have a problem converting dates while updating an SQL table in VB under access: here is my code:

'Excel 开发者_开发知识库format date conversion
strSQL = "UPDATE tblBlotterINTLControl " & _
            "SET tblBlotterINTLControl.TradeDate = CVDate(TradeDate), " & _
                "tblBlotterINTLControl.SettleDate = CVDate(SettleDate);"
DoCmd.RunSQL strSQL

I obtain an error for each row: "type conversion error" I have my tables in the right format though, please help thanks

EDIT: I have to say that a SELECT request works but an UPDATE request doesn't! why? how?

What are the data types of the TradeDate and SettleDate fields in the Access table tblBlotterINTLControl?

SELECT TypeName(TradeDate) AS TypeOfTradeDate, TypeName(SettleDate) AS TypeOfSettleDate
FROM tblBlotterINTLControl;

Please paste that query into SQL View of a new query in Access, run it and show us what you get back.

The reason I asked is because the SET statements in your UPDATE query puzzle me.

SET tblBlotterINTLControl.TradeDate = CVDate(TradeDate)

If the TradeDate field is Date/Time datatype, using the CVDate() function on it doesn't accomplish anything.

If the TradeDate field is text datatype, CVDate() will give you a variant date, but you can't store that Date/Time value back to your text field.

Maybe you would be better off using the Format() function. Here is a sample I copied from the Immediate Window:

? Format("2011/01/01", "d mmm yyyy")
1 Jan 2011

Try CDate instead of CVDate.

CVDate actually returns a Variant of type vbDate and is only around for backwards comparability. Maybe that's what causing the problems.