By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,761 Members | 1,878 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,761 IT Pros & Developers. It's quick & easy.

Need Help With Report

P: 24
I have a report that uses a crosstab query as its data source. The problem is not all of the crosstab values are present every time the report is generated. As a result, I get an error when the report is run. Access is looking for the value of the missing crosstab value. How do I supress the crosstab value from the report when the value is null????
Jan 27 '09 #1
Share this Question
Share on Google+
11 Replies


NeoPa
Expert Mod 15k+
P: 31,489
Can you be a little more precise?

What error (including message please) are you getting?

Presumably this is about a field which IS defined in the report but is simply missing (Null) in the data?
Jan 27 '09 #2

P: 24
NeoPa,

The message reads:

The Microsoft Jet database engine does not recognize "my report field" as a valid field name or expression.

Presumably this is about a field which IS defined in the report but is simply missing (Null) in the data? YES
Jan 27 '09 #3

NeoPa
Expert Mod 15k+
P: 31,489
Do you have any control in your report which has "my report field" as its source? If so, what should it be referring to?

That sounds to me like an instruction, or some example code. It would need to be given the name used in your query before it can work correctly.

Does that sound like sense?
Jan 27 '09 #4

P: 24
Yes, one of the fields in the report is tied to its value is a crosstab query. If the value is the crosstab is null or empty, I get the error. Is there a way to suppress the field on the report if its corresponding value in the crosstab query is null????
Jan 28 '09 #5

NeoPa
Expert Mod 15k+
P: 31,489
I can't tell if your first sentence is important as I cannot find a way to read it whereby it makes sense to me. The second is easy if I change the first "is" to an "in".

If I simply ignore the first sentence I would say that I'm surprised that a report control would fail on a null value. You could try using =Nz([FieldName],"") instead of FieldName, but I wouldn't be confident of success as I would not expect the error in the first place. See what you get when you try that.

PS. I'm assuming a string value. If it should be numeric then try =Nz([FieldName],0) instead.
Jan 28 '09 #6

P: 24
Let me clarify. I created a report that shows the age of unfilled orders. I have it grouped by region and age (in days) The data for the report comes from a crosstab query that groups by REGION and AGE IN DAYS. The grouping values for AGE IN DAYS are <=30, 31-90, and 90+. It is possible for one of the AGE IN DAYS values in the query results to be null or blank. When this occurs, there is no corresponding value in the query results to pass to the field on the report . I believe this is why I am getting the message I descibed earlier.

Here is a sample of what the report might look like:

A G E I N D A Y S

REGION <=30 31-90 90+

East 2 4 12

West 3 7 9

North 0 6 11

South 2 5 7


Now, the problem occurs when as displayed above, the value for the NORTH region's orders that are <=30 days is NULL. (i.e. the value is null or blank in the corresponding crosstab query.) When this happens, the report bombs!!!!!! So, is there a way to suppress the field on the report if the corresponding value is null or blank?? I am not using VB code to create the report. I have texts boxes on the report that are using the values of the crosstab fields as their control source. Does this help??
Jan 28 '09 #7

NeoPa
Expert Mod 15k+
P: 31,489
In the Control Source property of of the control you're having this problem with, you should see something like "My Report Field" (without the quotes).

To use Nz() you would need to enter "=Nz([My Report Field],0)" instead. The "=" tells it there is a formula to use rather than simply the field as returned by the bound recordset.
Jan 29 '09 #8

P: 24
I get #ERROR on the report where the value is null....Is this normal??
Jan 29 '09 #9

NeoPa
Expert Mod 15k+
P: 31,489
It's not what I would expect from what you've told us I must admit (although I've almost never used cross-tab queries, and using one for a report is something I cannot imagine ever doing.

If you can attach a zipped copy of the database to the thread I'll look at it for you if you like.

To reduce the size it may be necessary for you to strip out a lot of the database. Leave the minimum actually required to illustrate the situation, so test that it still fails in the same way before sending your example database.
Jan 29 '09 #10

P: 24
Thanks for the offer, NeoPa, but I took your advice and abandoned the idea of using a crosstab query as the source of the report....All is well now.
Feb 3 '09 #11

NeoPa
Expert Mod 15k+
P: 31,489
Pleased to hear it & good luck going forward :)
Feb 5 '09 #12

Post your reply

Sign in to post your reply or Sign up for a free account.