Pass dates from form to report to query

[Jim Stanicki]

I am trying to enter dates in a form that calls a report that invokes a query that uses the dates. It has been a less then satisfying experience. I am getting a Run-Time error 3122. Is it possible and I need to work on syntax or do I need to think of another way? BTW how do I lookup the Run-Time errors?
Thanks for helping an old guy learn new tricks.
Jim

 

[neileg]

I usually Google the error codes.

If you post the SQL of you query we may get a better idea of the problem!

 

[Jim Stanicki]

"If you post the SQL of you query we may get a better idea of the problem!"

Thanks Neil,

SELECT [Inventory Transactions Extended].Inventory.ID, [Inventory Transactions Extended].Inventory.Item, [Inventory Transactions Extended].[Transaction Type], Sum([Inventory Transactions Extended].[Stock Qty]) AS [SumOfStock Qty], [Inventory Transactions Extended].[Reorder Level], [Inventory Transactions Extended].[Target Stock Level], [Inventory Transactions Extended].Category, [Inventory Transactions Extended].Location, [Suppliers Extended].[Supplier Name], Sum([Inventory Transactions Extended].[Deployed Qty]) AS [SumOfDeployed Qty], Sum([Inventory Transactions Extended].[RMA Qty]) AS [SumOfRMA Qty]
FROM [Inventory Transactions Extended] LEFT JOIN [Suppliers Extended] ON [Inventory Transactions Extended].Supplier = [Suppliers Extended].ID
GROUP BY [Inventory Transactions Extended].Inventory.ID, [Inventory Transactions Extended].Inventory.Item, [Inventory Transactions Extended].[Transaction Type], [Inventory Transactions Extended].[Reorder Level], [Inventory Transactions Extended].[Target Stock Level], [Inventory Transactions Extended].Category, [Inventory Transactions Extended].Location, [Suppliers Extended].[Supplier Name]
HAVING ((([Inventory Transactions Extended].[Created Date])<DateAdd('d',1,[Forms]![frmStockLevelDates]![txtBegDate])));

 

[neileg]

Looks OK to me. Does it run without the HAVING clause?

I usually try an build queries up from the basics. Eg start without the SUMS and the HAVING, and then add thse in.

 

[Jim Stanicki]

Yes it works without the Having clause, but that is because that also eliminates the reference to the date on the form DateAdd('d',1,[Forms]![frmStockLevelDates]![txtBegDate])
Do I need the form to create a table with the dates then reference the new table in the query?
Thanks for your help
Jim

 

[neileg]

No you should'nt need another table.

Strictly speaking you should enter the interval in DateAdd as "d" not 'd' but it usually doesn't matter. Are you sure that the text box is accurately referenced and contains a valid date?