No luck sorting the record before I do the crosstab.
The sort query runs fine. But when I try to do a crosstab query I get...
The Microsoft Office Access database engine does not recognize
'[Forms]![f.RecordRankingChooser]![cboSport]'
Now what?
Bill
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> 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.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > Bill
> > > >
> > > > MN
> > > >
> > > >
> > > >
> > > > [Non-text portions of this message have been removed]
> > > >
> > > >
> > > >
> > > > ------------------------------------
> > > >
> > > > Yahoo! Groups Links
> > > >
> > >
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
Tidak ada komentar:
Posting Komentar