Strange dates...

[guldo76]

Sorry for bothering for a likely-easy thing, but I can't manage properly with dates.
I have a table I fill in by means of vbcode, and all's fine but the date. I have a date, say, from a txtbox. In vb I see it's like "18/07/02" for 18 july 2002. But as the sql append it to the table, it's appended as "02/07/18", resulting in 2 july 2018! And in fact it results (in vb) greater than "18/07/02", so it's more than just formatting...
I think the problem could be due to the fact that my MSAccess is italian:
italian: dd/mm/yy - 18/07/02
while 02/07/18 seems to be 18/07/02 set in yy/mm/dd, japanese-like! But as it's appended into the table, it turns out to be 2 july 2018.

So: the sql statement is right; I tried with "#" and formulas such as DateValue and DateSerial, but nothing changed.
Help me, please!

Thanks,
Guldo

 

[Rich]

Post the code

 

[Alexandre]

Guldo,

You are right that the pb comes from the difference between your regional settings and the date format expected by Access when processing SQL (US date format).
You should make a function to wrap the dates you intend to pass to queries:

Public Function SQLDate (dDate as Date) As String
SQLDate = Format$(dDate, "mm/dd/yyyy")
End Function

If you are not working directly with SQL but with parameters, you would adapt the above to return a date instead of a string.

BTW, I believe we had another thread pending but I am afraid lost its track...

 

[guldo76]

Thanks, Alexandre!

It worked.
I just had to put "#" in the sql.
Moreover, declaring the function as 'date' resulted in an awkward "31/12/1899"!!

Thank you,
Guldo

 

[Pat Hartman]

"31/12/1899" is the result of moving 0 to a date field.