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'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