report filter

[O Wilson]

Hello,

I have a form call "Daily Reports" which the user fills
out daily. I also have report that needs to be printed
daily.

I have a command button that opens the report in Print
Preview but it show reports for all the days. I'd like
to get it to open the report containing only the
information for a specific date; the date diplayed in the
form. My code, as is, looks like this:

Private Sub Command195_Click()
On Error GoTo Err_Command195_Click

Dim stDocName As String
Dim strFilter As String


stDocName = "Daily Manpower and Manhour Report"
strFilter = "Date = Forms!Daily Manpower and Manhour
Report!Date"
DoCmd.OpenReport stDocName, acPreview, , strFilter

Exit_Command195_Click:
Exit Sub

Err_Command195_Click:
MsgBox Err.Description
Resume Exit_Command195_Click

End Sub

This format works with other kinds of data but I cannot
get it to work using dates. Any suggestions would be
greatly appreciated.

TIA

Owen

 

[fredg]

Hello,

I have a form call "Daily Reports" which the user fills
out daily. I also have report that needs to be printed
daily.

I have a command button that opens the report in Print
Preview but it show reports for all the days. I'd like
to get it to open the report containing only the
information for a specific date; the date diplayed in the
form. My code, as is, looks like this:

Private Sub Command195_Click()
On Error GoTo Err_Command195_Click

Dim stDocName As String
Dim strFilter As String


stDocName = "Daily Manpower and Manhour Report"
strFilter = "Date = Forms!Daily Manpower and Manhour
Report!Date"
DoCmd.OpenReport stDocName, acPreview, , strFilter

Exit_Command195_Click:
Exit Sub

Err_Command195_Click:
MsgBox Err.Description
Resume Exit_Command195_Click

End Sub

This format works with other kinds of data but I cannot
get it to work using dates. Any suggestions would be
greatly appreciated.

TIA

Owen

1) For one thing, you posted that the form name was "Daily Reports".
But you use
strFilter = "Date = Forms!Daily Manpower and Manhour Report!Date"
in the filter.
At the very least, it should read:
strFilter = "Date = Forms!DailyReport!Date"
which won't work either. :-(

2) Date is a reserved word in Access/VBA and should not be used as a
Field or Control name.

Change that field name to dteDate, or some other descriptive name, but
not Date.

3) You can use the Me! keyword to replace the forms!formName syntax,
as long as you are referring to the same form that the code is on.

Me!ControlName can replace forms!FormName!ControlName.

4) While the Where clause must be a string, you must concatenate the
variable outside the string using &.

If [FieldName] is a Number datatype:
[FieldName] = " & Me![ControlName]

If [FieldName] is a Text datatype:
[FieldName] = '" & Me![FieldName] & "'"

If [FieldName] is a date....
5) Dates must be wrapped with the date delimiter symbol "#".

6) Then change the strFilter to:
strFilter = "[dteDate] = #" & Me![dteDate] & "#"

and it should work. Remember to substitute your actual Date field name
after you rename the field.

You could make it a bit simpler and easier to follow, using:

Private Sub Command195_Click()
On Error GoTo Err_Command195_Click

DoCmd.OpenReport "Daily Manpower and Manhour Report", acViewPreview, ,
"[dteDate] = #" & Me![dteDate] & "#"

Exit_Command195_Click:
Exit Sub

Err_Command195_Click:
MsgBox Err.Description
Resume Exit_Command195_Click
End Sub