This is fine Tom. I know where you're coming from.
This problem is fundamentally down to the confusion of how dates are
stored and how they are
formatted when displayed. Assuming a DateTime field or variable is used, dates are always
stored as a double-precision value. This doesn't change whichever country or defined locale your PC is set up for. How these values are formatted by default though, certainly does change depending on the locale. Unfortunately, when dealing with SQL (creating a SQL string to execute from within VBA code), putting the date in involves formatting in one way or another. The standard for SQL (so this is not open to variation across the world, unlike the display format) is m/d/y - just like they use in the US (See
Literal DateTimes and Their Delimiters (#)).
What this means is that, to create professional, portable, code you need to format the date value explicitly when adding it into a SQL string (No matter where in the world you are writing your code). If you were to use the following, then your problem would disappear :
- strSQL = "UPDATE [BanquetMaster] " & _
-
"SET [Banquet_amount] = " & VNewfee & ", " & _
-
" [Banquet_date = #" & Format(VBWBDate, "m/d/yyyy") & "# " & _
-
"WHERE ([banquet_name] = '" & VMatTitle & "')"