I am sure I have not looked properly for this answer.

Just upsized my access table to a SQL server (i have little control over)

dates are entered in dd/mm/yyyy (from all input controls)
dates appear in slq server tables as dd/mm/yyyy

In a sql query i have to use mm/dd/yyyy

What is different. What do I need to change. Surely I do not have to change each query or format each one.

Whats the local set to on your client machine?

What data format is sql server set to?

What language setting does your sql server login have?

All of these things will have an effect

Given the number of queries I have I am not liking what I have found out.

it seems that as soon as there is # # either side of the date then access automatically makes it a US date. I have no idea how it was working up until now.

This means that if my control has dd/mm/YYYY I am going to have to Format it first eg Format(Me.StartDate, "mm\/dd\/yyyy").

So as I am using a SQL Server back end I have heard that I should not use functions that SQL Server does not recognise, thus including this Format(Me.StartDate, "mm\/dd\/yyyy") in the actual SQL string should not be done.


Instead

strsqldate = Format(Me.StartDate, "mm\/dd\/yyyy")

and then use strsqldate instead.

Am I on the right track?

Whats the local set to on your client machine?Mine is Australian :-) but I am in Germany and the program will be run by computer in Japan, China Germany and the US.

What data format is sql server set to? How do I see this. I am not the SA so I may not be able to see it.

What language setting does your sql server login have? English I think, is there somewhere where you can choose between UK or US?

All of these things will have an effectIt must as I was test this at home with two computers and my own server on one of them, now I have linked to an existing server at work.

Given the number of queries I have I am not liking what I have found out.

it seems that as soon as there is # # either side of the date then access automatically makes it a US date. I have no idea how it was working up until now.

This means that if my control has dd/mm/YYYY I am going to have to Format it first eg Format(Me.StartDate, "mm\/dd\/yyyy").

So as I am using a SQL Server back end I have heard that I should not use functions that SQL Server does not recognise, thus including this Format(Me.StartDate, "mm\/dd\/yyyy") in the actual SQL string should not be done.


Instead

strsqldate = Format(Me.StartDate, "mm\/dd\/yyyy")

and then use strsqldate instead.

Am I on the right track?


Yeah kind of, you can also use a sql server t-sql command called CONVERT to change date formats.


declare @date datetime
set @date ='2003/10/09'
select convert(varchar(10),@date,101)


declare @date datetime
set @date ='2003/10/09'
select convert(varchar(10),@date,103)