Date Format Problem

[Guest]

Thanking you in advance. I am a novice user so please excuse my ignorance.
I have a report based on a query. I have created a form with two text boxes
in which you select date from & date to. I have also created a command
button which calls the report wanted. My regional settings are set using
short date format dd/mm/yyyy. This seems to be causing me a problem as I get
the wrong dates etc when the report is opened. Can anyone help me to format
the date so that I can keep my regional settings as they are yet get the
right info that I need.
My code is

Private Sub CmdPrintReport_Click()
Dim ReportName
ReportName = "SettlementsRpt"
DoCmd.OpenReport _
ReportName:=ReportName, _
view:=acViewPreview, _
wherecondition:="Date Between #" & _
Me.DateFrom & "# AND #" & _
Me.DateTo & "#"

End Sub

 

[Nikos Yannacopoulos]

Kez,

This is a common problem when using non-US date formats, because VB
assumes all dates are US format. To overcome the problem, change your
code as follows:

Private Sub CmdPrintReport_Click()
Dim ReportName, dtfm, dtto
ReportName = "SettlementsRpt"
dtfm = Format(Me.DateFrom, "yyyy/mm/dd")
dtto = Format(Me.DateFrom, "yyyy/mm/dd")
DoCmd.OpenReport _
ReportName:=ReportName, _
view:=acViewPreview, _
wherecondition:="Date Between #" & _
dtfm & "# AND #" dtto & "#"
End Sub

HTH,
Nikos

 

[Guest]

Thanks you are a genius.

I did have a little trouble at first but soon fixed it - dtto =
Format(MeDateFrom etc) - appeared twice changed it to Me. Date To and it
worked. You have saved me so much time and trouble.

Thanks again.

 

[Nikos Yannacopoulos]

You're absolutely right, my mistake, good work on your part.