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
> > >
> >
>
Tidak ada komentar:
Posting Komentar