Date criteria cause Crosstab report error

[Anna S]

I have a report based on a crosstab query. It works fine
if I enter date range criteria in the query but won't
accept dates entered as parameters or dates looked up on a
form.
This works: Between #1/1/2003# And #31/12/2003#

These don't work:
Between [Forms]![frm_Reports]![StartDate] And [Forms]!
[frm_Reports]![EndDate]

Between [Enter the start date] And [Enter the end date]

The Error: Microsoft Jet Database does not recognise
[Enter the start date] as a valid field name or expression.
I only get this problem with crosstab reports.

Does anyone know why?
Thanks
Anna

 

[Allen Browne]

Hi Anna

Have just tried a crosstab query, and it works fine with:
Between [Forms]![frm_Reports]![StartDate] And
[Forms]![frm_Reports]![EndDate]

What could be wrong:
- Double-check the spelling of the form and text box names.

- The text boxes are probably unbound, so Access may not have understood
them as dates. Set their Format property to Short Date. Also, declare the
parameters in the crosstab query: choose Parameters on Query menu, and enter
2 rows in the dialog:
[Forms]![frm_Reports]![StartDate] Date/Time
[Forms]![frm_Reports]![EndDate] Date/Time

- Is the crosstab based on another query rather than on tables? Do these
parameters need to be in the lower query?

- You will not be able to use these references in the WhereCondition of the
OpenReport action, as that is too late.

 

[Anna S]

Thanks again Allen

I have used your suggestions and still the report won't
generate. The crosstab query itself works fine, but the
report based on the query is still giving me an error.
Error this time is: Microsoft Jet Database does not
recognise " as a valid field name or expression.

Also the report is based on a Crosstab query which in turn
is based on another query. I have tried putting the date
range criteria in both places, (not at the same time) and
declaring the parameters as you suggested.

In your previous reply, were you suggesting that the date
range criteria would be best placed in the qrosstab query
or its underlying query?

The report opens with:
stDocName = "rpt_PatientsServiceTime"
DoCmd.OpenReport stDocName, acPreview


It puzzles me that the queries will work but not the
report. Do you have any further ideas.

Thank you in advance
Anna

-----Original Message-----
Hi Anna

Have just tried a crosstab query, and it works fine with:
Between [Forms]![frm_Reports]![StartDate] And
[Forms]![frm_Reports]![EndDate]

What could be wrong:
- Double-check the spelling of the form and text box names.

- The text boxes are probably unbound, so Access may not have understood
them as dates. Set their Format property to Short Date. Also, declare the
parameters in the crosstab query: choose Parameters on Query menu, and enter
2 rows in the dialog:
[Forms]![frm_Reports]![StartDate] Date/Time
[Forms]![frm_Reports]![EndDate] Date/Time

- Is the crosstab based on another query rather than on tables? Do these
parameters need to be in the lower query?

- You will not be able to use these references in the WhereCondition of the
OpenReport action, as that is too late.

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

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

I have a report based on a crosstab query. It works fine
if I enter date range criteria in the query but won't
accept dates entered as parameters or dates looked up on a
form.
This works: Between #1/1/2003# And #31/12/2003#

These don't work:
Between [Forms]![frm_Reports]![StartDate] And [Forms]!
[frm_Reports]![EndDate]

Between [Enter the start date] And [Enter the end date]

The Error: Microsoft Jet Database does not recognise
[Enter the start date] as a valid field name or expression.
I only get this problem with crosstab reports.

Does anyone know why?
Thanks
Anna

.

 

[Allen Browne]

Okay, so the crosstab query itself runs, so the fault is with the report.

In A2003, I have a crosstab that has a date field that picks up criteria
from the form. A report based on that query works fine, so we need to see
what the differences are.

Is there anything in the report that could be causing the problem?
In the control source of any text box?
In the Sorting And Grouping dialog (View menu)?
In its Filter property?
In any of its events?

Could Name AutoCorrect be an issue here?
Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Then compact the database:
Tools | Database Utilities | Compact

If the problem is still here after that, and you can't find any cause for
the probem, try decompiling a copy of the database by entering something
like this at the command prompt while Access is not running. It is all one
line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"
Then open a code window and see if it compiles again (Compile on Debug
menu).

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

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

Thanks again Allen

I have used your suggestions and still the report won't
generate. The crosstab query itself works fine, but the
report based on the query is still giving me an error.
Error this time is: Microsoft Jet Database does not
recognise " as a valid field name or expression.

Also the report is based on a Crosstab query which in turn
is based on another query. I have tried putting the date
range criteria in both places, (not at the same time) and
declaring the parameters as you suggested.

In your previous reply, were you suggesting that the date
range criteria would be best placed in the qrosstab query
or its underlying query?

The report opens with:
stDocName = "rpt_PatientsServiceTime"
DoCmd.OpenReport stDocName, acPreview


It puzzles me that the queries will work but not the
report. Do you have any further ideas.

Thank you in advance
Anna

-----Original Message-----
Hi Anna

Have just tried a crosstab query, and it works fine with:
Between [Forms]![frm_Reports]![StartDate] And
[Forms]![frm_Reports]![EndDate]

What could be wrong:
- Double-check the spelling of the form and text box names.

- The text boxes are probably unbound, so Access may not have understood
them as dates. Set their Format property to Short Date. Also, declare the
parameters in the crosstab query: choose Parameters on Query menu, and enter
2 rows in the dialog:
[Forms]![frm_Reports]![StartDate] Date/Time
[Forms]![frm_Reports]![EndDate] Date/Time

- Is the crosstab based on another query rather than on tables? Do these
parameters need to be in the lower query?

- You will not be able to use these references in the WhereCondition of the
OpenReport action, as that is too late.

I have a report based on a crosstab query. It works fine
if I enter date range criteria in the query but won't
accept dates entered as parameters or dates looked up on a
form.
This works: Between #1/1/2003# And #31/12/2003#

These don't work:
Between [Forms]![frm_Reports]![StartDate] And [Forms]!
[frm_Reports]![EndDate]

Between [Enter the start date] And [Enter the end date]

The Error: Microsoft Jet Database does not recognise
[Enter the start date] as a valid field name or expression.
I only get this problem with crosstab reports.

Does anyone know why?
Thanks
Anna

 

[Anna S]

Thanks Allen

Recompile and compact worked! There were no issues with
Sorting, Events, Filters or Auto Correct.

I appreciate your help.

Anna

-----Original Message-----
Okay, so the crosstab query itself runs, so the fault is with the report.

In A2003, I have a crosstab that has a date field that picks up criteria
from the form. A report based on that query works fine, so we need to see
what the differences are.

Is there anything in the report that could be causing the problem?
In the control source of any text box?
In the Sorting And Grouping dialog (View menu)?
In its Filter property?
In any of its events?

Could Name AutoCorrect be an issue here?
Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Then compact the database:
Tools | Database Utilities | Compact

If the problem is still here after that, and you can't find any cause for
the probem, try decompiling a copy of the database by entering something
like this at the command prompt while Access is not running. It is all one
line, and include the quotes:
"c:\Program Files\Microsoft

office\office\msaccess.exe" /decompile

"c:\MyPath\MyDatabase.mdb"
Then open a code window and see if it compiles again (Compile on Debug
menu).

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

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

Thanks again Allen

I have used your suggestions and still the report won't
generate. The crosstab query itself works fine, but the
report based on the query is still giving me an error.
Error this time is: Microsoft Jet Database does not
recognise " as a valid field name or expression.

Also the report is based on a Crosstab query which in turn
is based on another query. I have tried putting the date
range criteria in both places, (not at the same time) and
declaring the parameters as you suggested.

In your previous reply, were you suggesting that the date
range criteria would be best placed in the qrosstab query
or its underlying query?

The report opens with:
stDocName = "rpt_PatientsServiceTime"
DoCmd.OpenReport stDocName, acPreview


It puzzles me that the queries will work but not the
report. Do you have any further ideas.

Thank you in advance
Anna

-----Original Message-----
Hi Anna

Have just tried a crosstab query, and it works fine with:
Between [Forms]![frm_Reports]![StartDate] And
[Forms]![frm_Reports]![EndDate]

What could be wrong:
- Double-check the spelling of the form and text box names.

- The text boxes are probably unbound, so Access may not have understood
them as dates. Set their Format property to Short Date. Also, declare the
parameters in the crosstab query: choose Parameters on Query menu, and enter
2 rows in the dialog:
[Forms]![frm_Reports]![StartDate] Date/Time
[Forms]![frm_Reports]![EndDate] Date/Time

- Is the crosstab based on another query rather than on tables? Do these
parameters need to be in the lower query?

- You will not be able to use these references in the WhereCondition of the
OpenReport action, as that is too late.


I have a report based on a crosstab query. It works fine
if I enter date range criteria in the query but won't
accept dates entered as parameters or dates looked up on a
form.
This works: Between #1/1/2003# And #31/12/2003#

These don't work:
Between [Forms]![frm_Reports]![StartDate] And [Forms]!
[frm_Reports]![EndDate]

Between [Enter the start date] And [Enter the end date]

The Error: Microsoft Jet Database does not recognise
[Enter the start date] as a valid field name or expression.
I only get this problem with crosstab reports.

Does anyone know why?
Thanks
Anna

.