Query Problem

[dref]

Hello,
I want a query that works with parameters. When i put in outlet and month when prompted no data shows. see the attached file.


Please Help
Thx
Dref

[alansidman]

What format should the month be entered?

Also in your Expression1, you had a formula error that was pushing out an additional parameter. change [publisher].[date] to [publisher],[date]

Alan

[dref]

Hello,
It should be mmm,yyyy ( likeJanuary 2012).
Publisher was a mistake. It should be tblbookings.
Could u plz upload for me the one that works.
Thx

[alansidman]

Here is the SQL statement to correct your query.

In your primary table, you have the outlet linked to lookup table. You need to bring the lookup table into the query and use that as the source of the criteria for the name.

SELECT tblbookings.Date1, tblbookings.Product, tblbookings.Copies, tblbookings.Returns, tblbookings.Sales, tblbookings.Vendor_PX, tblbookings.Sales_Value, Format$(tblbookings.[Date1],'mmmm yyyy') AS Month1
FROM tbloutlets INNER JOIN tblbookings ON tbloutlets.OutletID = tblbookings.OutLet
WHERE (((Format$([tblbookings].[Date1],'mmmm yyyy'))=[Enter month in Format mmmm yyyy]) AND ((tbloutlets.Outlet) Like "*" & [Enter Outlet] & "*"));

Additionally, there is no need to have an aggregate on this query when all you are doing is looking up by outlet. There is no aggregation going on.