Microsoft database engine does not recognize " as a valid field or expression

[Jmoore86]

Hi Guys,


As per the title - I have a crosstab query with a date field (Collection Date) where i've got a parameter [Enter Date] to force the user to specify an individual date which will filter the crosstab query. This works perfectly (yay Access) however, when I try to run a report that uses said crosstab query as its data source, I get prompted a few times for a parameter then i'm left with the very annoying "Microsoft database engine does not recognize " as a valid field or expression" message.

The SQL for my crosstab is:

PARAMETERS [Enter Date] DateTime;
TRANSFORM Sum(qryProductsPerDay.Expr1) AS SumOfExpr1
SELECT qryProductsPerDay.[Collection Date], qryProductsPerDay.Product_Name, qryProductsPerDay.Quantity, qryProductsPerDay.UOM, Count(qryProductsPerDay.Expr1) AS [Total Of Expr1]
FROM qryProductsPerDay
WHERE (((qryProductsPerDay.[Collection Date])=[Enter Date]))
GROUP BY qryProductsPerDay.[Collection Date], qryProductsPerDay.Product_Name, qryProductsPerDay.Quantity, qryProductsPerDay.UOM
PIVOT qryProductsPerDay.[Platter Name];

As I say, this works perfectly when running the query on its own. The "Collection Date" text box on my report has the following text in it (created by the report wizard, not me) "=Format$([Collection Date],"Long Date",0,0)" Is this where my issue lies?

As ever - thanks very much for looking into this for me!

Jon

[ranman256]

its very difficult to bind a report to a Xtab query, since the fieldname can be random and wont match the boxes in the report.
Unless you make a box for every possible outcome of the xtab query.

usu. xtab queries are viewed best as datasheets.

[isladogs]

Disagree completely.
If you know the headings e.g Jan to Dec you can set fixed column headings.
However where the headings are dynamic, it is fairly simple to write code to manage that.

I have many examples of reports based on dynamic crosstab headings. See these links for two examples
http://www.mendipdatasystems.co.uk/cert/4594365453
http://www.mendipdatasystems.co.uk/e...ies/4594398115
Very similar code is used in the reports in each of those example apps and can be used in forms as well (continuous or datasheet)

[Jmoore86]

Cheers, Ridders.

I know that there can/ will be up to 15 possible headings and they are all predictable; they're all dates from 18th - 31st December 2018. What's the process for setting these fixed column headings?

Thanks again!

[isladogs]

Answering on my phone as we have no power for most of today.
You only need to specify headings if you want all columns to appear even if there is no data in some.
To do so, enter the values in the Column Headers part of the query property sheet
You need to separate each with a semicolon

[Jmoore86]

No power sounds grim! Cheers for the effort

I've got the report to run OK - but when I try and filter via a parameter on the crosstab query, it works fine, but doesn't on the report. I'm entering a valid date but it just doesn't seem to accept it... the field is used as a grouping within the report, if that makes a difference?

[isladogs]

Try removing the field from the grouping temporarily.
If you can't find a solution, post a stripped down version and I'll look at it later when I can do so ... unless someone else does so first