Why this error now?

[Guest]

Let's say we have a table in which one of the fields is Date. Now let's say
we want to create a simple report from that table, but the user has to pick
the date interval. So, the report will have to display records only from X
date to Y date. We create then a form called DateInput with 2 fields for the
user to input the X date and the Y date to create the interval. Then we make
a query from that table, and on the criteria of Date, we put the following
expression:

Between [Forms]![DateInput]![DateX] And [Forms]![DateInput]![DateY]

Well, that's what I've always done when I want to create a report based on a
date interval input by the user and it always worked.

But now I've got a problem. Instead of making a query, I made a crosstab
query from the table, to make the report easier to read. And again, on the
date criteria I put the same expression, but it won't work on the crosstab
query. I always get this error message:

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

Why does this expression work on a regular query but not on a crosstab query?

Also, any ideas on how to create this report on the date interval provided
by the user and using the crosstab query?

 

[Guest]

Is the form DateInput open? Does DateX and DateY have valid dates?

 

[Guest]

Is the form DateInput open? Does DateX and DateY have valid dates?

Yes and yes.

 

[Guest]

I missed something - the error has [Forms]![Report]![DateX] and you said
[Forms]![DateInput]![DateX] And [Forms]![DateInput]![DateY].

[Forms]![Report]![DateX] is not the same as [Forms]![DateInput]![DateX]

Where is it getting [Forms]![Report]![DateX] ? If you do not know you can
use the Analyze-Documentor.

 

[Guest]

No, that was just a typo. The error message indeed says
[Forms]![DateInput]![DateX]. I typed it like that because previously I named
the form "Report" but that was causing confusion, so I renamed it to
"DateInput". Sorry about that

I missed something - the error has [Forms]![Report]![DateX] and you said
[Forms]![DateInput]![DateX] And [Forms]![DateInput]![DateY].

[Forms]![Report]![DateX] is not the same as [Forms]![DateInput]![DateX]

Where is it getting [Forms]![Report]![DateX] ? If you do not know you can
use the Analyze-Documentor.

Yes and yes.

 

[Guest]

Hi Daniel,

Please see my article:
How To Create A Crosstab Query
http://www.access.qbuilt.com/html/crosstab_queries.html

Pay particular attention to paragraph 15.


Tom
_______________________________________

:

Let's say we have a table in which one of the fields is Date. Now let's say
we want to create a simple report from that table, but the user has to pick
the date interval. So, the report will have to display records only from X
date to Y date. We create then a form called DateInput with 2 fields for the
user to input the X date and the Y date to create the interval. Then we make
a query from that table, and on the criteria of Date, we put the following
expression:

Between [Forms]![DateInput]![DateX] And [Forms]![DateInput]![DateY]

Well, that's what I've always done when I want to create a report based on a
date interval input by the user and it always worked.

But now I've got a problem. Instead of making a query, I made a crosstab
query from the table, to make the report easier to read. And again, on the
date criteria I put the same expression, but it won't work on the crosstab
query. I always get this error message:

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

Why does this expression work on a regular query but not on a crosstab query?

Also, any ideas on how to create this report on the date interval provided
by the user and using the crosstab query?

 

[Guest]

Try putting the criteria in a select query and then using the select query as
the source for the crosstab.

No, that was just a typo. The error message indeed says
[Forms]![DateInput]![DateX]. I typed it like that because previously I named
the form "Report" but that was causing confusion, so I renamed it to
"DateInput". Sorry about that

I missed something - the error has [Forms]![Report]![DateX] and you said
[Forms]![DateInput]![DateX] And [Forms]![DateInput]![DateY].

[Forms]![Report]![DateX] is not the same as [Forms]![DateInput]![DateX]

Where is it getting [Forms]![Report]![DateX] ? If you do not know you can
use the Analyze-Documentor.

:

Is the form DateInput open? Does DateX and DateY have valid dates?

Yes and yes.

 

[Guest]

Hi Tom,

Thanks for your reply, it really solved my crosstab problem and I really
appreciate it. But now I ran into another problem I wonder if you could help
me with it. So, I have this table with records from employees time cards. The
table has the following fields: Id, Employee Name, Date, Time In, Time Out. I
created the crosstab query from it showing the Dates on the Horizontal and
the employee names on the vertical, and for each employee I can get the total
hours they worked for each day. And I also put the criteria on the date to
enable the user to pick the date interval, specifying the parameters.

But now, when I try to create a report with the report wizard from this
crosstab, there are no fields to select so I'm unable to create this report.
How can I create a report from it?

Thanks in advance

 

[Guest]

Hi Daniel,

Basing reports on crosstab query results is always problematic, since the
number of columns returned by a crosstab query can be variable. Here is one
method as implemented by Access MVP Duane Hookom:

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Cross Tab'

I haven't used Duane's method, but I assume it works fine. His report will
accomodate any number of columns with extra columns "wrapping" under the
first set of columns.

I have a sample database that I worked up a few years ago, that can display
the results of a crosstab, however, my sample does not wrap extra columns. It
is based on the Northwind sample database, and shows a summary of the sales
totals for each employee for a maximum period of one year. This results in 14
columns in the crosstab as the maximum (Empname, Jan-Dec columns, and a row
total column). I am willing to share this sample with you, if you send me a
private e-mail message with a valid reply address.

Since my earlier answer has helped you, please consider answering "Yes" to
the question at the bottom that reads "Did this post answer the question?"

Tom

QWaos168@XScom cast. Dnet (<--Remove all capitalized letters and spaces).
_______________________________________

:

Hi Tom,

Thanks for your reply, it really solved my crosstab problem and I really
appreciate it. But now I ran into another problem I wonder if you could help
me with it. So, I have this table with records from employees time cards. The
table has the following fields: Id, Employee Name, Date, Time In, Time Out. I
created the crosstab query from it showing the Dates on the Horizontal and
the employee names on the vertical, and for each employee I can get the total
hours they worked for each day. And I also put the criteria on the date to
enable the user to pick the date interval, specifying the parameters.

But now, when I try to create a report with the report wizard from this
crosstab, there are no fields to select so I'm unable to create this report.
How can I create a report from it?

Thanks in advance
_______________________________________

:

Hi Daniel,

Please see my article:
How To Create A Crosstab Query
http://www.access.qbuilt.com/html/crosstab_queries.html

Pay particular attention to paragraph 15.


Tom
_______________________________________

:

Let's say we have a table in which one of the fields is Date. Now let's say
we want to create a simple report from that table, but the user has to pick
the date interval. So, the report will have to display records only from X
date to Y date. We create then a form called DateInput with 2 fields for the
user to input the X date and the Y date to create the interval. Then we make
a query from that table, and on the criteria of Date, we put the following
expression:

Between [Forms]![DateInput]![DateX] And [Forms]![DateInput]![DateY]

Well, that's what I've always done when I want to create a report based on a
date interval input by the user and it always worked.

But now I've got a problem. Instead of making a query, I made a crosstab
query from the table, to make the report easier to read. And again, on the
date criteria I put the same expression, but it won't work on the crosstab
query. I always get this error message:

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

Why does this expression work on a regular query but not on a crosstab query?

Also, any ideas on how to create this report on the date interval provided
by the user and using the crosstab query?