Having problems with passing international date to another table

[CD Tom]

Here's my problem. I have a person in New Zealand that I'm having problems with the date. Everything works fine except I have a date field in one form that updates another table with the date. I'm using an Update statement to update the table, if I look at the date format that is being sent it is in the correct format DD/MM/YYYY but when I bring up the other table the date is in the format MM/DD/YYYY If I enter the date into that table it comes out with the correct format. I understand how Access handles dates but I don't know how to fix this problem. Hopefully someone can point me in the correct direction.
Thanks

[NeoPa]

Why don't you explain what you want help with. I expect the SQL of the UPDATE statement wouldn't hurt either.

You say you understand how dates work in Access, but I have to say that such an understanding isn't evident from what you do say. I'm hearing much confusion, that I think we can certainly help with. We need a question though, to start with.

[CD Tom]

Sorry about that, in my program the user (from New Zealand) can either select a date from a drop down calendar (I'm using clsmonthCal written by Stephen Lebans) or enter the date, the date is saved in a variable VBWBDate this variable show the date in the correct format DD/MM/YYYY. This date is then used to update another table using the SQL update statement.

"Update BanquetMaster set Banquet_amount = " & VNewfee & ", Banquet_date = #" & VBWBDate & "# where banquet_name = '" & VMatTitle & "'"

when you look at the table BanquetMaster the Banquet_date has the correct date only the format is MM/DD/YYYY. Banquet_date is set as short date. I hope this explains my problem better, if not I'll try again.
Thanks

[NeoPa]

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 & "')"

[CD Tom]

Thanks very much. That seem to do the trick, I learn something every day.

[NeoPa]

CD Tom:
I learn something every day.

That's good news for us Tom. We love that people are learning (BTW I continue to learn new things here myself too).