Date format from VBA Added to Table not correct and date is changed.

[mshakeelattari]

I am using Windows 10, MS Access 2016. I have date format in settings or regional settings (control panel) as
Short Date: dd/MM/yyyy
LOng Date: dd MMMM, yyyy.

In my Access Database, I have a table "LoginLogReport" with the following fields:

User ID: (Number)
Date: (Date/Time, Short Date)
Time: (Date/Time, Long Time)
Type: (Short Text, 25)

In a login form, in the click event of a Login command button, I am using the following code alongwith the other codelines.

strSQL = "INSERT INTO LoginLogReport (UserID, [Date], [Time], Type) " & _
            "VALUES (" & Me.cboUser & ", #" & Date & "#, #" & Time & "#, 'Login')"
    CurrentDb.Execute strSQL, dbFailOnError
 

All is fine but the Date field shows the date (e.g., 06/08/2016, i.e., August 06, 2016) as (08/06/2016, i.e., June 08, 2016). When, in the table, I click on the floating calendar control, it takes me to the latter mentioned date. Please help me where is the fault?

Please help as soon as possible. Thank you.

[mshakeelattari]

Is there no solution for this problem?

[PhilOfWalton]

Any sensible country would use a date format yyyy mm dd. The Brits, in their perverse way reverse this and are equally illogical in putting adjectives before nouns.
The Yanks are even more illogical in that their date format is completely random mm dd yyyy. Anyway, we're stuck with Microsoft being an American company, and we Brits have to overcome the problem.

I am unsure but this probably will sort out your problem

?cdate(format(#10/05/16#,"Medium Date"))
05/10/2016 
 

Anyway, that's what happens in the debug window.

Phil

[mshakeelattari]

Thank you for your reply. However, unfortunately this did not work for me.

[mshakeelattari]

When I used this code, it worked:

strSQL = "INSERT INTO LoginLogReport (UserID, [Date], [Time], Type) " & _
            "VALUES (" & Me.cboUser & ", " & Format(Date, "\#mm\/dd\/yyyy\#") & ", #" & Time & "#, 'Login')"

Now I want to ask that, will it not create any issue if the app is used on a computer with some different Regional Settings of the Control Panel (or system locale),in which the date format is different one?