If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Want a Parameter for date range driving a cross tab query
Hi,
I have a form with two text boxes called txtStartDate and txtEndDate. I have a query called qryCuffsShipped1 with a date field called DateShipped. In the criteria area in design view for this field I have Between [forms]![frmCuffsShippedMain]![txtStartDate] And [forms]![frmCuffsShippedMain]![txtEndDate] The query runs and the date restrictor works. I created a cross tab query based on this query with a button on the form which runs it. My DateShipped field is the Column heading in the cross tab. When I run the cross tab without anything in the criteria statement of qryCuffsShipped1 it works. However, when I put the above parameter in the DateShipped field of qryCuffsShipped1 I get an error that says "The Microsoft Jet Database engine does not recognize [forms]![frmCuffsShippedMain]![txtStartDate] as a valid field name or expression. I want to be able to choose the dates that run a cross tab but am having trouble doing so. Can someone help? Thanks, -- Chuck W |
#2
|
|||
|
|||
Want a Parameter for date range driving a cross tab query
On Thu, 29 Oct 2009 19:17:01 -0700, ChuckW wrote:
Hi, I have a form with two text boxes called txtStartDate and txtEndDate. I have a query called qryCuffsShipped1 with a date field called DateShipped. In the criteria area in design view for this field I have Between [forms]![frmCuffsShippedMain]![txtStartDate] And [forms]![frmCuffsShippedMain]![txtEndDate] The query runs and the date restrictor works. I created a cross tab query based on this query with a button on the form which runs it. My DateShipped field is the Column heading in the cross tab. When I run the cross tab without anything in the criteria statement of qryCuffsShipped1 it works. However, when I put the above parameter in the DateShipped field of qryCuffsShipped1 I get an error that says "The Microsoft Jet Database engine does not recognize [forms]![frmCuffsShippedMain]![txtStartDate] as a valid field name or expression. I want to be able to choose the dates that run a cross tab but am having trouble doing so. Can someone help? Thanks, It's always permissible to explicitly define a query's Parameters. Oddly, for a Crosstab query, it's obligatory! Open the Query in SQL view and put PARAMETERS [forms]![frmCuffsShippedMain]![txtStartDate] DateTime, [forms]![frmCuffsShippedMain]![txtEndDate] DateTime; at the very top of the query, before the SELECT statement; or, equivalently, right click the background of the tables window in query design view, select Parameters, and put the parameters (exactly, they must match byte for byte) in the left column and choose Date/Time from the dropdown in the right. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Want a Parameter for date range driving a cross tab query
Thank you very much, John. This was just the information I came looking for. I would like to add for others who need this information that I had to remove extra [] via SQL view after I added the parameter via the design view (kept getting an error about exta []). Cheers, Birgit Open the Query in SQL view and put PARAMETERS [forms]![frmCuffsShippedMain]![txtStartDate] DateTime, [forms]![frmCuffsShippedMain]![txtEndDate] DateTime; at the very top of the query, before the SELECT statement; or, equivalently, right click the background of the tables window in query design view, select Parameters, and put the parameters (exactly, they must match byte for byte) in the left column and choose Date/Time from the dropdown in the right. |
Thread Tools | |
Display Modes | |
|
|