Date Format keeps changing to mm/dd/yyyy

[RascalBird]

Hi there

I hope you can help me out, I've been going crazy trying to work out this problem.

I'm using Access 2010 and have a SQL INSERT INTO statement to automatically create a new record in a table.

I have another form where I have successfully forced the date to be inserted in the "dd/mm/yyyy" format and everything is perfect. For some reason this time it is not working.

I have tried to use

paydue = format(date,"dd/mm/yyyy")

on this occasion and am beating my head against a brick wall.:banghead:

I have checked regional settings, ensured field format settings are off and still no luck.

Any thoughts as to what I'm missing?? Below are 3 of the lines of code relating to one of the date fields.

dim paydue as date

paydue = Format(Date, "dd/mm/yyyy")

paydue = DateAdd("m", 3, Me.BASPaymentDueDate.Value)

Thanks in advance

 

[Brianwarnock]

VBA always uses the US format whatever your regional settings.. This is from an article by Allen Browne

2. Wrong Formatting in Code

In VBA code, delimit dates with the "#" symbol. Regardless or your regional settings, Access expects these literal dates to be in the American format, e.g. #12/31/1999#.

To demonstrate this, enter any date in the Criteria row under a date field in Query Design, and then switch to SQL View. In Query Design view, you see the date according to your local settings, but the SQL statement uses mm/dd/yyyy format.

SQL clauses are not always obvious, e.g. the Filter of a form, the WhereCondition of OpenReport, or the third argument of a domain aggregate function. Examples:

DoCmd.OpenReport "MyReport", acViewPreview, , "InvoiceDate > #12/31/2000#"
Debug.Print DLookup("StudentID", "tblStudent", "EntryDate = #6/30/1953#")
strSQL = "SELECT * FROM tblDonation WHERE DonationDate > #" & Format(Me.StartDate, "mm\/dd\/yyyy") & "#;"
The third example demonstrates how to concatenate a date into a SQL string. The Format() function is essential to force the date into American format. Unfortunately, Format() replaces the slashes with the date separator character defined in Control Panel | Regional Settings, so you must specify literal slashes in the format string by preceding the slash with backslashes.

Since this is something you do frequently, you may find it easy to call a small wrapper function for concatenating date strings. The example below formats it as:

Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time component,
' or a date/time format if it does.
'Author: Allen Browne. allen@allenbrowne.com, June 2006.
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
End Function


Hope it helps

Brian

 

[RascalBird]

Thanks Brian

I've read Allen Browns article but was hoping that there was something else that might have been causing the problem.

Any thoughts as to why my current code works for one of my forms but not on this particular one?

 

[RascalBird]

OK, now I'm struggling

I've created a SQLDate function as allen brown suggested but I can't call it without a datatype mismatch - I'm missing something basic, please help!

 prepdue = SQLDate([prepdue])