Where Condition in open report is not working

[cadocs]

I have read multiple other posts regarding the where condition not working properly on the open report action and haven't found a solution that will work for me. I have a simple report that pulls information regarding loan signings from my database and outputs the info separated by sales rep in the group header. I would like to be able to view multiple date ranges of my data and only have one main report and send different code to it vs. having multiple copies of the same report each with different SQL criteria. This way if i make a change to my report, i only have to change it once, and not 3 or 4 times.

The code I have behind the "on Click" event for a button on my switchboard to call the report is as follows:

    Dim stDocName As String

    stDocName = "rpttoday"
    
    FilterSignings = "[date] = #" & date & "#"
    DoCmd.OpenReport stDocName, acPreview, , FilterSignings

This code will pull today's loan signings and separate by sales rep. If I want it to pull tomorrow's info, i will just add another button and change the FilterSignings to below instead of creating a new report.

    FilterSignings = "[date] = #" & (date + 1) & "#"

What is happening is the report seems to be using the Where condition in the openreport action as a prefilter, and then appling the report source SQL (in which there is no "criteria" selected). So for today for example, Andrea is the only rep with signings, so the report filters out everyone else except for andrea, however the report then shows all the data for Andrea, instead of just today's info.

I tried putting the filter directly into the open event of the form and tested it with the msgbox to test that it is working and the same thing is happening.

    Private Sub Report_Open(Cancel As Integer)

    Reports!rptToday.Filter = FilterSignings
    MsgBox (Reports!rptToday.Filter)

    End Sub

I also have a textbox in the report with the record source being "=[filter]" as other threads have suggested to verfiy the filter is working, but the same thing is happening.

I know that If I filter the record source SQL of the report by adding the date and putting the =Date() as the criteria, it will work. The problem with this is that I will have to create a separate report for tomorrow's info with the criteria changed, which is what I don't want.

I know that I could put [Enter date] in the date criteria so that the user could put the date in, but I want the report to pop up automatically.

Any Ideas...

Thanks a Million,

Geoff

 

[dhookom]

What happens if you use:

    Dim stDocName As String
    Dim FilterSignings as String
    stDocName = "rpttoday"
    
    FilterSignings = "[date] = Date()"
    DoCmd.OpenReport stDocName, acPreview, , FilterSignings

You may have an issue with a field being named the same as a function. I would open the design view of the report's record source and alias the [Date] field:
SalesDate: [Date]
or whatever.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cadocs]

Hey Duane,

I did as you said and changed the date field name to signing_date and I also made the other filtersignings change removing the #'s, but the same thing is happening.

I think it has something to do with the fact that I am totaling the values up in the group header by sales rep, but then again, i copied the summing info into the detail section and the same thing happened.

Just to clarify, I am using dlookup calculations in my section header to calculate the total number of signing per sales rep and that is giving me the problems.

What is working is that the detail section IS filtering by the filter criteria, so i know it is working.

Any other ideas?

Geoff

 

[cadocs]

I think i just figured it out. I am using the dlookup function to get the values instead of using the count function. I tried it and it seems to be working.

Any other possible functions?

Geoff

 

[dhookom]

We don't know anything about your report record source, grouping, or desired calculations.

Generally, I avoid all DLookup(), DCount(), DSum(), etc in reports. They are one of the most mis-used methods I have ever seen in reports.

If you provided some information with a specific question, we might be able to provide more assistance.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cadocs]

Duane,

I used sum and count functions similar to the one's in the cheesy northwinds sample database and thos worked great. Thanks for your willingness to help.

Geoff