Question Date format change on save

[Paul Clark]

I have a strange problem with date format.
The problem is that dates for day 01-12 are converted to mm/dd/yyyy on saving.
Whiles dates for day 13-31 are saved in the correct format.

There is a subform that allows updates to the main form.
The date field on both forms has the Format specified dd/mm/yyyy, as does the field in the table sand the programming just says update date field X as date field Y.

I have also tried with and without the calendar date picker

Obviously once written to the table it is reported as the wrong date.

 

[jdraw]

Where are you located? What is the date format for your Regional setting?

 

[Paul Clark]

Where are you located? What is the date format for your Regional setting?

I am in UK and the computer is set correctly for my location as dd/mm/yyyy.
Is there a default for the database then?

 

[jdraw]

The default for Access is USA format MM/DD/YY

 

[Paul Clark]

Ok it get now that problem is the SQL engine totally ignoring both my format prerance and regional settings - sounds like a bug to me!

However, how do get my code to save a date entered as dd/mm/yyyy and save it in that format?

The following code updates the table field 'Start Date' with value in the field 'CStartDate' on the subform, using a picker in dd/mm/yyyy as regional setings.

Private Sub ANOwner_Click()
Dim strSQL As String
Dim rs As Recordset
Dim dbs As Database

[FONT=&quot] ' Inserts the new owner record from screen values
strSQL = "INSERT INTO OwnershipLog (Chassis_No, Membership_No, Owner_No, [Current Owner], [Note], [Start Date])" _
& "VALUES ('" & Me.CChassisNo & "', '" & Me.CMemNo & "', " & Me.CNextOwner & ",'" & Me.CCurrent & "','" & Me.CNote & "',#" & Me.CStartDate & "#);"
DoCmd.RunSQL strSQL[/FONT]

 

[Galaxiom]

Not a bug. The engine expects date values to be "#mm/dd/yyyy#"

Any SQL commands where the date value is read from a form and concatenated into the command must be reformatted. They can be sent directly to the engine with CurrentDb.Execute

The Access project itself uses the Regional Date Format from Windows. So form references within the command are handled correctly because they are interpreted by Access before being sent to the database engine. These can only be run with DoCmd.RunSQL which handles the transalation of the form references. There is really no point concatenating the values into the command if you use RunSQL.

 

[Paul Clark]

Not a bug. The engine expects date values to be "#mm/dd/yyyy#"

Any SQL commands where the date value is read from a form and concatenated into the command must be reformatted. They can be sent directly to the engine with CurrentDb.Execute

The Access project itself uses the Regional Date Format from Windows. So form references within the command are handled correctly because they are interpreted by Access before being sent to the database engine. These can only be run with DoCmd.RunSQL which handles the transalation of the form references. There is really no point concatenating the values into the command if you use RunSQL.

[FONT=&quot]This all looks much like a number of Google replies read and that Access SQL expects mm/dd/yyyy but I am still none the wiser as to how to Actually achieve the result of saving the date in my required format. [/FONT]

 

[Galaxiom]

Format([expression],"\#mm\/dd\/yyyy\#")

 

[Paul Clark]

Format([expression],"\#mm\/dd\/yyyy\#")

Presumably 'expression' would be my CStartDate, so:
Format([CStartDate],"\#mm\/dd\/yyyy\#")

Where do I put this?

 

[Tieval]

Bit confused by all this.

Having a system set on UK date and time, I can set a date field in a table and it defaults to Medium Date 12-Nov-2015 or Short Date 12/11/2015 et cetera.

If you hold that format through forms and the like it should all work happily, I never get any issues with sorting or anything.

I suspect you may possibly get issues if you give the database to an American but then again it may work it out, I have sent a database to the States and have had no problems reported.

 

[Minty]

The "date conversion" only happens when you use SQL commands within VBA, and annoyingly only happens if the date can be misinterpreted. e.g. as the OP stated dates with days numbered 1-12. If the date cannot be American Format as in 15/06/2016 it gets it right. Can be very confusing if you are not aware if it.

Allen Browne describes it in detail here http://allenbrowne.com/ser-36.html

 

[Tieval]

Just a suspicion but are you in your statement forcing the American format into your UK format.

If you have a form with a UK format date and an sql statement putting it into a table with a UK date format whatever is in the form should go straight in. Is the ## causing it to think of it as a standard date (American) and therefore messing up? Try removing the #s.

 

[jdraw]

Paul,
I responded to a question re:adding X Business days to a Given Date. I posted the code.


Good luck with your project.

 

[Paul Clark]

Bit confused by all this.

Having a system set on UK date and time, I can set a date field in a table and it defaults to Medium Date 12-Nov-2015 or Short Date 12/11/2015 et cetera.

If you hold that format through forms and the like it should all work happily, I never get any issues with sorting or anything.

I suspect you may possibly get issues if you give the database to an American but then again it may work it out, I have sent a database to the States and have had no problems reported.

I have tried both of these and also yyyy/mm/dd, in all cases the mm/dd are reversed.

 

[Paul Clark]

Just a suspicion but are you in your statement forcing the American format into your UK format.

If you have a form with a UK format date and an sql statement putting it into a table with a UK date format whatever is in the form should go straight in. Is the ## causing it to think of it as a standard date (American) and therefore messing up? Try removing the #s.

No removing the ## turns 12/07/2016 into 30/12/1899

 

[Paul Clark]

The "date conversion" only happens when you use SQL commands within VBA, and annoyingly only happens if the date can be misinterpreted. e.g. as the OP stated dates with days numbered 1-12. If the date cannot be American Format as in 15/06/2016 it gets it right. Can be very confusing if you are not aware if it.

Allen Browne describes it in detail here http://allenbrowne.com/ser-36.html

Penny/Bomb finally dropped and got the corrent use of format from allenbrowne's link

Thanks to all who responded.