INSERT INTO Date issue

[IanHallett]

Hi,

I have the following code which works pefectly well with the exception of the date format:

DoCmd.RunSQL "INSERT INTO Timesheet (EngineerNo,ContractNumber,Chargeable,ChargeCodeNo,Hours,TimesheetDate,Rate)" _
& " VALUES (" & Me!EngineerNo & ",'" & Me!ContractNumber & "'," & Me!Chargeable & "," & Me!ChargeCodeNo & "," & Me!Hours & ",#" & Format$(Me!Timesheetdate, "dd-mm-yyyy") & "#," & Me!Rate & ")"

If I have a date of 01/04/2008, the value posted into the Timesheet table is 04/01/2008. The date format is ShortDate dd/mm/yyyy in the timesheet table all regional settings on my PC are set to UK etc.

Any help would be much appreciated as I am pulling my hair out and I didn't have much to start with.

 

[PWise]

try
DoCmd.RunSQL "INSERT INTO Timesheet (EngineerNo,ContractNumber,Chargeable,ChargeCodeNo,Hours,TimesheetDate,Rate)" _
& " VALUES (" & Me!EngineerNo & ",'" & Me!ContractNumber & "'," & Me!Chargeable & "," & Me!ChargeCodeNo & "," & Me!Hours & ",#" & Me!Timesheetdate & "#," & Me!Rate & ")"

 

[PWise]

format changes the date to a string

 

[IanHallett]

Thanks, I tried that but got exactly the same result.

 

[SkipVought]

Hi,

Date/Time values are NUMBERS, like right now in North Texas the Date/Time value is 39539.48403.

That VALUE can for FORMATED to DISPLAY any number of different ways -- US, UK and skads of custom ways like Tuesday Apr 01, 2008 11:37.

If you are useing the Format function to return a STRING, you must CONVERT the string to a Date Value, which in Access can be done with # delimiters or using the DateValue function.

IMHO, I'd feel safer in the conversion using a yyyy/mm/dd UNAMBIGUOUS format, being that formats like mm/dd... or dd/mm... can yield unexpected conversions in some cases.


Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[KenReay]

Hi no matter what the windows locale settings are for date format, in SQL statements date must be in "american" format or "yyyy/mm/dd" format as skip says:

so

DoCmd.RunSQL "INSERT INTO Timesheet (EngineerNo,ContractNumber,Chargeable,ChargeCodeNo,Hours,TimesheetDate,Rate)" _
& " VALUES (" & Me!EngineerNo & ",'" & Me!ContractNumber & "'," & Me!Chargeable & "," & Me!ChargeCodeNo & "," & Me!Hours & ",#" & Format$(Me!Timesheetdate, "mm/dd/yyyy") & "#," & Me!Rate & ")"

or

DoCmd.RunSQL "INSERT INTO Timesheet (EngineerNo,ContractNumber,Chargeable,ChargeCodeNo,Hours,TimesheetDate,Rate)" _
& " VALUES (" & Me!EngineerNo & ",'" & Me!ContractNumber & "'," & Me!Chargeable & "," & Me!ChargeCodeNo & "," & Me!Hours & ",#" & Format$(Me!Timesheetdate, "yyyy/mm/dd") & "#," & Me!Rate & ")"

should do it

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[IanHallett]

Thanks guys, that worked a treat. Much appreciated.

 

[InkyRich]

Just as an addition to your discussion I find that VBA.Date works just as well.
Inky

A fool and his money are soon parted - so you might as well send it to me!