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