Why this error now?

[MrDowner]

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 Report 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]![Report]![DateX] And [Forms]![Report]![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?

 

[The_Doc_Man]

Possibly because the cross-tab query has hidden the actual name it used?

Look at the SQL of the query to see the field names employed to make the cross-tab. It might surprise you to see that they aren't always what you think they should be.

If that ain't it, then I don't know where else to look.

 

[MrDowner]

Well, I looked at the SQL code for the crosstab query and I couldn't see anything that would be causing that. Here is the code, maybe you could tell if there's anything out of order:

TRANSFORM Sum(TimeSheet.Id) AS SumOfId
SELECT TimeSheet.Employee
FROM TimeSheet
WHERE (((TimeSheet.Date) Between [Forms]![Report]![DateX] And [Forms]![Report]![DateY]))
GROUP BY TimeSheet.Employee
PIVOT TimeSheet.Date;

I just don't understand why it doesn't work.

Is there an alternative way I could enable the user to pick the date interval and make it work with this crosstab query?

Thanks

 

[The_Doc_Man]

After reconsideration, it strikes me that the named report has to be open for this to work. Syntax [Forms]![Report]![DateX] says "Find workspace collection Forms; then searching through that collection, find the report named Report, which includes a collection of controls. (It's the default collection for that syntax.) Search through the collection of controls until you find one named DateX."

What gets me is that the collection Forms is only all OPEN forms. If this Cross-Tab query is also the one driving the report that contains DateX, you may have run into a timing issue as to when the underlying cross-tab query opens vs. when the report opens. I believe that a cross-tab query is similar to a summation query of a query. I have no clue as to why any variant of this would ever work. So I'm assuming that the DateX and DateY fields are set up to input as parameters or something similar, since this is a report.

A cross-tab is different from many other types of query so I cannot be sure, but it is possible that you are running into an "order of evaluation" problem. The underlying cross-tab query apparently has to open BEFORE the report does, so the named controls don't exist (yet). I admit I'm guessing here. You might consider an experiment as an oddbal way of proving / disproving my idea.

Make your report have the DateX and DateY stuff in a Report Header. Doesn't matter what else is there as long as it is nothing from the Cross-Tab query. In fact, this report probably should be unbound.

Also make this report have only one thing in its detail section - a SUB-REPORT built from / bound to the Cross Tab query. And don't open the SUB-REPORT until you open the main report's detail section - Detail_OnFormat event, perhaps? You might have to change the Cross-Tab query reference to the dates to take into account that the report's ReportHeader section is a member of the report's Section collection. So you would need one more qualifier in the sequence that specifies DateX and DateY. But if you can defer opening the SUB-REPORT until the dates are in place, this might work.

If this doesn't do it, I'm sorry to have lead you astray.

 

[MrDowner]

After reconsideration, it strikes me that the named report has to be open for this to work. Syntax [Forms]![Report]![DateX] says "Find workspace collection Forms; then searching through that collection, find the report named Report, which includes a collection of controls. (It's the default collection for that syntax.) Search through the collection of controls until you find one named DateX."

What gets me is that the collection Forms is only all OPEN forms. If this Cross-Tab query is also the one driving the report that contains DateX, you may have run into a timing issue as to when the underlying cross-tab query opens vs. when the report opens.

Well I think you might be misunderstanding somethings here. The "Report" here is not actually the report itself but just the name of the form which has the fields "DateX" and "DateY" for the user to input the date interval. I guess I should have named it something different to avoid confusion.

You said it could be a timing issue if the crosstab query is also the one driving the report that contains DateX, but I don't think that is possible because the fields "DateX" and "DateY" are unbound, and they only exist during the time the form "Report" is open.

So basically what I've always done is creating this form with the "DateX" and "DateY" fields and a button to open the report. Created a query based on the table that I wanted my data to be filtered, added that expression to the date criteria. Then create a report, based on the query. The only way the user can trigger the report is pushing the button on the form, after he input the date interval. So all the user sees is the form window with the two date fields and a button to preview the report. What I'm trying to point out here is that the report and the query are only triggered AFTER the form with the dates is open, so "DateX" and "DateY" indeed exist. Like I said, it works fine when you're working with a regular query, but not with a cross-tab query, for reasons unknown to me.

 

[The_Doc_Man]

because the fields "DateX" and "DateY" are unbound, and they only exist during the time the form "Report" is open.

Yeah, using "REPORT" as a form name is a good way to throw people off. Including Access, since REPORT is a reserved word.

What I'm trying to point out here is that the report and the query are only triggered AFTER the form with the dates is open

I'll make a bizarre suggestion for you. Make a query that uses the syntax that works and that has the WHERE clause with the [DateX] and [DateY] references. Then base the Cross-Tab off the query. Hide the dates from the actual cross-tab and just let the dates be ordinary data (rather than WHERE fields).

 

[MrDowner]

Already tried that, still doesn't work.

By the way, I changed the form "Report"'s name to "form_dateinput", but it still wouldn't work.

I just give up, I guess I'll have to deal with the confusing report that the regular query provides.

I tried to see if I can get support from Microsoft, but they wanna charge me $99 for it... I don't think so.

 

[Pat Hartman]

The problem is with the crosstab query processor. Apparently it was written by a different programmer who wasn't there the day the team decided that parameters didn't need to be specifically defined.

To fix the problem, open the crosstab in design view. RightClick on the background and select the parameters peroperty. Specifically define the parameters. If the parameters are specified in a subquery, modify the subquery's parameters property.

 

[MrDowner]

Ok, but how exactly should I define these parameters?

 

[MrDowner]

Nevermind, I figured it out, and yes it works now!

Finally, this was driving me insane. Thanks a lot Pat Hartman and Doc_Man.

 

[MrDowner]

Well, I was glad that I made this cross-tab query finally work the way I wanted but now there's another problem...

The whole point of creating this cross-tab was to create a report out of it that would be easier for me to read, but now, when I try to create a report using the wizard based on the cross-tab, there are no fields to select!

I guess I'll need some programming going on for this report to work, but I'm no programmer... Any of you have any ideas?

 

[nicola1]

I don't know if im way off the mark here, as im only a novice user myself, i think i had to create a similar report. I had a table with the field named date, the table was recording orders. My users asked me to design them a report whereby then can select a date from and date to, to show all of the orders which took place in a specific period of time. I just made a query from the table and in the criteria for date put: Between [Start of period] And [End of period]. You just then create the report using this query and its done. Im not sure if thats what you wanted, not sure if i understand your problem. good luck

 

[MrDowner]

What you said is exactly what I've done. The problem is that instead of creating a query, I need a report off of a crosstab query. Things don't work that easily with the crosstab query.

 

[Pat Hartman]

Make sure that the parameters you enter will actually select records. Use the crosstab's ColumnHeading property to define headings if you need a fixed set of columns (which you will if you want to make a report).

 

[jalmari]

Thank you

I had tha same problem and have been working on it very long time and now it solved by your advices, thanks a lot!!

 

[JC3]

Thats great. I never knew about the fixed column headings. Solves one problem for me.

Is there a way to also fix the row headings?

JC