I just ran a test in Access 2007 of the Northwind sample database. I created a form with two text boxes and then created a query which works well without declaring the datatype of the parameters.
TRANSFORM Sum(Orders.Freight) AS SumOfFreight
SELECT Orders.EmployeeID
FROM Orders
WHERE (((Orders.OrderDate) Between [Forms]![frmDateRange]![txtStartDate] And [Forms]![frmDateRange]![txtEndDate]))
GROUP BY Orders.EmployeeID
PIVOT Weekday([OrderDate]) In (1,2,3,4,5,6,7);
If I remove the Column Headings property, the query complains as expected.
TRANSFORM Sum(Orders.Freight) AS SumOfFreight
SELECT Orders.EmployeeID
FROM Orders
WHERE (((Orders.OrderDate) Between [Forms]![frmDateRange]![txtStartDate] And [Forms]![frmDateRange]![txtEndDate]))
GROUP BY Orders.EmployeeID;
This has been my experience for decades of using Access. As I mentioned, it is still good practice to declare the parameter datatypes.
Happy 20th birthday (later this year) MS Access!!!
Duane Hookom
MS Access MVP
--- 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