Can't Filter a Report

[Robert T]

Hello:

I created a checkbook application that goes back several years so it has a
lot of transactions. I only want to print transactions within the last 30
days. I put the following script on the command button, however, Access keeps
printing every report.

Note: Trans_Date is a field in the child table called tblTransactions

Private Sub btnrptChecks_Click()
On Error GoTo Err_btnrptChecks_Click

Dim stDocName As String
Dim stWhereCondition As String

stDocName = "rptqryTblAccounts"
stWhereCondition = "[Trans_Date] > (Date()-30)"

DoCmd.OpenReport stDocName, acPreview, stWhereCondition

Exit_btnrptChecks_Click:
Exit Sub

Err_btnrptChecks_Click:
MsgBox Err.Description
Resume Exit_btnrptChecks_Click

End Sub

 

[Duane Hookom]

Try add another comma to the left of stWhereCondition:
DoCmd.OpenReport stDocName, acPreview, , stWhereCondition

 

[Robert T]

Thanks Duane but that didn't help. Now, instead of print previewing all of
the records in the report, an Enter Parameter dialog box pops up.

I played around with the expression just a little, and this is what it
currently looks like, but I'm still getting that Enter Parameter Dialog Box
popping up.

stDocName = "rptqryTblAccounts"
stWhereCondition = "[sfrmtblTransactions].Form![Trans_Date]> (Date() -
30)"

DoCmd.OpenReport stDocName, acPreview, , stWhereCondition

Robert

 

[Duane Hookom]

Your "Enter..." should provide a name that it is looking for. Did you get
this?

Assuming [Trans_Date] is a field in your report's record source, try:

stWhereCondition = "[Trans_Date]> #" & DateAdd("d", -30,Date) & "#"

--
Duane Hookom
MS Access MVP


You shouldn't reference a control on a subform or whatever here. You need to
reference a field name from your report's record source.

 

[Robert T]

Hello Duane:

Yes, [Trans_Date] is a control on the report. I therefore changed the
stWhereCondtion to

stWhereCondition = "[Trans_Date]> #" & DateAdd("d", -30,Date) & "#"

When I click on the cmd button I still get the Enter Paramter dialog box
popping up. The only difference is I see Trans_Date showing up in the box.

Am I missing something obvious? Can I use the above technique to always
filter the report to show only those transactions within the last 30 days?

Thanks,
Robert

 

[Duane Hookom]

I stated "Assuming [Trans_Date] is a field in your report's record source".
This is not the smae as a "control on the report". If Trans_Date was a field
in your report's record source, you would not be asked for Trans_Date.

Please check this again to see if Trans_Date is in the report's field list.

 

[Robert T]

Hello Duane:

I just chekced and you're correct. [Trans_Date] isn't a field in the
report's record source, it's a field in the child table. Until you pointed it
out, I didn't realize [Trans_Date] was NOT listed in the report's record
source. The only fields listed in the report's record source are the fields
from the Parent Table.

Is there a way to filter the report using the value of a field in the CHILD
table?

Thanks,
Robert

 

[Duane Hookom]

Yes, it is possible if you include Trans_Date in the main report's record
source query. You don't need to display it in the query but it can be used
in the Where clause/criteria of the query.

 

[Robert T]

Hello Duane:

I designed another report, ensuring the fields I need are in the Report's
list of fields and it works, displaying only transactions which were
processed in the last 30 days.

Or I can write an expression that displays transactions only from account
ID_No "001", which is a text field.

Unfortunately I can't seem to properly design a stWhereCondition which
combines both expressions using AND. Each of the following works, but I can't
seem to put them together so the report is filtered by both conditions.

stWhereCondition = "tblTransactions_ID_NO = '001'"

stWhereCondition = "[Trans_Date] >= " & Format(Date - 31, "\#mm\/dd\/yyyy\#")

Could you possibly help me combine both of the expressions above so it will
filter the report to display all transactions from Account No. ID_No "001"
within the last 30 days?

Thanks,
Robert

 

[Robert T]

Hello Duane:

I kept experimenting with single and double quotes and finally came up with
an expression that worked.

stWhereCondition = "tblTransactions_ID_NO = '001' and [Trans_Date] >= " &
Format(Date - 31, "\#mm\/dd\/yyyy\#") '"

Thanks so much for your patience and all of your help.
Robert