I have a crosstab report that works fine without parameters. What I need to do is have a data range parameter filter the query for use with a report from a form. This is the crosstab query that I cannot make work. I have established the parameters in the query and when I run the query and manually type in the dates it works. However, when I run the form and enter the dates I get Error 3070: ... database engine does not recognize '' as a valid field name or expression. What do I need to fix or am I going about this completely wrong?

PARAMETERS [Forms]![frmCandidates]![txtRecEntryDateCriteria1] DateTime, [Forms]![frmCandidates]![txtRecEntryDateCriteria2] DateTime;
TRANSFORM IIf(Count([PK_CandIDNo]) & ""="",0,Count([PK_CandIDNo])) AS TotCandidates
SELECT IIf([RecEntryDate] Between ([RecEntryDate]+7-Weekday([RecEntryDate],7)-6) And ([RecEntryDate]+7-Weekday([RecEntryDate],7)),[RecEntryDate]+7-Weekday([RecEntryDate],7),[RecEntryDate]) AS WEDate, IIf(Count([PK_CandIDNo]) & ""="",0,Count([PK_CandIDNo])) AS TotCandidates
FROM tblCandidates RIGHT JOIN tblCandStatusTypes ON tblCandidates.FK_CandStatusTypeIDNo = tblCandStatusTypes.PK_CandStatusTypeIDNo
WHERE (((IIf([RecEntryDate] Between ([RecEntryDate]+7-Weekday([RecEntryDate],7)-6) And ([RecEntryDate]+7-Weekday([RecEntryDate],7)),[RecEntryDate]+7-Weekday([RecEntryDate],7),[RecEntryDate])) Between [Forms]![frmCandidates]![txtRecEntryDateCriteria1] And [Forms]![frmCandidates]![txtRecEntryDateCriteria2]))
GROUP BY IIf([RecEntryDate] Between ([RecEntryDate]+7-Weekday([RecEntryDate],7)-6) And ([RecEntryDate]+7-Weekday([RecEntryDate],7)),[RecEntryDate]+7-Weekday([RecEntryDate],7),[RecEntryDate])
ORDER BY IIf([RecEntryDate] Between ([RecEntryDate]+7-Weekday([RecEntryDate],7)-6) And ([RecEntryDate]+7-Weekday([RecEntryDate],7)),[RecEntryDate]+7-Weekday([RecEntryDate],7),[RecEntryDate]) DESC
PIVOT tblCandStatusTypes.CandStatusType;

So have you entered in Design - Query Parameters the Form Text Box name and the Data Type for both of the Text Boxes?

Yes, as indicated by the first line of my SQL statement or are you referring to another location?

Yes, I see you added the Text Boxes on the Form. Sorry!

What is the complete error you are receiving? Does it mention anything about the "RecEntryDate" Field?

The complete error is error 3070... database engine does not recognize '' as a valid field name or expression. It did not reference a specific control or field.

Hi Steve,

Could you please clarify your situation for us? Are you saying that you are trying to open a "report" based on this query while the form is open and you are getting the error? If so, how exactly are you opening the report?

Just my 2 cents...

The form is open and when I click a command button control the crosstab report is opened using DoCmd.OpenReport "rptCandidatesAddedWeeklySum2", acViewPreview, , , acWindowNormal. The SQL statement above is set as the record source of the report.

Thanks. And, do you also get the same error even if you just open the report manually from the Nav Pane?

Just my 2 cents...

Yes. The only place the query seems to run correctly is when I run it by itself and manually enter the dates.

Hi Steve,


Wait a minute... What do you mean by "manually" entering the dates? Aren't you supposed to enter the dates on the form before you open the query?

FYI, I get the same error. The reason is because I am trying to reference the name of the Field that I used in the Crosstab Query, the same one that I used as the Column Heading. Well, after I run the Crosstab, that Field no longer exists and instead you must refer to the name of one of the Column Names the Crosstab creates. So say you had a Field called States instead of referring to States in the Report you must have a Text Box for "Maryland", "New York", etc.

Does this sound like what the problem might be?

It very well could be. How do I go about fixing it?

DBGuy... I saved the crosstab query with the parameters and ran the crosstab query by itself to see ifit worked without the form is what I was saying.

So, opening the query with the parameters set to the form doesn't work either? If so, maybe RAZ's suggestion is the right one.

Just my 2 cents...

That is the strange thing when I open just the query... it asks me to manually put in the form values because it cannot find the form and when I manually put them in I get the result I want. It must work differently when the crosstab query is used in a report with the parameter values passed by the form. I feel I am on the cusp of either nailing this thing or going insane! LOL

That is strange! Are the names of the form references correct in the query? Are you able to post a zip copy of your db with test data?

Just my 2 cents...

One way to fix this, is in the Crosstab Query, view the Properties of the Column Heading. Then in the Column Headings, enter the Fifty States, each separated by a comma. You will also need to add a Text Box on the Report for each of the States. The problem with this, is lets say the US gets another State, that state will not show in the Crosstab until after you add it to the Column Headings and then to the Report too.

Edit: Goodnight Gentlemen.

RAZMaddaz

Wish I could post the DB but I can't as it is on a closed system. I am goign to play with the column headings a bit and see if that makes a difference.

Hi Steve,

I think I have a simple solution for you if it still doesn't work after following RAZ's advice. Let us know how it goes...

Just my 2 cents...

Love simple solutions so please do tell as I tend to make things harder on myself than need be. I am sure my boss is going to want a few more of these types of reports.

I renamed the column headings for the statuses and everything worked fine... thanks RAZMaddaz!

Hi Steve,

Glad to hear RAZ's suggestion worked!

My solution is simpler but there's a big difference in the result that you get in the Crosstab. For example, let's say you have 10 status columns but only have data that meet five of them. In your current solution, I think the crosstab still gives you back 10 columns, correct? In my solution, the crosstab will only give you back the five columns that meet the criteria. So, I'm not sure if that is still the result you want to get back.

Just my 2 cents...

Most of the time I would want it to give me all of the columns. That said... for some of the reports where there is a ridiculous amount of columns I would prefer not to output all of them.

Okay, not sure how this will apply to your query but in the WHERE condition, try wrapping the form references with the Eval() function. If you really want to display all the columns, you can modify the PIVOT part to add an IN() clause that specifies all the column headings.

Just my 2 cents...

Thanks for all your help! I will certainly play around with it some more.

Hi,



RAZ and I are happy to help. Good luck with your project.

Awesome!!!! Glad you got it working and that theDBGuy and I could help!

RAZMaddaz