Access VBA submit Date - American Format Issue

[tark221]

Hi Everyone,

I'm hoping someone will enlighten me with this problem as this is one of the most frustrating things I have come across.

I have an Access Database with an Access Front End. When the user clicks submit on the front end it submits a few details including the current date. I've tried the below code all resulting in the table storing the date as american format instead of UK. Other than possibly changing the computers regional settings which could be difficult as they use thin clients how else can I fix this issue.

Sub Example
Dim Current_Date as Date

Current_Date = Date()

End Sub

Sub Example
Dim Current_Date as Date

Current_Date = format(Date(),"dd/mm/yyyy")

End Sub

 

[tark221]

For anyone who ever had an issue with the above, found a solution, just don't use sql, instead use DAO, I normally don't use it but I have no issues as of yet. Hope this helps.

Private Sub Command6_Click()
Dim dba As DAO.Database
Dim rcord As DAO.Recordset

Set dba = CurrentDb
Set rcord = dba.OpenRecordset("TBL_TEST")

rcord.AddNew
rcord!CUSTOMER_NAME = [Forms]![FRM_HOME]!TB_CUSTOMER_NAME
rcord!TRANSACTION_DATE = Format(Date, "DD/MM/YYYY")
rcord.Update
End Sub

 

[Micron]

Your code samples don't provide enough information to advise as to what the problem might be. Your date variable exists only at the module level, so the result may have been due to how it was written to the table, but more so I expect the field in the table itself is not formatted correctly. You can apply the format to the table field itself in design view, which should take care of the problem. You'd enter dd/mm/yyyy in the format line of the property sheet. To use what looks to me like a backwards date separator, I think you'd need dd\\mm\\yyyy. Coding to achieve your format is a bit of a hack and will have to be implemented everywhere.