Results 1 to 2 of 2
2006-03-01, 09:43 #1
- Join Date
- Sep 2002
- London, England
- Thanked 0 Times in 0 Posts
Randomly switching between US and UK dates (2003)
I am having difficulties trying to report on data held in a SQL Server database using Access. It seems that Access is randomly switching between treating the dates in the SQL data as if they were in UK format (dd/mm/yyyy) or in US format (mm/dd/yyyy). I've seen a similar problem before, in Excel, where UK data is viewed on a machine set up for US dates, and Excel treats all the dates as US dates except those that don't fit (31/12/2004 for example) when it switches to teating the date as a UK date (I think it was that way round - it was a while ago). However, I don't think I've seen this before in Access. Also, the dates that it seems to be having problems with are not those that would give non existing months in either system. What I am seeing is 11/10/2004 treated as 11 Oct, but then, on the next line, 12/10/2004 treated as 10 Dec.
I am showing, in a form, daily sales transactions totals, one line for each day, then using a button to drill down to the transaction details for the selected day. Every now and again, the transaction details on the drill down form don't total to the amount shown on the total form. So far, the total on the details form is correct, but, if there's a problem, it's the figure on the totals form that is wrong, because it's showing the total for the wrong day (10 Dec instead of 12 Oct). All the machines involved in data capture, storage and manipulation are set up to use UK dates.
I know that SQL Server queries expect to see their dates in US format, but I'm writing my queries in Access (against linked tables from the SQL Server and another Access database) . I also don't use dates directly in my queries, I just group on the date field to get the daily totals. The other place that dates are used is in an Access lookup table that is used to determine which financial period the dates fall into.
Has anybody else seen this, or anything similar.
2006-03-01, 10:07 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: Randomly switching between US and UK dates (2003)
Since we also use dd/mm/yyy format in The Netherlands, I am familiar with the problems that can arise from VBA and SQL's insistence on mm/dd/yyyy format. But I have never seen a discrepancy such as 11/10/2004 being interpreted as 11 October and 12/10/2004 being interpreted as 10 December.