MS Access "does not recognise ... as a valid field name"

0
votes
TRANSFORM Count(Breakdown.Hours) AS CountOfHours
SELECT Breakdown.Description, Breakdown.Customer, Count(Breakdown.Hours) AS [Total Of Hours]
FROM Breakdown
WHERE Breakdown.Code = [Forms]![Pre-Break]![Code]
GROUP BY Breakdown.Description, Breakdown.Customer
PIVOT Breakdown.Day;

This is the SQL statement I am tring to run, I have a combo box in Pre-Break form form called Code , This syntax has worked with other SQL statements for me where I haven't had GROUP BY and PIVOT after the WHERE. I have tried putting the after at the end but I still get an error. Could anyone tell me what I am doing wrong ??

1
sqlms-access

1 Answers

1
votes

Try explicitly declaring the form-value as a parameter - made a difference when I tried to run this:

PARAMETERS [Forms]![Pre-Break]![Code] Text (255);
TRANSFORM Count(Breakdown.Hours) AS CountOfHours
SELECT Breakdown.Description, Breakdown.Customer, Count(Breakdown.Hours) AS [Total Of Hours]
FROM Breakdown
WHERE Breakdown.Code = [Forms]![Pre-Break]![Code]
GROUP BY Breakdown.Description, Breakdown.Customer
PIVOT Breakdown.Day;

If the Code is an integer then use Short instead of Text(255).