A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Want a Parameter for date range driving a cross tab query



 
 
Thread Tools Display Modes
  #1  
Old October 30th, 2009, 03:17 AM posted to microsoft.public.access.forms
ChuckW
external usenet poster
 
Posts: 182
Default 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  
Old October 30th, 2009, 04:45 AM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old November 10th, 2009, 01:53 PM posted to microsoft.public.access.forms
Birgit A
external usenet poster
 
Posts: 1
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.