Full Version: Sending dd/mm/yyyy dates via Access to SQL table? Possible?
UtterAccess Forums > Microsoft® Access > Access Date + Time
I have a text box on an Access Project form that requires my users to give a date in the following format:
HAs we are in the UK...
When I attempt to send this date to my SQL 2000 table via an SQL statement in VB, I get an "out-of-range" error if the day part is more than 12. It would appear the SQL 2000 server is always expecting US date format, so errors when it thinks an invalid month is being sent. Ie.. if the date given is 20/11/2005 (20th November 2005) it believes I'm trying to suggest the month is '20', which isn't possible.
Is there any way I can make this work? I don't want to have to start telling my users to use mm/dd/yyyy formatting. It's a challenge enough to get them to use a database, if I have to get them to use mm/dd/yyyy or yyyy/mm/dd I'll have a mutiny on my hands.
This also errors if I pull the current system date via the 'Date' function. Again, because this date is in the wrong format, SQL errors if the day is older than 12.
Any ideas/suggestions? I understand this may be a fairly simple issue but after hours of digging on Google, I'm no clearer sadly, and this is currently halting my development in it's tracks.
Thanks in advance!!!
Use Format to convert the date when sending it back to SQL Server.
Rightly or wrongly, I solved it with the following code:
Function DateFormat(dt)
DateFormat = Year(dt) & "/" & Left("00", 2 - Len(Month(dt))) & Month(dt) & "/" & Left("00", 2 - Len(Day(dt))) & Day(dt) & " " & FormatDateTime(dt, 4)
End Function
And in my VBA code....
myNow = DateFormat(Date)
And all I do is insert myNow which inserts the current date in UK format. I can also use this with my text boxes, so it all looks to be fine... we shall see frown.gif
Thanks for the reply.
Larry Larsen
Also check out: International Dates in Access# by Allen Browne.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.