Crosstab Query Parameter problems (1 Viewer)

Hi all, I'm a newbie to Access and really only use the design views to create everything I need, rather than VBA or SQL, so I apologise in advance if this is a stupid question, posted in the wrong forum or I struggle to understand any advice.

I've created a select query with date parameters which are collected via a form, and then created a crosstab query based on this select query (parameters are specified in both select and transform queries). I then use another form as a switchboard, which the user uses to open the report that's based on the crosstab query.

I've set the defaul values on the parameter collection form to the earliest time point of the data and today's date as the start and end dates.

My problem is that when the report opens and the defaul dates are used to generate the report data it runs fine, but when the user specifies a different, later start date the following error message "the microsoft access database engine does not recognize " as a valid field name or expression",.

After a bit of digging on this forum I decided that this might be due to blank fields/null values so I amended the TRANSFORM line of the SQL to the following

TRANSFORM CLng(NZ(Sum([Query_Data Per Endoscopist_incomplete_Exam].CountOfid),0)) AS SumOfCountOfid

Now I get the following error message saying
"Invalid bracketing of name '[Forms]![date range![Start Date]].

It seems to me almost like the crosstab/transform query doesn't recognise the parameter path even though the select query does, or doesn't link with it?

I'd appreciate any help with this - I've been tearing my hair out with this issue all week!

Thanks so much

I am not sure if this is a typo, but there is a extra ] (square bracket) in the above..

Loser said:

"Invalid bracketing of name '[Forms]![date range![Start Date]].

Click to expand...

Sorry, that was a typo - the error message actually has an additional square bracket at the beginning of it...

"invalid bracketing of name '[[Forms]![Date ramge][Start Date] ]'"

When I checked the SQL and the design view there is not square brackets round the whole statement, just the error message seems to put an extra set on ?

If you could copy and paste the whole SQL statement it would be easier to spot the error I believe..

Ok, no problem - thanks!

Select query:
PARAMETERS [Forms]![Date range]![Start Date] DateTime, [Forms]![Date range]![End Date] DateTime;
SELECT [Query_key data per endoscopist].nc_user.name, nc_code_incompleteexam.name, Count(nc_code_incompleteexam.id) AS CountOfid, nc_coloscopy.invitationNo
FROM ((nc_coloscopy_incompleteexam INNER JOIN nc_code_incompleteexam ON nc_coloscopy_incompleteexam.incompleteExams_id = nc_code_incompleteexam.id) INNER JOIN (nc_coloscopy INNER JOIN [Query_key data per endoscopist] ON nc_coloscopy.id = [Query_key data per endoscopist].coloscopy_id) ON nc_coloscopy_incompleteexam.[_id] = nc_coloscopy.id) INNER JOIN nc_examination ON nc_coloscopy.invitationNo = nc_examination.invitationNo
WHERE (((nc_examination.dateOfExam) Between [Forms]![Date range]![Start Date] And [Forms]![Date range]![End Date]))
GROUP BY [Query_key data per endoscopist].nc_user.name, nc_code_incompleteexam.name, nc_coloscopy.invitationNo;

Crosstab query (without correcting for Null Values - returns error messge "the microsoft access database engine does not recognize " as a valid field name or expression"):
PARAMETERS [Forms]![Date range]![Start Date] DateTime, [Forms]![Date range]![End Date] DateTime;
TRANSFORM Sum([Query_Data Per Endoscopist_incomplete_Exam].CountOfid) AS SumOfCountOfid
SELECT [Query_Data Per Endoscopist_incomplete_Exam].nc_code_incompleteexam.name
FROM [Query_Data Per Endoscopist_incomplete_Exam] INNER JOIN nc_examination ON [Query_Data Per Endoscopist_incomplete_Exam].invitationNo=nc_examination.invitationNo
WHERE (((nc_examination.dateOfExam) Between [Forms]![Date range]![Start Date] And [Forms]![Date range]![End Date]))
GROUP BY [Query_Data Per Endoscopist_incomplete_Exam].nc_code_incompleteexam.name
PIVOT [Query_Data Per Endoscopist_incomplete_Exam].nc_user.name;

Crosstab query correcting for null values as above but with TRANSFORM amended as below: TRANSFORM CLng(NZ(Sum([Query_Data Per Endoscopist_incomplete_Exam].CountOfid),0)) AS SumOfCountOfid