Date range in crosstab query

  • Thread starter Thread starter ew
  • Start date Start date
E

ew

Is there a way I can use the "between [] and []" for a
date range in a crosstab query? I need data from a
crosstab to feed another query that is driven by a date
range entered. The only way I've been able to get it to
work is to enter the date range into the criteria field.
Thanks,
e
 
The crosstab probably does need to apply the dates before it generates its
output, so the dates must be available at the lower level. If you wish to
reuse the same dates again on a higher level query or on the report itself,
perhaps you could create a form with a pair of text boxes. You can then
refer to them from any and all levels.

The Criteria in your Crosstab would be something like this:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]
Set the Format property of these unbound text boxes to Short Date so Access
knows what kind of data you intend.
 
still no luck. This is probably a rookie error...I get an
error message that says "The Microsoft Jet Database engine
does not recognize [forms].[daterange].[startdate] as a
valid field name or expression". I built a form with two
unbound boxes to enter dates and a button to execute the
query. Any suggestions?
thanks,
e
-----Original Message-----
The crosstab probably does need to apply the dates before it generates its
output, so the dates must be available at the lower level. If you wish to
reuse the same dates again on a higher level query or on the report itself,
perhaps you could create a form with a pair of text boxes. You can then
refer to them from any and all levels.

The Criteria in your Crosstab would be something like this:
Between [Forms].[Form1].[StartDate] And [Forms]. [Form1].[EndDate]
Set the Format property of these unbound text boxes to Short Date so Access
knows what kind of data you intend.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Is there a way I can use the "between [] and []" for a
date range in a crosstab query? I need data from a
crosstab to feed another query that is driven by a date
range entered. The only way I've been able to get it to
work is to enter the date range into the criteria field.
Thanks,
e
Click to expand...


.
Click to expand...
 
Pardon me for jumping in, but

First thing, with a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2
Repeat for each parameters

In addition, try using ! as the separators instead of .
[forms]![daterange]![startdate]

still no luck. This is probably a rookie error...I get an
error message that says "The Microsoft Jet Database engine
does not recognize [forms].[daterange].[startdate] as a
valid field name or expression". I built a form with two
unbound boxes to enter dates and a button to execute the
query. Any suggestions?
thanks,
e
-----Original Message-----
The crosstab probably does need to apply the dates before it generates its
output, so the dates must be available at the lower level. If you wish to
reuse the same dates again on a higher level query or on the report itself,
perhaps you could create a form with a pair of text boxes. You can then
refer to them from any and all levels.

The Criteria in your Crosstab would be something like this:
Between [Forms].[Form1].[StartDate] And [Forms]. [Form1].[EndDate]
Set the Format property of these unbound text boxes to Short Date so Access
knows what kind of data you intend.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Is there a way I can use the "between [] and []" for a
date range in a crosstab query? I need data from a
crosstab to feed another query that is driven by a date
range entered. The only way I've been able to get it to
work is to enter the date range into the criteria field.
Thanks,
e
Click to expand...


.
Click to expand...
Click to expand...
 
Hi John,

Sorry now I am going to jump in if I can. I am wondering what names you
fill in in the Parameters, is this the text boxes on the form. I have a
query I can not get to work also, just trying to get clues from postings
have not got responses to my post.

Thanks,

Jeff

John Spencer (MVP) said:
Pardon me for jumping in, but

First thing, with a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2
Repeat for each parameters

In addition, try using ! as the separators instead of .
[forms]![daterange]![startdate]

still no luck. This is probably a rookie error...I get an
error message that says "The Microsoft Jet Database engine
does not recognize [forms].[daterange].[startdate] as a
valid field name or expression". I built a form with two
unbound boxes to enter dates and a button to execute the
query. Any suggestions?
thanks,
e
-----Original Message-----
The crosstab probably does need to apply the dates before it generates its
output, so the dates must be available at the lower level. If you wish to
reuse the same dates again on a higher level query or on the report itself,
perhaps you could create a form with a pair of text boxes. You can then
refer to them from any and all levels.

The Criteria in your Crosstab would be something like this:
Between [Forms].[Form1].[StartDate] And [Forms]. [Form1].[EndDate]
Set the Format property of these unbound text boxes to Short Date so Access
knows what kind of data you intend.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Is there a way I can use the "between [] and []" for a
date range in a crosstab query? I need data from a
crosstab to feed another query that is driven by a date
range entered. The only way I've been able to get it to
work is to enter the date range into the criteria field.
Thanks,
e


.
Click to expand...
Click to expand...
Click to expand...
 
Jeff said:
Hi John,

Sorry now I am going to jump in if I can. I am wondering what names
you fill in in the Parameters, is this the text boxes on the form. I
have a query I can not get to work also, just trying to get clues
from postings have not got responses to my post.
Click to expand...

In the parameters dialog, you would put a reference to the text boxes on
your form, yes. For example
[Forms]![frmParamForm]![txtStartDate]
and under type choose Date/Time
Enter a second parameter for your End date
[Forms]![frmParamForm]![txtEndDate]
and under type choose Date/Time

Substitute the name of your paramater form for frmParamForm, and the names
of the textboxes for txtStartDate and txtEndDate in the above.

If you still aren't clear, please post back with the name of the form and
the textboxes, and also the SQL statement for your query (view, SQL View).
 
Thanks for the info, i will look at that.

Jeff

Joan Wild said:
Jeff said:
Hi John,

Sorry now I am going to jump in if I can. I am wondering what names
you fill in in the Parameters, is this the text boxes on the form. I
have a query I can not get to work also, just trying to get clues
from postings have not got responses to my post.
Click to expand...

In the parameters dialog, you would put a reference to the text boxes on
your form, yes. For example
[Forms]![frmParamForm]![txtStartDate]
and under type choose Date/Time
Enter a second parameter for your End date
[Forms]![frmParamForm]![txtEndDate]
and under type choose Date/Time

Substitute the name of your paramater form for frmParamForm, and the names
of the textboxes for txtStartDate and txtEndDate in the above.

If you still aren't clear, please post back with the name of the form and
the textboxes, and also the SQL statement for your query (view, SQL View).
Click to expand...
 
Back
Top