Date format

[Romio_1968]

I have a table containing a Date/Time field
The system date format is "dd-mm-yyyy"
A weird thing happends

If i am using Now() function to add a date value in the table using SQL, the month and day are inverted

Example 1>

' Add 3 consecutive records in "TDJunction" table
  Dim db2 As DAO.Database
  Dim rs2 As DAO.Recordset
  Set db2 = CurrentDb
  Set rs2 = db2.OpenRecordset("TDJunction")
 
  For i = 1 To 3
    If Me("Rank" & i) <> 0 Then
      rs2.AddNew
      rs2!Title_IDFK = Me.Title_ID
      rs2!ClassCode_FK = Me("ClassCode" & i)
      rs2!Rank = Me("Rank" & i)
      rs2!Timestamp = Now()
      rs2.Update
    End If
  Next i

Example 2

Public scrapTmstp As Date
(...)
'Form_Load event

scrapTmstp = Now()

(...)
'Click Event
' Insert a new record into the "ScrappedInventory" table with the same Inventory_No, Title_ID, and current timestamp
  strSQLscrap = "INSERT INTO ScrappedInventory (Inventory_No, Title_IDFK, ScrapTimestamp, AddTimestamp) " & _
                "VALUES (" & invNo & ", " & titleID & ", #" & scrapTmstp & "#, #" & addTmstp & "#)"
  db.Execute strSQLscrap

In all tables, if i try to use Now(), the d-m inversion occurs

Yet, if I have a control form with default value set to Now(), the value is passed corectly, so the system formats seems to work
it occurs only by using SQL statements

 

[bob fitz]

You may find the following of interest: http://allenbrowne.com/ser-36.html

 

[sonic8]

it occurs only by using SQL statements

You need to explicitly format dates in SQL statements in US or ISO format. See: https://codekabinett.com/rdumps.php?Lang=2&targetDoc=vba-sql-string-tutorial#sql-strings-dates

 

[theDBguy]

' Insert a new record into the "ScrappedInventory" table with the same Inventory_No, Title_ID, and current timestamp strSQLscrap = "INSERT INTO ScrappedInventory (Inventory_No, Title_IDFK, ScrapTimestamp, AddTimestamp) " & _ "VALUES (" & invNo & ", " & titleID & ", #" & scrapTmstp & "#, #" & addTmstp & "#)" db.Execute strSQLscrap

Just curious, what happens if you try it this way?

' Insert a new record into the "ScrappedInventory" table with the same Inventory_No, Title_ID, and current timestamp
  strSQLscrap = "INSERT INTO ScrappedInventory (Inventory_No, Title_IDFK, ScrapTimestamp, AddTimestamp) " & _
                "VALUES (" & invNo & ", " & titleID & ", Now(), Now())"
  db.Execute strSQLscrap

PS. Not sure which of the two needed the Now() value, I used it on both just for testing.