Date format problem in ACCESS VBA report filter

[Palatini]

Hi guys,
i am a medical staff in Australia. Please allow me to ask some silly
questions here.

In order to filter the report in a time frame, I built a form has two
textboxes and a button.
The filter string is like: [Date] between #01/01/2007# AND
#31/01/2007#
It works. the report only includes the 1st month of 2007. the date is
recognised as British style/format.
BUT, when the filter becomes #01/01/2007# AND #12/01/2007#
the reports includes 11 months in 2007 rather the 1st 12 days in 2007.
Obviously, the date 12/01/2007 is recognised as American stryle/
format.

Also, i built a query, but the code [Date] between#01/01/2007# AND
#12/01/2007#, the query only gives me the 1st 12 days in 2007. it
means query works properly.

here is my code. (I am not a professional programmer, the code may be
not that good. )

Private Sub btn_rpt_bymonth_Click()
On Error GoTo Err_btn_rpt_bymonth_Click
If IsNull(StartDate) Or IsNull(EndDate) Then
MsgBox "Please enter the start date and end date!"
Me.StartDate.SetFocus
Else
If EndDate >= StartDate Then
Dim stDocName As String
Dim filtersting As String
Dim finalfilter As String
Dim wardf As String
stDocName = "Rpt_Template_VRE_byMonth"
DoCmd.OpenReport stDocName, acPreview
filterstring = "Date between #" & StartDate & "# AND #" &
EndDate & "#"
wardf = Wardfilter
If wardf = "" Then
finalfilter = filterstring
Else
finalfilter = "(" & filterstring & ") AND (" & wardf &
")"
End If
Reports!Rpt_template_VRE_byMonth.Filter = finalfilter
Reports!Rpt_template_VRE_byMonth.FilterOn = True
Else
MsgBox "ERROR: Start Date > END Date"
Me.StartDate.SetFocus
End If
End If
Exit_btn_rpt_bymonth_Click:
Exit Sub

Please help. Thanks

 

[Steve Schapel]

Palatini,

First of all, a couple of comments not directly related to your question.

'Date' is a Reserved Word (i.e. has a special meaning) in Access, and as
such should not be used as the name of a field or control. I recommend
changing it if you can. Otherwise, you should always enclose it in []s
in your code etc.

Secondly, make sure that the textboxes where you are entering the date
range criteria have their Format property set to a valid date format -
doesn't matter which.

Your code is a bit more complicated than you need. You can apply the
criteria via the Where Condition argument of the OpenReport method,
without the need for applying a Filter.

Dates are by default assumed by VBA to be American format. I myself
find it easiest to handle this by taking advantage of the fact that
Access stores dates as a number (Long Integer).

So here's one way to do your code, something like this:

Private Sub btn_rpt_bymonth_Click()
If IsNull(Me.StartDate) Or IsNull(Me.EndDate) Then
MsgBox "Please enter the start date and end date!"
Me.StartDate.SetFocus
Else
If Me.EndDate >= Me.StartDate Then
Dim filtersting As String
filterstring = "[YourDate] Between " & CLng(Me.StartDate) & "
And " & CLng(Me.EndDate)
If Wardfilter <> "" Then
filterstring = "(" & filterstring & ") AND (" & Wardfilter
& ")"
End If
DoCmd.OpenReport "Rpt_Template_VRE_byMonth", acPreview, ,
filterstring
Else
MsgBox "ERROR: Start Date > END Date"
Me.StartDate.SetFocus
End If
End If
Exit Sub


--
Steve Schapel, Microsoft Access MVP

Hi guys,
i am a medical staff in Australia. Please allow me to ask some silly
questions here.

In order to filter the report in a time frame, I built a form has two
textboxes and a button.
The filter string is like: [Date] between #01/01/2007# AND
#31/01/2007#
It works. the report only includes the 1st month of 2007. the date is
recognised as British style/format.
BUT, when the filter becomes #01/01/2007# AND #12/01/2007#
the reports includes 11 months in 2007 rather the 1st 12 days in 2007.
Obviously, the date 12/01/2007 is recognised as American stryle/
format.

Also, i built a query, but the code [Date] between#01/01/2007# AND
#12/01/2007#, the query only gives me the 1st 12 days in 2007. it
means query works properly.

here is my code. (I am not a professional programmer, the code may be
not that good. )

Private Sub btn_rpt_bymonth_Click()
On Error GoTo Err_btn_rpt_bymonth_Click
If IsNull(StartDate) Or IsNull(EndDate) Then
MsgBox "Please enter the start date and end date!"
Me.StartDate.SetFocus
Else
If EndDate >= StartDate Then
Dim stDocName As String
Dim filtersting As String
Dim finalfilter As String
Dim wardf As String
stDocName = "Rpt_Template_VRE_byMonth"
DoCmd.OpenReport stDocName, acPreview
filterstring = "Date between #" & StartDate & "# AND #" &
EndDate & "#"
wardf = Wardfilter
If wardf = "" Then
finalfilter = filterstring
Else
finalfilter = "(" & filterstring & ") AND (" & wardf &
")"
End If
Reports!Rpt_template_VRE_byMonth.Filter = finalfilter
Reports!Rpt_template_VRE_byMonth.FilterOn = True
Else
MsgBox "ERROR: Start Date > END Date"
Me.StartDate.SetFocus
End If
End If
Exit_btn_rpt_bymonth_Click:
Exit Sub

Please help. Thanks

 

[Allen Browne]

You need to use the American date format in SQL statements. This applies to
things like Filter strings and criteria of DLookup(), as they are ultimately
handled as the WHERE clause of a SQL statement too.

See:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html
It's based on 15 years experience using all versions of Access in Australia.

Here's another simple example:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

And here's a more comprehensive one where the filter string includes other
fields as well as the date range:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[Palatini]

Palatini,

First of all, a couple of comments not directly related to your question.

'Date' is a Reserved Word (i.e. has a special meaning) in Access, and as
such should not be used as the name of a field or control.  I recommend
changing it if you can.  Otherwise, you should always enclose it in []s
in your code etc.

Secondly, make sure that the textboxes where you are entering the date
range criteria have their Format property set to a valid date format -
doesn't matter which.

Your code is a bit more complicated than you need.  You can apply the
criteria via the Where Condition argument of the OpenReport method,
without the need for applying a Filter.

Dates are by default assumed by VBA to be American format.  I myself
find it easiest to handle this by taking advantage of the fact that
Access stores dates as a number (Long Integer).

So here's one way to do your code, something like this:

Private Sub btn_rpt_bymonth_Click()
  If IsNull(Me.StartDate) Or IsNull(Me.EndDate) Then
     MsgBox "Please enter the start date and end date!"
     Me.StartDate.SetFocus
  Else
     If Me.EndDate >= Me.StartDate Then
         Dim filtersting As String
         filterstring = "[YourDate] Between " & CLng(Me.StartDate) & "
And " & CLng(Me.EndDate)
         If Wardfilter <> "" Then
             filterstring = "(" & filterstring & ") AND ("& Wardfilter
& ")"
         End If
         DoCmd.OpenReport "Rpt_Template_VRE_byMonth", acPreview,,
filterstring
     Else
         MsgBox "ERROR: Start Date > END Date"
         Me.StartDate.SetFocus
     End If
  End If
Exit Sub

--
Steve Schapel, Microsoft Access MVP

Hi guys,
i am a medical staff in Australia. Please allow me to ask some silly
questions here.

In order to filter the report in a time frame, I built a form has two
textboxes and a button.
The filter string is like: [Date] between #01/01/2007# AND
#31/01/2007#
It works. the report only includes the 1st month of 2007. the date is
recognised as British style/format.
BUT, when the filter becomes #01/01/2007# AND #12/01/2007#
the reports includes 11 months in 2007 rather the 1st 12 days in 2007.
Obviously, the date 12/01/2007 is recognised as American stryle/
format.

Also, i built a query, but the code [Date] between#01/01/2007# AND
#12/01/2007#, the query only gives me the 1st 12 days in 2007. it
means query works properly.

here is my code. (I am not a professional programmer, the code may be
not that good. )

Private Sub btn_rpt_bymonth_Click()
On Error GoTo Err_btn_rpt_bymonth_Click
If IsNull(StartDate) Or IsNull(EndDate) Then
    MsgBox "Please enter the start date and end date!"
    Me.StartDate.SetFocus
Else
    If EndDate >= StartDate Then
        Dim stDocName As String
        Dim filtersting As String
        Dim finalfilter As String
        Dim wardf As String
        stDocName = "Rpt_Template_VRE_byMonth"
        DoCmd.OpenReport stDocName, acPreview
        filterstring = "Date between #" & StartDate & "# AND #" &
EndDate & "#"
        wardf = Wardfilter
            If wardf = "" Then
                finalfilter = filterstring
            Else
                finalfilter = "(" & filterstring & ") AND (" & wardf &
")"
            End If
        Reports!Rpt_template_VRE_byMonth.Filter = finalfilter
        Reports!Rpt_template_VRE_byMonth.FilterOn = True
    Else
        MsgBox "ERROR: Start Date > END Date"
        Me.StartDate.SetFocus
    End If
End If
Exit_btn_rpt_bymonth_Click:
        Exit Sub

Please help. Thanks- Hide quoted text -

- Show quoted text -

Thanks for help, i gonna test it tomorrow.

 

[Palatini]

You need to use the American date format in SQL statements. This applies to
things like Filter strings and criteria of DLookup(), as they are ultimately
handled as the WHERE clause of a SQL statement too.

See:
    International Date Formats in Access
at:
   http://allenbrowne.com/ser-36.html
It's based on 15 years experience using all versions of Access in Australia.

Here's another simple example:
    Limiting a Report to a Date Range
at:
   http://allenbrowne.com/casu-08.html

And here's a more comprehensive one where the filter string includes other
fields as well as the date range:
    Search form - Handle many optional criteria
at:
   http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi guys,
i am a medical staff in Australia. Please allow me to ask some silly
questions here.

In order to filter the report in a time frame, I built a form has two
textboxes and a button.
The filter string is like: [Date] between #01/01/2007# AND
#31/01/2007#
It works. the report only includes the 1st month of 2007. the date is
recognised as British style/format.
BUT, when the filter becomes #01/01/2007# AND #12/01/2007#
the reports includes 11 months in 2007 rather the 1st 12 days in 2007.
Obviously, the date 12/01/2007 is recognised as American stryle/
format.

Also, i built a query, but the code [Date] between#01/01/2007# AND
#12/01/2007#, the query only gives me the 1st 12 days in 2007. it
means query works properly.

here is my code. (I am not a professional programmer, the code may be
not that good. )

Private Sub btn_rpt_bymonth_Click()
On Error GoTo Err_btn_rpt_bymonth_Click
If IsNull(StartDate) Or IsNull(EndDate) Then
   MsgBox "Please enter the start date and end date!"
   Me.StartDate.SetFocus
Else
   If EndDate >= StartDate Then
       Dim stDocName As String
       Dim filtersting As String
       Dim finalfilter As String
       Dim wardf As String
       stDocName = "Rpt_Template_VRE_byMonth"
       DoCmd.OpenReport stDocName, acPreview
       filterstring = "Date between #" & StartDate & "# AND #"&
EndDate & "#"
       wardf = Wardfilter
           If wardf = "" Then
               finalfilter = filterstring
           Else
               finalfilter = "(" & filterstring & ") AND (" & wardf &
")"
           End If
       Reports!Rpt_template_VRE_byMonth.Filter = finalfilter
       Reports!Rpt_template_VRE_byMonth.FilterOn = True
   Else
       MsgBox "ERROR: Start Date > END Date"
       Me.StartDate.SetFocus
   End If
End If
Exit_btn_rpt_bymonth_Click:
       Exit Sub

Please help. Thanks- Hide quoted text -

- Show quoted text -

The reference does help!