ACCESS FEED: [MS_AccessPros] Re: Crosstab problems

Bill,
Well that worked. WOW. I would not have figured that our. I set the data type of [Forms]![f_RecordRankingChooser]![cboSport] as an Integer. I also tried a text just for fun and as expected it did not work. The 2nd query which is the report query pulls from the crosstab query. Both queries run correctly and sort per the combo box on my form.

But now the report does not run because it 2nd report query shows no available fields.

When I run the report it says "The Microsoft Access database engine does not recogonize " as a valid field name of expression"

I tried to design a new report off of the 2nd query and in the report wizard the query shows no fields available.

Another error message that is similar but I am not sure what next.

....except for tonight, sleep is next.

Thanks for your help.
Bill
MN

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Bill-
>
> Open your Crosstab query in Design view. Open the Parameters dialog. Define
> [Forms]![f_RecordRankingChooser]![cboSport] and set its data type from the
> drop-down list. Save your query.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Nashua, NH)
>
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> bill.singer@...
> Sent: Tuesday, March 06, 2012 9:13 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Re: Crosstab problems
>
> I guess do not exactly understand. I currently have no parameters because it
> will ask the end user a question they will not know how to answer. Maybe I do
> not understand what you are trying to tell me.
>
> Bill.
>
> Sent from my Verizon 4G smartphone
>
> ----- Reply message -----
> From: "John Viescas" <john@...>
> To: <MS_Access_Professionals@yahoogroups.com>
> Subject: [MS_AccessPros] Re: Crosstab problems
> Date: Tue, Mar 6, 2012 7:57 pm
> And is that parameter defined in your Crosstab query?
>
>
>
> John Viescas, author
>
> Microsoft Office Access 2010 Inside Out
>
> Microsoft Office Access 2007 Inside Out
>
> Building Microsoft Access Applications
>
> Microsoft Office Access 2003 Inside Out
>
> SQL Queries for Mere Mortals
>
> http://www.viescas.com/
>
> (Nashua, NH)
>
>
>
> -----Original Message-----
>
> From: MS_Access_Professionals@yahoogroups.com
>
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
>
> bill.singer@...
>
> Sent: Tuesday, March 06, 2012 8:56 PM
>
> To: MS_Access_Professionals@yahoogroups.com
>
> Subject: Re: [MS_AccessPros] Re: Crosstab problems
>
>
>
> John, sorry, typo. In the form name that . Should be a _
>
>
>
> Thanks
>
> Bill
>
>
>
> Sent from my Verizon 4G smartphone
>
>
>
> ----- Reply message -----
>
> From: "John Viescas" <john@...>
>
> To: <MS_Access_Professionals@yahoogroups.com>
>
> Subject: [MS_AccessPros] Re: Crosstab problems
>
> Date: Tue, Mar 6, 2012 7:18 pm
>
> Bill-
>
>
>
> You said your parameter is [Forms]![f.RecordRankingChooser]![cboSport] - but
>
>
>
> that's a funny form name. Perhaps you should get rid of the f. -
>
>
>
> [Forms]![RecordRankingChooser]![cboSport] - and then the form
>
>
>
> RecordRankingChooser must already be open and have a value in the control
>
>
>
> cboSport.
>
>
>
> John Viescas, author
>
>
>
> Microsoft Office Access 2010 Inside Out
>
>
>
> Microsoft Office Access 2007 Inside Out
>
>
>
> Building Microsoft Access Applications
>
>
>
> Microsoft Office Access 2003 Inside Out
>
>
>
> SQL Queries for Mere Mortals
>
>
>
> http://www.viescas.com/
>
>
>
> (Nashua, NH)
>
>
>
> -----Original Message-----
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
>
>
>
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of agent1of6
>
>
>
> Sent: Tuesday, March 06, 2012 8:08 PM
>
>
>
> To: MS_Access_Professionals@yahoogroups.com
>
>
>
> Subject: [MS_AccessPros] Re: Crosstab problems
>
>
>
> Duane or Bill,
>
>
>
> Maybe I am misunderstanding this. When I put in a parameter and run the query I
>
>
>
> get a pop up box that asks me for an input. I am guessing that is how it is
>
>
>
> suppose to work.
>
>
>
> Since I designed the database I know what number to put in but there is no way
>
>
>
> that an end user would know. Especially if I have multiple parameters with each
>
>
>
> needing an ID number, which the user does not see. I was hoping they could use a
>
>
>
> combo box or some other control.
>
>
>
> In error, I thought that setting the parameters would set the data type and
>
>
>
> allow me to use the information in an upstream query.
>
>
>
> Maybe I should sort the data, based on combo boxes in a query prior to the
>
>
>
> crosstab query?
>
>
>
> For now I am off to coach my kids basketball team but if you have any ideas I
>
>
>
> would welcome them.
>
>
>
> Bill
>
>
>
> MN
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@>
>
>
>
> wrote:
>
>
>
> >
>
>
>
> > Duane
>
>
>
> >
>
>
>
> > back as far as version 2000, I found it necessary to explicitly set the
>
>
>
> PARAMETERS clause when the parameter was a date data type. Text seemed to work
>
>
>
> okay. For that point on, I've made it my standard practice to always declare
>
>
>
> them.
>
>
>
> >
>
>
>
> > Regards,
>
>
>
> > Bill Mosca, Founder - MS_Access_Professionals
>
>
>
> > http://www.thatlldoit.com
>
>
>
> > Microsoft Office Access MVP
>
>
>
> > https://mvp.support.microsoft.com/profile/Bill.Mosca
>
>
>
> >
>
>
>
> >
>
>
>
> >
>
>
>
> > --- In MS_Access_Professionals@yahoogroups.com, "Duane" <duanehookom@> wrote:
>
>
>
> > >
>
>
>
> > > I think there is an exception to "Crosstab queries are an exception. You
>
>
>
> must ALWAYS define all parameters with a PARAMETERS clause at the beginning of
>
>
>
> the query".
>
>
>
> > >
>
>
>
> > > At least in earlier versions, the PARAMETERS were not required if you set
>
>
>
> the Column Headings property. Some how this seemed to resolve the datatypes or
>
>
>
> something.
>
>
>
> > >
>
>
>
> > > It's probably still a good idea to specify the parameter datatypes.
>
>
>
> > >
>
>
>
> > > Duane Hookom
>
>
>
> > > MS Access MVP
>
>
>
> > >
>
>
>
> > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
>
>
>
> wrote:
>
>
>
> > > >
>
>
>
> > > > Bill-
>
>
>
> > > >
>
>
>
> > > > It's always advisable to explicitly declare all query parameters, but most
>
>
>
> of
>
>
>
> > > > the time you can get away without it. Crosstab queries are an exception.
>
>
>
> You
>
>
>
> > > > must ALWAYS define all parameters with a PARAMETERS clause at the
>
>
>
> beginning of
>
>
>
> > > > the query. In 2007 in Query Design, click Parameters in the Show / Hide
>
>
>
> group
>
>
>
> > > > on the Design tab.
>
>
>
> > > >
>
>
>
> > > > John Viescas, author
>
>
>
> > > > Microsoft Office Access 2010 Inside Out
>
>
>
> > > > Microsoft Office Access 2007 Inside Out
>
>
>
> > > > Building Microsoft Access Applications
>
>
>
> > > > Microsoft Office Access 2003 Inside Out
>
>
>
> > > > SQL Queries for Mere Mortals
>
>
>
> > > > http://www.viescas.com/
>
>
>
> > > > (Nashua, NH)
>
>
>
> > > >
>
>
>
> > > >
>
>
>
> > > >
>
>
>
> > > >
>
>
>
> > > > -----Original Message-----
>
>
>
> > > > From: MS_Access_Professionals@yahoogroups.com
>
>
>
> > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Singer
>
>
>
> > > > Sent: Tuesday, March 06, 2012 3:22 PM
>
>
>
> > > > To: MS_Access_Professionals@yahoogroups.com
>
>
>
> > > > Subject: RE: [MS_AccessPros] Crosstab problems
>
>
>
> > > >
>
>
>
> > > > Yesterday I was having issues with Null fields in my Crosstab query when
>
>
>
> > > > trying to calculate wins and losses for a team.
>
>
>
> > > >
>
>
>
> > > > That problem is fixed. That problem is fixed and teams with no wins or
>
>
>
> > > > loses get a zero in their field. My teams sort correctly based on a
>
>
>
> > > > calculated win percentage. My reports sort correctly by grade and sport.
>
>
>
> I
>
>
>
> > > > thought I was on my way to victory but. Now I want to have a 2nd query
>
>
>
> pull
>
>
>
> > > > information from the crosstab query for just one specific sport, such as
>
>
>
> > > > Basketball. I set up a form, (I call it a Chooser form) with an unbound
>
>
>
> > > > combo box that looks at the t_sport and selects a sport and stores the
>
>
>
> value
>
>
>
> > > > for later use. Column 1 is bound, which is where the SpoID number is.
>
>
>
> > > >
>
>
>
> > > >
>
>
>
> > > >
>
>
>
> > > > In all the other queries I have set up like this everything runs perfect.
>
>
>
> > > > With this 2nd query, as soon as I add the crosstab query to the design
>
>
>
> > > > window I get the following message.
>
>
>
> > > > The Microsoft Office Access database engine does not recognize
>
>
>
> > > > '[Forms]![f.RecordRankingChooser]![cboSport]'
>

>
>
> > > > If I remove the crosstab query from my query design window it runs fine
>
>
>
> > > > again. So now I have a great crosstab query that calculates wins, losses
>
> etc, but I have no way to sort the information by sport.
>
>

> > > > I do not understand why pulling information from a crosstab query will all
>
>
> > > > of sudden not allow my query to see a combo box value.
>
>

>
> > > > Do you need the SQL
>
>
> > > > Thank you for your wisdom. Yesterday I researched the Nz function so I
>
>
>
> > > > now have added that to my toolbox.
>
>