SELECT DISTINCTROW [LastName] & ", " & [FirstName] AS [Employee Name],
Sum([Sales by Employee Subquery].[Total Sales]) AS [Total Sales],
Sum(CLng([Sales by Employee Subquery].[Total Sales]*[Sales by Employee
Subquery].[SalesTaxRate]*100)/100) AS [Sales Tax], Sum([Sales by Employee
Subquery].[FreightCharge]) AS [Total Freight], Sum([Sales by Employee
Subquery].[Total Units]) AS [Total Units] FROM Employees INNER JOIN [Sales by
Employee Subquery] ON [Employees].[EmployeeID]=[Sales by Employee
Subquery].[EmployeeID] WHERE ((([Sales by Employee
Subquery].[OrderDate])>=[forms]![Report Date Range]![Beginning Order Date]
And ([Sales by Employee Subquery].[OrderDate])<=[forms]![Report Date
Range]![Ending Order Date])) GROUP BY [LastName] & ", " & [FirstName];
This one works the way it's supposed to. now i will give you an example of
one that doesn't work the way it's supposed to and no matter what date i
enter it reports everything.
SELECT [Customers].[CompanyName], [Customers].[CustomerID], [Order
Details].[ProductID], [Order Details].[Quantity], [Order Details].[UnitPrice]
FROM (Customers INNER JOIN Orders ON
[Customers].[CustomerID]=[Orders].[CustomerID]) INNER JOIN [Order Details] ON
[Orders].[OrderID]=[Order Details].[OrderID];
Ofer said:
Check the Record source of the two reports, to see if in the first report
there is a reference to the fields in the report
Can you post the SQL of the record source of the report
JeremyH1982 said:
In the One Report i have
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Report Date Range", , , , , acDialog, "Sales By Product"
If Not IsLoaded("Report Date Range") Then
Cancel = True
End If
End Sub
Then for the Report Date Range i have
Private Sub Form_Open(Cancel As Integer)
Me.Caption = Me.OpenArgs
End Sub
Private Sub Preview_Click()
If IsNull([Beginning Order Date]) Or IsNull([Ending Order Date]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "Beginning Order Date"
Else
If [Beginning Order Date] > [Ending Order Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Order Date"
Else
Me.Visible = False
End If
End If
End Sub
No matter what date i put in, everything comes up. On another report though
i put in a specific date and just whatever is in that date range comes up. I
don't see what i am doing wrong.
The report that reports correctly is written as:
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Report Date Range", , , , , acDialog, "Sales by Employee"
If Not IsLoaded("Report Date Range") Then
Cancel = True
End If
End Sub
And the Report Date Range is the same as above:
Private Sub Form_Open(Cancel As Integer)
Me.Caption = Me.OpenArgs
End Sub
Private Sub Preview_Click()
If IsNull([Beginning Order Date]) Or IsNull([Ending Order Date]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "Beginning Order Date"
Else
If [Beginning Order Date] > [Ending Order Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Order Date"
Else
Me.Visible = False
End If
End If
End Sub
I don't see a difference between the two, but one reports correctly and the
top one doesn't. I do appreciate the help. Thank You
