High everyone,

I am in Australia so my concern is with American/Australia date
formats.

I have a report based on a query. That query takes two paramters -
you know the usual - fromDate and ToDate. The report has been running
fine for months but now I design a form where the user inputs the
dates into two unbound fields and hits a run button - this makes it
more user friendly than the default access paramter input dialogues.

Anyways, I take the query and turn it into a filter using the
docmd.openform,frmname,criteria
method

the report runs but returns all sorts of dates so I am wondering what
is going on.
So I try writing an sql statement and switching the recordsource of
the report to the sql statement. Same result.

Turns out that somewhere along the way the dates are being converted
into American format, so for example, I enter 1/07/2003 to 10/07/2003
and I am getting everything from the 7/1/2003 to 7/10/2003.

All my dates are set up correctly and if I run the report and give it
a recordsource using a stored query, I don't get this problem. So my
final solution is to take the date and convert it to American format
myself using a very convoluted method - sorry if I am showing my
inexperience but I couldn't find a better way.

FromDate = Month(FromDate) & "/" & Day(FromDate) & "/" &
year(FromDate)
ToDate = Month(ToDate) & "/" & Day(ToDate) & "/" & year(ToDate)

My beef is that I shouldn't have to do this. Its only in the code
that the problem exists. The same sql as a recordsource works if the
sql is a stored query.

Has anyone had a similar problem?

Did any of this make sense?



For those who want to see the code here it is:


-----------------------------------------------------------------------------
Function JetWeeklyRep()
'opens JetWeeklyRep with filter applied

On Error GoTo JetWeeklyRep_err
Application.Echo False

Dim frm As Form, rpt As Report, repName As String
Dim FromDate, ToDate
Dim dbs As Object

' close the form if it isn't already
Set dbs = Application.CurrentProject
If dbs.AllReports("rptJetWeekly").IsLoaded = True Then
DoCmd.Close acReport, "rptJetWeekly", acSaveNo
End If

Set frm = Forms!frmDateRange
FromDate = CDate(frm!FromDate) 'making sure the dates are real
dates
ToDate = CDate(frm!ToDate)
'--------convert to american format here
----------------------------------
FromDate = Month(FromDate) & "/" & Day(FromDate) & "/" &
year(FromDate)
ToDate = Month(ToDate) & "/" & Day(ToDate) & "/" & year(ToDate)
'----------------------------------------------------------------------------
DoCmd.OpenReport "rptJetWeekly", acViewDesign
Set rpt = Reports!rptJetWeekly
repName = rpt.NAME

sql = "SELECT Jet.*,services.ServName " & _
"FROM services INNER JOIN Jet ON services.ServID = Jet.ServID " &
_
" WHERE (((Jet.CCRWSent)>=#" & FromDate & "# And
(Jet.CCRWSent)<=#" & ToDate & "#) AND ((services.Region)='" &
frm!cmbRegion & "'));"
rpt.RecordSource = sql
DoCmd.OpenReport repName, acViewPreview
DoCmd.RunCommand acCmdSave
Application.Echo True

Exit Function

JetWeeklyRep_err:
Application.Echo True
If Err.Number = 3075 Then

MsgBox "Please enter a value in each box before trying again!",
vbCritical, msgboxTitle
End If

End Function
----------------------------------------------------------------------------

Michael, take a look at:
International Dates in Access
at:
http://allenbrowne.com/ser-36.html

The article addresses the three areas where Access tends to misunderstand
our dates here in Australia.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to the newsgroup. (Email address has spurious "_SpamTrap")

"Michael" <westmj (AT) hotmail (DOT) com> wrote

-

[Boy, are you lucky Allen Browne just happened to have a whole Web page that
answered your question!]


"Michael" <westmj (AT) hotmail (DOT) com> wrote

-

Thanks for that - that explains everything