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????
-
-
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? -
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? YESComment
-
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?Comment
-
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????Comment
-
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.Comment
-
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??Comment
-
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.Comment
-
I get #ERROR on the report where the value is null....Is this normal??Comment
-
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.Comment
-
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.Comment
-
Pleased to hear it & good luck going forward :)Comment
Comment