Report criteria not in final query

[Joan]

Hi,
I have built a query for a summary report that will present the sale and
profit statistics for salesmen. The total number of dogs sold, the total
sales price of the dogs sold, the total cost of the dogs sold, the number of
orders (customers sold to), the number of dogs left in inventory (each
salesman is assigned certain dogs to sell) and the percentage of gross
profits for each salesman is shown in this report. The final query that I
will use as the record source in built upon numerous other nested queries.

Also I have made a report criteria dialog form where the user may enter a
Start date and an End date to retrieve the dogs sold during a specific time
period based on the FinalSaleDate field. This query field is based on an
IIF expression which checks to see if the dog has been returned and then
resold or not: IIf([ReturnedSaleDate] Is
Null,[DateSold],[ReturnedSaleDate]). My problem is that neither
FinalSaleDate, [Date Sold] or [ReturnedSaleDate] are in the final query that
is used as the record source for the report. When I run my dialog form, a
small form pops up: Enter parameter value for FinalSaleDate. How can I get
this to work where the report can show summary data based on a field that is
not in the final query??

Joan

Below is the code that I have written for the Click Event of the Show Report
button:

Private Sub cmdOpenReport_Click()
Dim stDocName As String
Dim mysql As String

stDocName = "Salesperson"
If Len(Me.Start) > 0 Then
'If Not IsNull(Me.txtStartDate) Then
mysql = mysql & "(qrySalespersonDogs.[FinalSaleDate]>=#" & Me.Start &
"#) and "
End If
If Len(Me.End) > 0 Then
'If Not IsNull(Me.txtEndDate) Then
mysql = mysql & "(qrySalespersonDogs.[FinalSaleDate]<=#" & Me.End &
"#)"
End If

DoCmd.OpenReport stDocName, acViewPreview, , mysql
Me!Start = ""
Me!End = ""
DoCmd.Close acForm, Me.Name
End Sub

 

[Steve Schapel]

Joan,

The abbreviated answer is.... You either need to find a way to include
att the fields you need into the report's recordsource, or you need to
use a domain function such as DLookup() to find the values you want to
reference.

qrySalespersonDogs.[FinalSaleDate] doesn't really make sense to
Access, but even if it did, it can't be applied in any case because
there is no way for Access to know which record in the query you want
it to look at.

By the way, a couple of other random responses to your post, unrelated
to your question but hopefully helpful anyway...
mysql = mysql & "(qry ... doesn't really make sense, because there is
no value assigned to mysql already, so you're concatenating somthing
to nothing. It will still work, but unnecessary.
IIf([ReturnedSaleDate] Is Null,[DateSold],[ReturnedSaleDate]) could
possibly be neater and more efficient to use
Nz([ReturnedSaleDate],[DateSold])

- Steve Schapel, Microsoft Access MVP

Hi,
I have built a query for a summary report that will present the sale and
profit statistics for salesmen. The total number of dogs sold, the total
sales price of the dogs sold, the total cost of the dogs sold, the number of
orders (customers sold to), the number of dogs left in inventory (each
salesman is assigned certain dogs to sell) and the percentage of gross
profits for each salesman is shown in this report. The final query that I
will use as the record source in built upon numerous other nested queries.

Also I have made a report criteria dialog form where the user may enter a
Start date and an End date to retrieve the dogs sold during a specific time
period based on the FinalSaleDate field. This query field is based on an
IIF expression which checks to see if the dog has been returned and then
resold or not: IIf([ReturnedSaleDate] Is
Null,[DateSold],[ReturnedSaleDate]). My problem is that neither
FinalSaleDate, [Date Sold] or [ReturnedSaleDate] are in the final query that
is used as the record source for the report. When I run my dialog form, a
small form pops up: Enter parameter value for FinalSaleDate. How can I get
this to work where the report can show summary data based on a field that is
not in the final query??

Joan

Below is the code that I have written for the Click Event of the Show Report
button:

Private Sub cmdOpenReport_Click()
Dim stDocName As String
Dim mysql As String

stDocName = "Salesperson"
If Len(Me.Start) > 0 Then
'If Not IsNull(Me.txtStartDate) Then
mysql = mysql & "(qrySalespersonDogs.[FinalSaleDate]>=#" & Me.Start &
"#) and "
End If
If Len(Me.End) > 0 Then
'If Not IsNull(Me.txtEndDate) Then
mysql = mysql & "(qrySalespersonDogs.[FinalSaleDate]<=#" & Me.End &
"#)"
End If

DoCmd.OpenReport stDocName, acViewPreview, , mysql
Me!Start = ""
Me!End = ""
DoCmd.Close acForm, Me.Name
End Sub