Date Formatting Wrong In Query 2

[GummowN]

Another strange one here. My machine has UK settings for dates.
I create a parameter based on a date, I append that into a string which I then use as the string for a query. The string is correct but when the query runs it changes the date to US format. Any ideas.

Below is the code

intDwn = 6
strparam = dateadd("d",-1,date())

strSql = "SELECT dbo_tblRpt_Agreements.CustomerRef FROM ((dbo_tblRpt_Agreements " & _
"INNER JOIN dbo_tblRpt_Financials ON dbo_tblRpt_Agreements.AgreementID = " & _
"dbo_tblRpt_Financials.AgreementID) INNER JOIN dbo_tblRpt_Milestones ON " & _
"dbo_tblRpt_Agreements.AgreementID = dbo_tblRpt_Milestones.AgreementID) " & _
"INNER JOIN dbo_tblRpt_MilestoneHistory ON dbo_tblRpt_Agreements.AgreementID = " & _
"dbo_tblRpt_MilestoneHistory.AgreementID WHERE ((dbo_tblRpt_Milestones.PaymentsDown)>= " & intDwn & &quot " &_
"AND ((dbo_tblRpt_MilestoneHistory.AsOfDate)= #" & strParam & "#) AND " & _
&quot;((dbo_tblRpt_MilestoneHistory.PaymentsDown)< &quot; & intDwn & &quot);&quot;

Set qdf = dbs.QueryDefs(&quot;qsel_col_00016_VariableDown_NG_00016&quot
qdf.SQL = strSql

 

[KenReay]

Hi

This is as it should be, dates in SQL strings must always be US format, no matter what the local settings Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[GummowN]

So why can I create a simple query for pulling back records on a certain date when I input the date in UK format?
And when saved stores the date in UK format?

 

[KenReay]

Hi

I assume you are using the query grid to do this?

This 'corrects' the date format, so it is not apparent that the us form is being used, but I assure you it is the US date format which is used in SQL strings, trying making an SQL string in code and executing it, with for example a db.execute or docmd.runsql and you will see for yourself Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[LittleSmudge]

For those of us in the 'logical' world that display dates in the progressive order smallest, medium, largest ( dd/mm/yy ) this problem is a real bind.

However, the solution I now always use is

&quot;SELECT .. .. etc. .. .. WHERE DateField < #&quot; & Format( DateControlName, &quot;Medium Date&quot; ) & &quot;#&quot;


DateControlName can be a variable holding a date, or a control with a date value in it. These will display in the format according to the international setting on the local machine.

Wrapping this in the Format (xxx,&quot;Medium Date&quot construct FORCES the date into an unambiguous dd/mmmm/yy format which JET will always interpret correctly.



'ope-that-'elps.


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[GummowN]

That indeed helped. Thanks

I would give you a STAR if I knew how to.

This problem should be filed under B - for George Bush Syndrome - nothing exists outside the USofA

 

[johnwm]

Gummown
Look at your last post. Immediately below the word 'problem' in the last line there is a link 'Mark this post as a helpful/expert post!'

Click to give LittleSmudge his star!
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'