RacerGirl117
Technical User
Sorry for the long post, but I have a complicated (to me) problem here.
I have two reports that are similar in that they share the same data, but report different types of information. The reports are based on leads (prospective customers) that were turned in within a specific time period (usually within a payroll period).
1) One report gets printed and given to the employee with their paycheck so they know what leads they are being paid for and how much. The fields on this one are as follows:
Header Section
Name - Displays the employee's name.
Date - Displays the current date. It is and unbound field.
Detail Section
Proposal ID - Autonumber field
Customer Name - Self-explanatory
Lead Fee - Calculated field as follows:
LeadFeeAmt: CDbl(IIf([Lead Fee Paid] Is Not Null,"5",0))
Sold Fee - Calculated field (in the query) as follows:
SoldFeeAmt: CDbl(IIf([Comm Paid] Is Not Null,"25",0))
Name Footer Section
Total Fees - Calculated (in the query) field as follows:
TotalFees: CDbl((IIf([Lead Fee Paid] Is Not Null,"5",0)))+CDbl((IIf([Comm Paid] Is Not Null,"25",0)))
I also have the following field in the query:
FeesPaid: (IIf([Lead Fee Paid] Is Null,[Comm Paid],[Lead Fee Paid]))
and the parameters:
Is Not Null And Between [Forms]![Date Dialog]![Begin Date] And [Forms]![Date Dialog]![End Date]
That is what tells the report to pull what records. The problem with this is that if a date in the Lead Fee Paid field falls within the current pay period the report is incorrect.
For example - Our current pay period is 12/30/02 through 1/12/03. I have one person who turned in a lead where the Lead Fee was paid 1/3/03 (which is the date of our last paycheck) and the Sold Fee is marked as being paid 1/17/03 (which is the date of our upcoming paycheck).
I don't want that Lead Fee to show up. What is an efficient way of eliminating something like that from showing up in the report and being calculated in the total?
2) The other report is used strictly to report/count the number or leads entered and leads sold. The fields on this one are as follows:
Page Header
An unbound field that pulls the dates from the Date Dialog box and displays them.
Detail Section
CountofDate - A count of the number of leads entered between the specified time period.
CountofDateSold - A count of the number of leads turned into sales between the specified time period.
Report Footer Section
Two total fields summing the numbers of the above two fields.
In the query that runs this one, I have:
Is Not Null And Between [Forms]![Date Dialog]![Begin Date] And [Forms]![Date Dialog]![End Date]
in both the Date (which is the date the lead was entered) and the Date Sold fields.
The problem with this, for example, is that for this pay period's report I get 6 records. All 6 records have a date in the Date field. 4 of the 6 records have a date in the Date and Date Sold fields. Out of the 6 dates in the Date field, only 3 of them really match the parameters of "Between 12/30/02 and 1/12/03". All 4 of the 6 dates in the Date Sold field are good.
The query is counting all 6 of the "Dates" because 3 of them also have dates in the Date Sold field. What is the best way to stop it from counting those dates and reporting them?
I've probably made this more complicated than it needs to be, which isn't unusual for me. That's why I come here. Because I know that SOMEONE out there will be able to make sense of my madness.
Thanks in advance, Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA
I have two reports that are similar in that they share the same data, but report different types of information. The reports are based on leads (prospective customers) that were turned in within a specific time period (usually within a payroll period).
1) One report gets printed and given to the employee with their paycheck so they know what leads they are being paid for and how much. The fields on this one are as follows:
Header Section
Name - Displays the employee's name.
Date - Displays the current date. It is and unbound field.
Detail Section
Proposal ID - Autonumber field
Customer Name - Self-explanatory
Lead Fee - Calculated field as follows:
LeadFeeAmt: CDbl(IIf([Lead Fee Paid] Is Not Null,"5",0))
Sold Fee - Calculated field (in the query) as follows:
SoldFeeAmt: CDbl(IIf([Comm Paid] Is Not Null,"25",0))
Name Footer Section
Total Fees - Calculated (in the query) field as follows:
TotalFees: CDbl((IIf([Lead Fee Paid] Is Not Null,"5",0)))+CDbl((IIf([Comm Paid] Is Not Null,"25",0)))
I also have the following field in the query:
FeesPaid: (IIf([Lead Fee Paid] Is Null,[Comm Paid],[Lead Fee Paid]))
and the parameters:
Is Not Null And Between [Forms]![Date Dialog]![Begin Date] And [Forms]![Date Dialog]![End Date]
That is what tells the report to pull what records. The problem with this is that if a date in the Lead Fee Paid field falls within the current pay period the report is incorrect.
For example - Our current pay period is 12/30/02 through 1/12/03. I have one person who turned in a lead where the Lead Fee was paid 1/3/03 (which is the date of our last paycheck) and the Sold Fee is marked as being paid 1/17/03 (which is the date of our upcoming paycheck).
I don't want that Lead Fee to show up. What is an efficient way of eliminating something like that from showing up in the report and being calculated in the total?
2) The other report is used strictly to report/count the number or leads entered and leads sold. The fields on this one are as follows:
Page Header
An unbound field that pulls the dates from the Date Dialog box and displays them.
Detail Section
CountofDate - A count of the number of leads entered between the specified time period.
CountofDateSold - A count of the number of leads turned into sales between the specified time period.
Report Footer Section
Two total fields summing the numbers of the above two fields.
In the query that runs this one, I have:
Is Not Null And Between [Forms]![Date Dialog]![Begin Date] And [Forms]![Date Dialog]![End Date]
in both the Date (which is the date the lead was entered) and the Date Sold fields.
The problem with this, for example, is that for this pay period's report I get 6 records. All 6 records have a date in the Date field. 4 of the 6 records have a date in the Date and Date Sold fields. Out of the 6 dates in the Date field, only 3 of them really match the parameters of "Between 12/30/02 and 1/12/03". All 4 of the 6 dates in the Date Sold field are good.
The query is counting all 6 of the "Dates" because 3 of them also have dates in the Date Sold field. What is the best way to stop it from counting those dates and reporting them?
I've probably made this more complicated than it needs to be, which isn't unusual for me. That's why I come here. Because I know that SOMEONE out there will be able to make sense of my madness.
Thanks in advance, Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA
