Date Range Criteria for Crosstab Query

[Guest]

I have a cross tab query that I am using as a base for a report. I've
created a form with a BeginningDate text box and an EndingDate text box that
I want to use as user inputs for a date range for the report. When I run the
query I get the following error:

The Microsoft Jet database engine does not recognize '[Forms]![Report
Creator]![BeginningDate]' as a valid field name or expression.

I've made the Date part of the crosstab query grouped by "Where" and use the
following expression to prompt the Report Creator form.

Between [Forms]![Report Creator]![BeginningDate] And [Forms]![Report
Creator]![Ending Date] Or [Forms]![Report Creator]![BeginningDate] Is Null

Why am I getting an error and what can I do to alow the date range to be
input?

 

[Allen Browne]

You can use parametersin a crosstab query if you explicitly declare them.

In query design view, choose Parameters on the Query menu.
Enter two rows in the dialog:
[Forms]![Report Creator]![BeginningDate] Date/Time
[Forms]![Report Creator]![Ending Date] Date/Time

 

[Guest]

Thanks, that worked with allowing me to input a date range. Now it won't let
me generate a report. I initially had date as a row heading so I stopped
having it as a row heading. Now when I go to create a report no fields show
up in the report wizard when it prompts for which fields you'd like reported.
What happened and how can I make a report?

You can use parametersin a crosstab query if you explicitly declare them.

In query design view, choose Parameters on the Query menu.
Enter two rows in the dialog:
[Forms]![Report Creator]![BeginningDate] Date/Time
[Forms]![Report Creator]![Ending Date] Date/Time

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

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

I have a cross tab query that I am using as a base for a report. I've
created a form with a BeginningDate text box and an EndingDate text box
that
I want to use as user inputs for a date range for the report. When I run
the
query I get the following error:

The Microsoft Jet database engine does not recognize '[Forms]![Report
Creator]![BeginningDate]' as a valid field name or expression.

I've made the Date part of the crosstab query grouped by "Where" and use
the
following expression to prompt the Report Creator form.

Between [Forms]![Report Creator]![BeginningDate] And [Forms]![Report
Creator]![Ending Date] Or [Forms]![Report Creator]![BeginningDate] Is Null

Why am I getting an error and what can I do to alow the date range to be
input?

 

[Allen Browne]

Because the column names come from the data, Access must run the crosstab
query to completion before it knows what field names it will have for the
report.

This creates problems for designing the report:
a) It can take quite a while before the fields show up in report design.
b) There is no guarantee that those field names will be used again next
time, so the report may end up with #Name errors.

To avoid these problems, declare all the possible field names in the query
if possible. To do that, open the crosstab query in design view, open the
Properties box, and a list of separated values beside the Column Headings
property.

If the column names will be constantly changing, you cannot create a report
that just uses those constantly changing field names. You either have to
alias the fields in the query somehow, or save the report with generically
named unbound controls and use the Open event of the report to
programmatically assign their Control Source, Left, and Visible properties.

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

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

Thanks, that worked with allowing me to input a date range. Now it won't
let
me generate a report. I initially had date as a row heading so I stopped
having it as a row heading. Now when I go to create a report no fields
show
up in the report wizard when it prompts for which fields you'd like
reported.
What happened and how can I make a report?

You can use parametersin a crosstab query if you explicitly declare them.

In query design view, choose Parameters on the Query menu.
Enter two rows in the dialog:
[Forms]![Report Creator]![BeginningDate] Date/Time
[Forms]![Report Creator]![Ending Date] Date/Time

I have a cross tab query that I am using as a base for a report. I've
created a form with a BeginningDate text box and an EndingDate text box
that
I want to use as user inputs for a date range for the report. When I
run
the
query I get the following error:

The Microsoft Jet database engine does not recognize '[Forms]![Report
Creator]![BeginningDate]' as a valid field name or expression.

I've made the Date part of the crosstab query grouped by "Where" and
use
the
following expression to prompt the Report Creator form.

Between [Forms]![Report Creator]![BeginningDate] And [Forms]![Report
Creator]![Ending Date] Or [Forms]![Report Creator]![BeginningDate] Is
Null

Why am I getting an error and what can I do to alow the date range to
be
input?

 

[Guest]

Once I did that it started saying I had column names that existed in more
than one place, which is an error that had never popped up before. Is there
a better way to get the effect of a crosstab query without the reporting
problems?

Because the column names come from the data, Access must run the crosstab
query to completion before it knows what field names it will have for the
report.

This creates problems for designing the report:
a) It can take quite a while before the fields show up in report design.
b) There is no guarantee that those field names will be used again next
time, so the report may end up with #Name errors.

To avoid these problems, declare all the possible field names in the query
if possible. To do that, open the crosstab query in design view, open the
Properties box, and a list of separated values beside the Column Headings
property.

If the column names will be constantly changing, you cannot create a report
that just uses those constantly changing field names. You either have to
alias the fields in the query somehow, or save the report with generically
named unbound controls and use the Open event of the report to
programmatically assign their Control Source, Left, and Visible properties.

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

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

Thanks, that worked with allowing me to input a date range. Now it won't
let
me generate a report. I initially had date as a row heading so I stopped
having it as a row heading. Now when I go to create a report no fields
show
up in the report wizard when it prompts for which fields you'd like
reported.
What happened and how can I make a report?

You can use parametersin a crosstab query if you explicitly declare them.

In query design view, choose Parameters on the Query menu.
Enter two rows in the dialog:
[Forms]![Report Creator]![BeginningDate] Date/Time
[Forms]![Report Creator]![Ending Date] Date/Time

I have a cross tab query that I am using as a base for a report. I've
created a form with a BeginningDate text box and an EndingDate text box
that
I want to use as user inputs for a date range for the report. When I
run
the
query I get the following error:

The Microsoft Jet database engine does not recognize '[Forms]![Report
Creator]![BeginningDate]' as a valid field name or expression.

I've made the Date part of the crosstab query grouped by "Where" and
use
the
following expression to prompt the Report Creator form.

Between [Forms]![Report Creator]![BeginningDate] And [Forms]![Report
Creator]![Ending Date] Or [Forms]![Report Creator]![BeginningDate] Is
Null

Why am I getting an error and what can I do to alow the date range to
be
input?

 

[Allen Browne]

I don't understand the problem, Mark.
Haven't seen that error message.

Did you type in some names twice perhaps?

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

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

Once I did that it started saying I had column names that existed in more
than one place, which is an error that had never popped up before. Is
there
a better way to get the effect of a crosstab query without the reporting
problems?

Because the column names come from the data, Access must run the crosstab
query to completion before it knows what field names it will have for the
report.

This creates problems for designing the report:
a) It can take quite a while before the fields show up in report design.
b) There is no guarantee that those field names will be used again next
time, so the report may end up with #Name errors.

To avoid these problems, declare all the possible field names in the
query
if possible. To do that, open the crosstab query in design view, open the
Properties box, and a list of separated values beside the Column Headings
property.

If the column names will be constantly changing, you cannot create a
report
that just uses those constantly changing field names. You either have to
alias the fields in the query somehow, or save the report with
generically
named unbound controls and use the Open event of the report to
programmatically assign their Control Source, Left, and Visible
properties.

Thanks, that worked with allowing me to input a date range. Now it
won't
let
me generate a report. I initially had date as a row heading so I
stopped
having it as a row heading. Now when I go to create a report no fields
show
up in the report wizard when it prompts for which fields you'd like
reported.
What happened and how can I make a report?

:

You can use parametersin a crosstab query if you explicitly declare
them.

In query design view, choose Parameters on the Query menu.
Enter two rows in the dialog:
[Forms]![Report Creator]![BeginningDate] Date/Time
[Forms]![Report Creator]![Ending Date] Date/Time

I have a cross tab query that I am using as a base for a report.
I've
created a form with a BeginningDate text box and an EndingDate text
box
that
I want to use as user inputs for a date range for the report. When
I
run
the
query I get the following error:

The Microsoft Jet database engine does not recognize
'[Forms]![Report
Creator]![BeginningDate]' as a valid field name or expression.

I've made the Date part of the crosstab query grouped by "Where" and
use
the
following expression to prompt the Report Creator form.

Between [Forms]![Report Creator]![BeginningDate] And [Forms]![Report
Creator]![Ending Date] Or [Forms]![Report Creator]![BeginningDate]
Is
Null

Why am I getting an error and what can I do to alow the date range
to
be
input?

 

[Guest]

No, it said that the name "Line" could exist in more than one table.

I don't understand the problem, Mark.
Haven't seen that error message.

Did you type in some names twice perhaps?

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

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

Once I did that it started saying I had column names that existed in more
than one place, which is an error that had never popped up before. Is
there
a better way to get the effect of a crosstab query without the reporting
problems?

Because the column names come from the data, Access must run the crosstab
query to completion before it knows what field names it will have for the
report.

This creates problems for designing the report:
a) It can take quite a while before the fields show up in report design.
b) There is no guarantee that those field names will be used again next
time, so the report may end up with #Name errors.

To avoid these problems, declare all the possible field names in the
query
if possible. To do that, open the crosstab query in design view, open the
Properties box, and a list of separated values beside the Column Headings
property.

If the column names will be constantly changing, you cannot create a
report
that just uses those constantly changing field names. You either have to
alias the fields in the query somehow, or save the report with
generically
named unbound controls and use the Open event of the report to
programmatically assign their Control Source, Left, and Visible
properties.

Thanks, that worked with allowing me to input a date range. Now it
won't
let
me generate a report. I initially had date as a row heading so I
stopped
having it as a row heading. Now when I go to create a report no fields
show
up in the report wizard when it prompts for which fields you'd like
reported.
What happened and how can I make a report?

:

You can use parametersin a crosstab query if you explicitly declare
them.

In query design view, choose Parameters on the Query menu.
Enter two rows in the dialog:
[Forms]![Report Creator]![BeginningDate] Date/Time
[Forms]![Report Creator]![Ending Date] Date/Time

I have a cross tab query that I am using as a base for a report.
I've
created a form with a BeginningDate text box and an EndingDate text
box
that
I want to use as user inputs for a date range for the report. When
I
run
the
query I get the following error:

The Microsoft Jet database engine does not recognize
'[Forms]![Report
Creator]![BeginningDate]' as a valid field name or expression.

I've made the Date part of the crosstab query grouped by "Where" and
use
the
following expression to prompt the Report Creator form.

Between [Forms]![Report Creator]![BeginningDate] And [Forms]![Report
Creator]![Ending Date] Or [Forms]![Report Creator]![BeginningDate]
Is
Null

Why am I getting an error and what can I do to alow the date range
to
be
input?