Hello,

I have two pc's, both with Windows XP SP2 and Office 2003 SP2 (dutch) and the same input locales. I made a program in Access (connected to a MS SQL Server) where dates are stored. Now I want to built a report with a daterange. Because of the dateformat in SQL I made a function to convert the date because they enter it in Dutch format. The function is:

Function DatumSQL(datDatum As Date) As String
DatumSQL = "#" & Format(datDatum, "mm") & "-" & Format(datDatum, "dd") & "-" & Format(datDatum, "yyyy") & "#"
End Function

This works great on computer 1. But not on computer 2. On the second computer the date is converted again. When I don't use the function it works fine on computer 2 but not anymore on computer 1.
I searched for differences and found that on computer 1 the files in the map C:\Program Files\Common files\System\ado are from 3-8-2004 and on computer 2 they are from 2-3-2006.

How can this happen? And what is the solution?

I hope someone can help me.

Thanks in advance.

Benno

Always tricky dates are. Store them as long integers. 73050 = 31-12-2099. Never fails.

Hello,

I don't think that's an option.... I use a lot of datefields in various programs and didn't have this problem over years. Until now....

I installed on both computers MS SQL Server Enterprise Manager and used the query analyzer for the following result of the sql statement:

"SELECT [tblOffertes].*, Klant00.Bedrijf, Klant00.Aard
FROM [tblOffertes] LEFT JOIN KLANT00 ON [tblOffertes].KLANTNR = KLANT00.KLANTNR
WHERE ((([tblOffertes].OPDRACHT) Between '1/15/2007' And '12/31/2007')
AND ((KLANT00.AARD) like '%') AND (([tblOffertes].Onderdeel) Like '%')
AND (([tblOffertes].AKTIE) Like '%'))"

Ofcourse it works on 1 computer but the second one says:
'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.'

This is with the same query analyzer on the same sql database!

Hoi Bennov,
Perhaps you have to convert the dates between quotes to datetypes before you compare them to OPDRACHT.
SELECT [tblOffertes].*, Klant00.Bedrijf, Klant00.Aard
FROM [tblOffertes] LEFT JOIN KLANT00 ON [tblOffertes].KLANTNR = KLANT00.KLANTNR
WHERE ((([tblOffertes].OPDRACHT) Between '1/15/2007' And '12/31/2007')
AND ((KLANT00.AARD) like '%') AND (([tblOffertes].Onderdeel) Like '%')
AND (([tblOffertes].AKTIE) Like '%'))

A little rusty i am with SQL Server syntax. You could use CAST or CONVERT for this task i believe.

Enjoy!

Thanks for the replies but I didn't get it to work. I now upgraded from SQL Server 2000 to 2005 en it works fine on all computers!