By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,940 Members | 611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,940 IT Pros & Developers. It's quick & easy.

Crosstab Query Parameters

P: 10
I've created a Crosstab Query displaying the Consultant ID and the corresponding amount of Appointments they have on a specific date, as well as a count of the number of appointments.

I am trying to get it to display as a subform in the Consultant Form, and I have read around stating that you need to declare your parameters in order for it to work. I have tried to do so but the error message I get is "Too few Parameters. Expected 1."

I'm most likely just making a mockery of the parameters, but I can't find away around it as it's my first time doing crosstab, anyone able to led a hand? (Access 2007)

Expand|Select|Wrap|Line Numbers
  1.  PARAMETERS [Forms].[Consultant].[Consultant ID] Long;
  2. TRANSFORM Count(Appointment.[Appointment Number]) AS [CountOfAppointment Number]
  3. SELECT Appointment.[Consultant ID], Count(Appointment.[Appointment Number]) AS [Total Of Appointment Number]
  4. FROM Appointment
  5. WHERE (((Appointment.[Consultant ID])=[Forms].[Consultant].[Consultant ID]) AND ((Format([Appointment Date],"Short Date"))=Date()))
  6. GROUP BY Appointment.[Consultant ID]
  7. PIVOT Appointment.[Appointment Date]; 
Jan 13 '08 #1
Share this Question
Share on Google+
5 Replies


FishVal
Expert 2.5K+
P: 2,653
I've created a Crosstab Query displaying the Consultant ID and the corresponding amount of Appointments they have on a specific date, as well as a count of the number of appointments.

I am trying to get it to display as a subform in the Consultant Form, and I have read around stating that you need to declare your parameters in order for it to work. I have tried to do so but the error message I get is "Too few Parameters. Expected 1."

I'm most likely just making a mockery of the parameters, but I can't find away around it as it's my first time doing crosstab, anyone able to led a hand? (Access 2007)

Expand|Select|Wrap|Line Numbers
  1.  PARAMETERS [Forms].[Consultant].[Consultant ID] Long;
  2. TRANSFORM Count(Appointment.[Appointment Number]) AS [CountOfAppointment Number]
  3. SELECT Appointment.[Consultant ID], Count(Appointment.[Appointment Number]) AS [Total Of Appointment Number]
  4. FROM Appointment
  5. WHERE (((Appointment.[Consultant ID])=[Forms].[Consultant].[Consultant ID]) AND ((Format([Appointment Date],"Short Date"))=Date()))
  6. GROUP BY Appointment.[Consultant ID]
  7. PIVOT Appointment.[Appointment Date]; 
Hi, there.

The problem, I guess, in invalid form referencing.
Try Forms!Consultant.ConsultantID or Forms!Consultant!ConsultantID in query body. Parameters declaration is really not needed.

Regards,
Fish

P.S. And there is no need to use format() to compare one date variable with another.
Jan 13 '08 #2

P: 10
Thanks for the speedy response Fish. I tried both [Forms]![Consultant]![Consultant ID] and [Forms]![Consultant].[ConsultantID] but neither seemed to work. I'm now getting the "The Microsoft Access Engine does not recognise [Forms]![Consultant]![Consultant ID] as a valid field name or expression. I also removed the formatting as you said it made no difference.

So here is what I have so far

Expand|Select|Wrap|Line Numbers
  1.  TRANSFORM Count(Appointment.[Appointment Number]) AS [CountOfAppointment Number]
  2. SELECT Appointment.[Consultant ID], Count(Appointment.[Appointment Number]) AS [Total Of Appointment Number]
  3. FROM Appointment
  4. WHERE (((Appointment.[Consultant ID])=Forms!Consultant![Consultant ID]) And ((Appointment.[Appointment Date])=Date()))
  5. GROUP BY Appointment.[Consultant ID]
  6. PIVOT Appointment.[Appointment Date];
  7.  
I've had a number of issues with the whole Forms!form!field thing, can never seem to get it right myself. Once again appreciate the help
Jan 13 '08 #3

FishVal
Expert 2.5K+
P: 2,653
The syntax seems to be ok. The only question is whether the form [Consultant] is open while the query runs and whether it is open as form (not as subform on another form)?
Jan 13 '08 #4

P: 10
I've tried both in the form as a subform, and standalone.

I understand why it wouldn't work standalone as it does not have thhe consultant ID info to draw from, But whenever i select the subform as the cross tab I get the error message.
Jan 15 '08 #5

FishVal
Expert 2.5K+
P: 2,653
Hi, odavison.

If the form is open as subform, then you don't reference it properly. Form opened in subform control is not a member of "Forms" collection.

Try the following syntax:
Forms![Main form name]![Consultant].Form![Consultant ID]
Jan 15 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.