Date Format Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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.
 
Back
Top