Keeping a date as UK when updating through SQL

[Andrew Fairbairn]

I'm using a VB backend to add and update records to a database via SQL, but the dates are changed to US format when I need them in UK format.

How can I make it so that either they're stored as UK times, or the US time is displayed as UK and reports and queries that are trying to parse them for month properties function correctly?

The other problem is that if the date inputted is the 28/07/2007, for example, it will keep it in that format, but if the date inputted is the 01/07/2007 then the date stored is 07/01/2007, so I can't just display the date differently and tell reports to look at dd rather than mm and just pretend it's all ok, because for half the month it won't be ok.

Thanks in advance!

[FishVal]

I've been fighting with this too.
From my experience Jet SQL accepts date in american format only.
You should write code to convert you date to american format before passing it to SQL query.

Something like this.

Public Function SQLDate(varInput As Variant) As String

If IsNull(varInput) Then
SQLDate = ""
Else
SQLDate = "#" & Month(varInput) & "/" & _
Day(varInput) & "/" & Year(varInput) & "#"
End If

End Function

Good luck.

[Andrew Fairbairn]

The problem was that I didn't discover this until recently because I'd been making the database and testing it at the backend of last month, so the dates were being passed correctly.

Because it was a very late stage I instead opted to have three input boxes for day/month/year and to store them as such.

I then remade my queries so that the old Date field used the same name but consisted of [Day]+"/"+[Month]+"/"+[Year] meaning the system still functioned and I didn't have to spend ages trying to rework the entire thing.

Thanks for the help though, I'm sure it'll be useful in the future!