Report error in viewing

[leafsrock100]

I have an inventory report that is based on a user inputted date range on a form. When I click preview on the form, the report opens via a macro and sometimes shows an "#Error". However, when I click design view and then print preview again, the report queries the desired results.

How can I program it so that the "#Error" does not show?

The following is the SQL statement if that helps:

SELECT DISTINCTROW Products.ProductID, Products.ProductName, [Beginning Inventory].UnitType, [Beginning Inventory].[Beginning Inventory], Purchases.Purchases, Sales.Sales, nz([Beginning Inventory],0)+nz([Purchases],0)-nz([Sales],0) AS [Ending Inventory], [Beginning Inventory].BegUnitPrice, Format(nz([Beginning Inventory]*[BegUnitPrice],0),"$0.00") AS BegTotal, Format(Sum(nz([Beginning Inventory]*[BegUnitPrice],0)+nz([Purchases]*[PurchasePrice],0))/Sum(nz([Purchases],0)+nz([Beginning Inventory],0)),"$0.00") AS [Ending Unit Price], Format(nz([Ending Unit Price]*[Ending inventory],0),"$0.00") AS [End Total]
FROM (Units INNER JOIN (((Products INNER JOIN [Inventory Transactions] ON Products.ProductID = [Inventory Transactions].ProductID) INNER JOIN [Beginning Inventory] ON Products.ProductID = [Beginning Inventory].ProductID) INNER JOIN Purchases ON Products.ProductID = Purchases.ProductID) ON Units.UnitID = [Inventory Transactions].UnitID) INNER JOIN Sales ON Products.ProductID = Sales.ProductID
GROUP BY Products.ProductID, Products.ProductName, [Beginning Inventory].UnitType, [Beginning Inventory].[Beginning Inventory], Purchases.Purchases, Sales.Sales, [Beginning Inventory].BegUnitPrice;

 

[vbaInet]

Can you convert the macro to vba so we can see the steps it's taking? There's a button for this.

 

[leafsrock100]

Here is the code:

Private Sub Preview_Click()
On Error GoTo Preview_Click_Err
DoCmd.OpenReport "Inventory Summary", acViewPreview, "", "[Forms]![Manage Inventory]![Products Subform].[Form]![TransactionDate] Between [Forms]![Filtered Inventory Summary Date Range]![BeginDate] And [Forms]![Filtered Inventory Summary Date Range]![EndDate]", acNormal

Preview_Click_Exit:
Exit Sub
Preview_Click_Err:
MsgBox Error$
Resume Preview_Click_Exit
End Sub

 

[vbaInet]

It should be:

DoCmd.OpenReport "Inventory Summary", acViewPreview, "", "[Name of Date Field] Between [Forms]![Filtered Inventory Summary Date Range]![BeginDate] And [Forms]![Filtered Inventory Summary Date Range]![EndDate]", acNormal

 

[leafsrock100]

I tried:

DoCmd.OpenReport "Inventory Summary", acViewPreview, "", "[TransactionDate] Between [Forms]![Filtered Inventory Summary Date Range]![BeginDate] And [Forms]![Filtered Inventory Summary Date Range]![EndDate]", acNormal

but that gave me an "Enter Parameter value" box

 

[vbaInet]

The form "Filtered Inventory Summary Date Range" must be open before you try to open the report. Are you opening the report from this form?

 

[leafsrock100]

yes, the parameter box appears when I click the preview button on that form. It doesn't appear when I leave it as the original code but the #Error message pops up

 

[vbaInet]

Let's get it changed then:

DoCmd.OpenReport "Inventory Summary", acViewPreview, "", "[[COLOR=red][B]TransactionDate[/B][/COLOR]]  Between #" & Me![BeginDate] & "# And  #" & Me![EndDate] & "#", acNormal

 

[leafsrock100]

Same thing happens, TransactionDate is unrecognized

 

[vbaInet]

Then transactionDate is not a field in your report. Check the exact name, it may contain spaces. Look in the Record Source property of your report and open the sql.