RANDALL R ANTHONY
RRANTHON at sentara.com
Fri Sep 11 09:38:23 CDT 2009
Ok, this might be getting us somewhere. I added the column Region to the grid as a Where and placed the [forms]![summ_rpt_shm_frm]![cbosvcregion] in the criteria and got a result. However, if I pass null, I get an error (null = all). (Lambert, I tried the below previously and it kept prompting me at runtime) >>> On 9/11/2009 at 9:55 AM, in message <B4F1446167F0BD4FAD640A79824EE0B00E1A51F8D4 at LIVP3MMBX11.mail.aig.net>, "Heenan, Lambert" <Lambert.Heenan at chartisinsurance.com> wrote: I'm assuming that you either put a criteria in the query grid, such as a prompt to enter a value... [Enter Criteria] ... or else that you have a criteria that references some control on a form ... [Forms]![SomeForm]![SomeTextBox] ... In which case I think you will find that the problem is in the crosstab query. Whenever you need to have a criteria in a crosstab you also must define a parameter for it. To do that open the x-tab in design mode and select 'Parameters' on the 'Query' menu. To quote from the on-line help... On the Query menu, click Parameters. In the first Parameter cell, type the first prompt you entered in the query design grid. In the Data Type cell to the right, click the appropriate data type according to the following guidelines: If the parameter field data type is Click this data type in the Data Type cell Currency, Date/Time, Memo, OLE Object, Text, and Yes/No Currency, Date/Time, Memo, OLE Object, Text, and Yes/No Number Byte, Single, Double, Integer, Long Integer, and Replication ID. These data types correspond to the five FieldSize property settings of the Number data type. Unknown Value. This is a generic data type that accepts any type of data. Binary Binary. Used with parameter queries directed to linked tables that do recognize this data type (Microsoft Access does not recognize this data type). Repeat Steps 2 and 3 for each parameter you want to specify a data type for. HTH Lambert -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of RANDALL R ANTHONY Sent: Friday, September 11, 2009 9:38 AM To: accessd at databaseadvisors.com Subject: [AccessD] Crosstab Query - Additional Parameter Hi y'all, I've got a crosstab query that works great. However, I need to pass an additional parameter (that may or may not be null) and thought I could just add them to the query grid. This does not work. I'm assuming that it has something to do with not being part of the columns displayed. What do I need to do? DoCmd.OpenReport sObjName, acViewPreview, , sWhere, , strSvcRegion & "~" & strLocality Is the command, sWhere = "" or 2 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com ( http://www.databaseadvisors.com/ ) -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com ( http://www.databaseadvisors.com/ )