Formatting Issue

[aziz rasul]

I have the following SQL string.

strSQL = "INSERT INTO " & strTableName & " (NPC, FIN_PERIOD, FIN_WEEK, FINANCIALYEAR, LINES_REQUESTED, LINES_SUPPLIED, REQ_QTY, SUP_QTY, TOT_COST, TOT_SALES, DATETIMEVALUE) " & _
"VALUES ('" & rst.Fields(0) & "', " & rst.Fields(1) & ", " & rst.Fields(2) & ", " & Forms!frmRefreshData!cboYear & ", " & rst.Fields(3) & ", " & rst.Fields(4) & ", " & rst.Fields(5) & ", " & rst.Fields(6) & ", " & rst.Fields(7) & ", " & rst.Fields(8) & ", #" & Now() & "#);"

In the [DATETIMEVALUE] field I get a typical value like

05/06/2023 11:30:05

However when I hover the cursor in the code over the Now() function, I get 23/06/05 11:30:05 which is how I want it. How can I use the Format function so that I can get this?

 

[KeithIT]

Format it as a short date, and make sure you are using the American system for dating. Search the forum for this, I'm pretty sure SJ put something up here yesterday about that format.
Keith

 

[KeithIT]

sorry, not the American system... The European system. One of these days I'll learn to read before I reply.

 

[aziz rasul]

This is what I changed the SQL string to -

strSQL = "INSERT INTO " & strTableName & " (NPC, FIN_PERIOD, FIN_WEEK, FINANCIALYEAR, LINES_REQUESTED, LINES_SUPPLIED, REQ_QTY, SUP_QTY, TOT_COST, TOT_SALES, DATETIMEVALUE) " & _
"VALUES ('" & rst.Fields(0) & "', " & rst.Fields(1) & ", " & rst.Fields(2) & ", " & Forms!frmRefreshData!cboYear & ", " & rst.Fields(3) & ", " & rst.Fields(4) & ", " & rst.Fields(5) & ", " & rst.Fields(6) & ", " & rst.Fields(7) & ", " & rst.Fields(8) & ", #" & Format(Now(), "Short Date") & "#);"

But I obtained 05/06/2023 in the table?

 

[Bat17]

you need to use the US style in the query, try something like :-

#" & Format(Now(), "MM/DD/YYYY") & "#


Peter

 

[aziz rasul]

I wanted the UK format. Taking your approach I managed to get it write. Thanks all.

strSQL = "INSERT INTO " & strTableName & " (NPC, FIN_PERIOD, FIN_WEEK, FINANCIALYEAR, LINES_REQUESTED, LINES_SUPPLIED, REQ_QTY, SUP_QTY, TOT_COST, TOT_SALES, DATETIMEVALUE) " & _
"VALUES ('" & rst.Fields(0) & "', " & rst.Fields(1) & ", " & rst.Fields(2) & ", " & Forms!frmRefreshData!cboYear & ", " & rst.Fields(3) & ", " & rst.Fields(4) & ", " & rst.Fields(5) & ", " & rst.Fields(6) & ", " & rst.Fields(7) & ", " & rst.Fields(8) & ", #" & Format(Now(), "DD/MM/YYYY HH:NN:SS") & "#);"