Odd Parameter request in Crosstab

Good Morning,

I read the threads about creating parameters in crosstab queries and this 
helped me to get it to work but the parameter box pops up when I try to save 
the query.  Though my changes are saved, I don’t think this is normal?  It 
happens in all views.   Can anyone tell me what might be wrong?  Here is my 
SQL:

PARAMETERS [Enter Month and Year] DateTime;
TRANSFORM Sum(Trans_Mstr_ODC.ODC_Cost) AS SumOfODC_Cost
SELECT Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category, 
Trans_Mstr_ODC.Product, Sum(Trans_Mstr_ODC.ODC_Cost) AS [Total Of ODC_Cost]
FROM Trans_Mstr_ODC
WHERE (((Trans_Mstr_ODC.TransDate)=[Enter Month and Year]))
GROUP BY Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category, 
Trans_Mstr_ODC.Product
PIVOT Trans_Mstr_ODC.ODC_Cost_Category;

TIA,
Kay

0
Utf
3/23/2010 2:02:01 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
752 Views

Similar Articles

[PageSpeed] 14

Crosstabs can be a little buggy with parameters. Your best bet is to create a 
normal select query that includes the declared parameter and any other 
criteria to gather up the records and fields that you want to see. Make sure 
that this query works then save it with a name. Next create a crosstab query 
using the first query as its record source.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"AccessKay" wrote:

> Good Morning,
> 
> I read the threads about creating parameters in crosstab queries and this 
> helped me to get it to work but the parameter box pops up when I try to save 
> the query.  Though my changes are saved, I don’t think this is normal?  It 
> happens in all views.   Can anyone tell me what might be wrong?  Here is my 
> SQL:
> 
> PARAMETERS [Enter Month and Year] DateTime;
> TRANSFORM Sum(Trans_Mstr_ODC.ODC_Cost) AS SumOfODC_Cost
> SELECT Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category, 
> Trans_Mstr_ODC.Product, Sum(Trans_Mstr_ODC.ODC_Cost) AS [Total Of ODC_Cost]
> FROM Trans_Mstr_ODC
> WHERE (((Trans_Mstr_ODC.TransDate)=[Enter Month and Year]))
> GROUP BY Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category, 
> Trans_Mstr_ODC.Product
> PIVOT Trans_Mstr_ODC.ODC_Cost_Category;
> 
> TIA,
> Kay
> 
0
Utf
3/23/2010 2:36:02 PM
Thank you.  I will do this.  I’ve been trying to not use so many layers of 
queries but I guess I’ll have to accept the crosstab’s buggy nature.

Kay

"Jerry Whittle" wrote:

> Crosstabs can be a little buggy with parameters. Your best bet is to create a 
> normal select query that includes the declared parameter and any other 
> criteria to gather up the records and fields that you want to see. Make sure 
> that this query works then save it with a name. Next create a crosstab query 
> using the first query as its record source.
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> 
> "AccessKay" wrote:
> 
> > Good Morning,
> > 
> > I read the threads about creating parameters in crosstab queries and this 
> > helped me to get it to work but the parameter box pops up when I try to save 
> > the query.  Though my changes are saved, I don’t think this is normal?  It 
> > happens in all views.   Can anyone tell me what might be wrong?  Here is my 
> > SQL:
> > 
> > PARAMETERS [Enter Month and Year] DateTime;
> > TRANSFORM Sum(Trans_Mstr_ODC.ODC_Cost) AS SumOfODC_Cost
> > SELECT Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category, 
> > Trans_Mstr_ODC.Product, Sum(Trans_Mstr_ODC.ODC_Cost) AS [Total Of ODC_Cost]
> > FROM Trans_Mstr_ODC
> > WHERE (((Trans_Mstr_ODC.TransDate)=[Enter Month and Year]))
> > GROUP BY Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category, 
> > Trans_Mstr_ODC.Product
> > PIVOT Trans_Mstr_ODC.ODC_Cost_Category;
> > 
> > TIA,
> > Kay
> > 
0
Utf
3/23/2010 3:16:01 PM
That is not the standard behavior.  Simply saving the query should not display 
the parameter prompt.

What version of Access are you using?

I would try copying the SQL text into a new blank query and see if you get the 
same behavior.  If not, then you have something in your malfunctioning query 
that is causing the problem.  The simplest cure would be to delete the 
offending query and rename the new query with the name of the old query.

BACK UP YOUR DATABASE before doing this.  Just in case.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

AccessKay wrote:
> Good Morning,
> 
> I read the threads about creating parameters in crosstab queries and this 
> helped me to get it to work but the parameter box pops up when I try to save 
> the query.  Though my changes are saved, I don’t think this is normal?  It 
> happens in all views.   Can anyone tell me what might be wrong?  Here is my 
> SQL:
> 
> PARAMETERS [Enter Month and Year] DateTime;
> TRANSFORM Sum(Trans_Mstr_ODC.ODC_Cost) AS SumOfODC_Cost
> SELECT Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category, 
> Trans_Mstr_ODC.Product, Sum(Trans_Mstr_ODC.ODC_Cost) AS [Total Of ODC_Cost]
> FROM Trans_Mstr_ODC
> WHERE (((Trans_Mstr_ODC.TransDate)=[Enter Month and Year]))
> GROUP BY Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category, 
> Trans_Mstr_ODC.Product
> PIVOT Trans_Mstr_ODC.ODC_Cost_Category;
> 
> TIA,
> Kay
> 
0
John
3/23/2010 4:02:18 PM
I did what you said about copying the SQL into a fresh query and this worked. 
 Strange…but my problem is solved (for now).  BTW, I’m using Access 2007.

Thank you,
Kay


"John Spencer" wrote:

> That is not the standard behavior.  Simply saving the query should not display 
> the parameter prompt.
> 
> What version of Access are you using?
> 
> I would try copying the SQL text into a new blank query and see if you get the 
> same behavior.  If not, then you have something in your malfunctioning query 
> that is causing the problem.  The simplest cure would be to delete the 
> offending query and rename the new query with the name of the old query.
> 
> BACK UP YOUR DATABASE before doing this.  Just in case.
> 
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
> 
> AccessKay wrote:
> > Good Morning,
> > 
> > I read the threads about creating parameters in crosstab queries and this 
> > helped me to get it to work but the parameter box pops up when I try to save 
> > the query.  Though my changes are saved, I don’t think this is normal?  It 
> > happens in all views.   Can anyone tell me what might be wrong?  Here is my 
> > SQL:
> > 
> > PARAMETERS [Enter Month and Year] DateTime;
> > TRANSFORM Sum(Trans_Mstr_ODC.ODC_Cost) AS SumOfODC_Cost
> > SELECT Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category, 
> > Trans_Mstr_ODC.Product, Sum(Trans_Mstr_ODC.ODC_Cost) AS [Total Of ODC_Cost]
> > FROM Trans_Mstr_ODC
> > WHERE (((Trans_Mstr_ODC.TransDate)=[Enter Month and Year]))
> > GROUP BY Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category, 
> > Trans_Mstr_ODC.Product
> > PIVOT Trans_Mstr_ODC.ODC_Cost_Category;
> > 
> > TIA,
> > Kay
> > 
> .
> 
0
Utf
3/23/2010 6:57:01 PM
Reply:

Similar Artilces:

Odd date format
In Column A I need to format a date, without dashes or slashes, in this way: yymmdd In Column B I need Excel to recognize it as a date, in that order and convert it to a regular date such as: mm/dd/yy Is there any way of doing this? It's driving me crazy. Surely there has to be a way. TIA! Cindy Cindy Hi Cindy, Can be done, but a lot simpler is to do it the other way around: Enter the date in Column A as mm/dd/yy In column B: =A1, Format>Cells>Custom, in the Type box, enter yymmdd If you really need it otherwise: post again! -- Kind Regards, Niek Otten Microsoft MVP -...

xsd.exe bug or odd xsd file
I was given a couple of XSD files so that I could generate a client that targeted a SOAP/RPC web service. One of the XSDs includes a definition of an element that is a sequence of row elements, which in turn is defined as a sequence of column elements. The CS class file generated defines the sequence of row elements "rows as column() ()" rather than "row() As row". This seems to give trouble to XmlSerializer. With XmlSerializerPreCompiler I was able to narrow the problem down and guess at the problem. I made the change that I indicated above because this seems...

Copy/Paste of QueryTable doesn't adjust relative parameters
Column A 1. Blue 2. Red 3. Green A1 is a parameter to the query - on the QueryTable Parameters dialog, I have "Get the value from the following cell: =Sheet1!A1" Note that this is a relative reference, not $A$1. When I copy the whole QueryTable and paste it elsewhere on the sheet the parameter of the new QueryTable is still set to A1, rather than A2 for example. How can I create the QueryTable so that the parameter references are updated properly when I copy the report? Please note...I am not VBA literate - yet:) So I am looking for a solution here that does not require VBA - or ...

ERROR: "The record you are requesting is currently unavailable"
CRM 3.0, using Web client, Windows Server 2003 SP1 for SBS. Have found no good solution for this problem I've been getting since installation couple days ago. When trying to create a new appointment, upon save get the error: "The record that you are requesting is currently unavailable. Either the record was not found or you do not have sufficient security permissions to view it." Upon clicking "Ok" I get the error: "General failure in scheduling engine" with a "Ignore and Save" button which will save the appointment, but still marks the appointm...

openning directly a meeting request
Hi everybody ! In a Web Intranet Application, I would like : 1. to open by a web link a new Meeting Request 2 add one or several people to this meeting request If I use OWA : 1. I can open with this url http://<server>/exchange/<user>/Calendar/?Cmd=new&mm=11&dd=5&yy=2003 2. Is it possible to add people ? If I use Outlook XP 1. I can just open a window with the calendar using <a href="outlook:calendar">link</a> but I can no more open a new meeting request as we could do it with outlook 2000 like this : href...

Intelligent Message Filter behaves very odd
I have the IMF that comes with Exchange 2003 sp2 installed and working very well, (I never had IMF v1 installed ) the only issue I have is when I set up my custom thresholds and actions under "Gateway blocking configuration" they are changed by it self to defaults the next day and I have to be change them back to my desire settings everyday. Does someone know how to fix this? or is there any registry key or setting in AD where to set this up? I've read the IMF guide but I found nothing about this. I have 2 exchange back-end servers and 1 front-end server, I also have tried ...

Odd Issue
Exchange 2003 on native 2003 AD. I am really baffled. I have a distribution group called school admin. It has 2 users in it, Jodi and Mark. When someone (anyone) makes a calander event and adds School Admin to the attendies, Jodi gets the request, Mark does not. Here's the wierd part. Beth and Robyn, are not on the DL, are not connected to that school. Beth get the invitation and whoever sent the invite gets a NDR about Robyn. I have deleted the DL and recreated it. I have looked at Jodi and Marks deligation and it's clean. Where else can I look? Thanks, Fred That ...

Odd problem
The balance in the Account list for my Pending Account does not agree with the balance that is displayed when the account is selected. The Account list shows a balance of $38.50 The Account balanced when you open the account is $5.00 Actual account balance is $5.00 So how can I fix this?? I also noticed that one of my other accounts is wrong. I should mention that Money(2004) did crash during the last download. "Patrick Simonds" <ordnance1@comcast.net> wrote in message news:OiMZOY93DHA.2404@TK2MSFTNGP12.phx.gbl... > The balance in the Account list for my Pending Acco...

"Exceptional Demand" "Help" translation requested.
Hi, If you do a SOP Transaction Entry in GPv8, and use the first blue arrow on a line item (just above the Item Number filed), it brings up the "Sale Item Detail Entry" window. There is a filed there called "Exceptional Demand". The Help for that field says: "Exceptional Demand If you use ordered quantities to calculate sales forecasts, mark this option if the line item constitutes demand that is more than what is typical for an item-site. This type of demand will be treated differently by a forecasting system. If you change the Exceptional Demand indicator for...

Adding Report Parameter
Hi there, if I add the following - to the parentreport in section <Subreport Name="testSR"><Parameters> <Parameter Name="testString"> <Value>="hallo world"</Value> </Parameter> - to the subreport in section <ReportParameters> <ReportParameter Name="testString"> <DataType>String</DataType> <Nullable>true</Nullable> <AllowBlank>true</AllowBlank> </ReportParameter> I get "error: the subreport could not be shown" Why? TIA, Mart...

Outputing Descriptions for Parameters in WSDL
Is there anyway of outputting descriptions to the parameters of webservice functions. I am trying to populate the documentation element of the message/part e.g. .... <message name="fooSoapIn"> <part name="a" type="s:string"> <documentation>This is a very important attribute</documentation> </part> </message> .... Regards Simon ...

Where are CDataExchange Parameters Stored?
Hi All, Is there someplace I can find information on the variables created using MFC Wizards, the variables used to for DDX in dialog boxes? I created an Integer variable associated with a text box. I'd like to review what I set as the upper range but can't seem to find it anywhere. I thought it would be in the dialog box header file somewhere but no sign of it. Versions of VS prior to .NET at least allowed me to see the settings but not the current version. Thanks for any help. -- Jonathan Wood SoftCircuits http://www.softcircuits.com Available for consulting: http://www.softcir...

code for odd character
I have an odd character that shows up in docs that are from an export from another program. I need to replace it with nothing but I don't know the ascii code for the character and cant' find it in any tables. Is there something in word that will let me highlight a character and then return it's code? The character is a little arrow that points to the right. (not this: >). It's not a keyboard character and I can't copy and paste it itno my vba code. Possibly a tab character? (See http://word.mvps.org/FAQs/Formatting/NonPrintChars.htm) If so, the code ...

passing parameters from a from to a query
Hi, I'm trying to pass params from my form to a query. In the query I ref the form value in the criteria: [form]![id] I've tested my query directly and it works perfectly, however when I call my query from my form (open at time of call), I am still prompted to supply the param. I have tried encasing my form value in quotes, but of course then it is seen as a string literal. Is there a simple way to tell the query that [form]![ID] isn't a prompt? (I have looked at a zillion posting here but haven't found one for this answer) Thank you!!! granola911...

Numbering oddly
Qt based on Word 2007 Is there an easy way to set numbering to the following: 1. 3. 5. And so on Use the following field construction { = { SEQ Odd } * 2 - 1 } You must use Ctrl+F9 to insert each pair of field delimiters and use Alt+F9 to toggle off their display. After creating the field construction, you could select it (and probably the following tab) and create an autotext entry of it to facilitate its use. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins ...

Crosstab query columns not adding correctly
I have a crosstab query based off of a table "tblClaims". TRANSFORM (Nz(Sum(tblClaims.Count),0)) AS [The Value] SELECT tblClaims.LOC, tblClaims.RvwRsn, Sum(tblClaims.Count) AS Total FROM tblClaims GROUP BY tblClaims.LOC, tblClaims.RvwRsn PIVOT tblClaims.Report In ("C170","RC 85"); My end result should have one row of data for, example: LOC RvwRsn C170 RC 85 72 BTMJ 25 10 But instead I am getting: LOC RvwRsn C170 RC 85 72 BTMJ ...

Pivot Table Request
I tried to make a Pivot Table do the following: "Show me the right value - copy the value to a Temporarily worksheet" I tried the code shown below. How ever it keeps on giving me the same error in: ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="", ColumnFields:="Activity", PageFields:="ARF Code" Does someone see the error? Thanks in Advance! Sub Get_DATA_ARF() Dim cLastRow As Long Dim i As Long Dim iPos As Long k = Sheets("LookUp").Range("AM1").Value Windows("Kostenbeheerssysteem.xls").Activa...

LanManServer\Parameters DisableDos need a reboot?
Does DisableDos under registry key HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\LanManServer\Parameters need Windows to restart for changes to this to take effect? I'm following the below steps and would rather not reboot unless necessary. 1. Click Start, click Run, type regedit in the Open box, and then click OK. 2. Locate and then click the following registry subkey: HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\LanManServer\Parameters 3. On the Edit menu, point to New, and then click DWORD Value. 4. Type DisableDos for the name of the DWORD value, and then...

Pass form parameters to query expression
Hi All - Given: 1. Stand-alone Access 2003 mdb database. 2. Custom dialog form with two textboxes for user input (latitude & longitude). 3. btnOK on the form sets Me.Visible = False, but does not close the form. 4. A stored Select query based on an underlying table. 5. A controlling VBA procedure that opens the form, waits until btnOK is pressed, then continues executing. Question: Can the form variables ‘latitude’ and ‘longitude’ be passed from the open form (or from the controlling VBA procedure) to an expression in a query field of the stored query without using an SQL statement? ...

Insufficient system resources exist to complete the requested serv
Hi all, We have an application which takes backup to NAS storage device. We have 200GB data in XP machine and this data has to be completely taken backup to NAS storage device. While taking the backup windows error is occuring saying "Insufficient system resources exist to complete the requested service". Details: Database has 1.....100000 records, based on this records we have to backup. So, Accessing DB for 100000 times and copying files about 200GB Programming language C#. Can any one help. Thanks Goutham On Apr 22, ...

Help Please: Transferspreadsheet brings in odd format
I'm using Ac 2003, Win xp pro, Excel 2003. My sheet is formatted as text. Example: data is 3249388005 in Excel but imports as 3.24939e+009 in Access. I've formatted the target table to which the data is appended as Text. As both the Excel cells and the Access field is formatted as text, I don't know how to correct this. It appears this is a scientific notation type data format problem. Please help: how to I preserve the Excel format? Perico <Perico@discussions.microsoft.com> wrote: >I'm using Ac 2003, Win xp pro, Excel 2003. My sheet is formatted as text....

How to make a USB control request in a timer callback...
Hi, I'm trying to make the following in a timer callback: VOID DpxMttCalibrationStateEvtTimerFunction( IN WDFTIMER Timer ) { NTSTATUS status = STATUS_SUCCESS; PDEVICE_EXTENSION devContext = GetDeviceContext(WdfTimerGetParentObject(Timer)); UCHAR CalibrationState; status = DpxMttGetCalibrationState(devContext,&CalibrationState); if (status==STATUS_SUCCESS) { if (devContext->Context.State.Calibrated==0) { if (CalibrationState==1) devContext->Context.State.Calibrated = 1; else devContext->Context.State.Calibrated...

Odd error dialog
I have an Access app that uses Excel to format up and send short e- mails. This process is fraught with peril, because if Excel puts up a dialog box you're pretty much rebooting. I've managed to find most of the problem cases that would do this and eliminate them over time. However, on ONE USERS machine I'm getting an "error" I have never seen before. On MailEnvelope.Item.Send a dialog appears saying something like (sorry, no screen shot) "This document contains hidden columns that the recipient may be able to view. Send anyway?" There are no hidden columns. In...

This delegate takes ParameterizedThreadStart takes an object but can be called wirhout parameter
Hi! The delegate ParameterizedThreadStart is declared as taking one parameter of type object and returning void like this public delegate void ParameterizedThreadStart(Object obj) So according to the delegate the method signature should be void ParameterizedThreadStart(Object obj) In this example when I use the method ThreadWorkWithParam which is based on the delegete ParameterizedThreadStart and look like this private static void ThreadWorkWithParam(object param) { .. . . } I call the method ThreadWorkWithParam without passing a argument I just call is like this t1.Start...

Where to check pending requests for Exch2003?
Hi there, EXCH2003 server with sp1 running on W2K3 server(no sp1) with FE & BE config. All clients with OL2003SP1 in caching mode. Occasionally some users will have forever waiting for the send/receive to complete. To fix the issue I've to kill outlook.exe & restart OL2003. Wondering where can I check the pending requests that stucked for that particular user on both server & client sides? Many thanks! Have any error message? check this kb first http://support.microsoft.com/kb/913112/en-us -- Jammyù�ٴ� "Leemutpo" <leemutpo@hotmail.com> ���g��l...