Null Values in Crosstab

gdesreu

Active Member
Joined
Jul 30, 2012
Messages
318
This is a pretty serious issue with access and I'm wondering if there is a way to trap this. Basically, when you near the limit of headers either with fields or length of fields, access Crosstab queries will happily run and produce data but for numerous records it will report null values instead of the actual value. This is as serious as a heart attack for the end user. I'd rather prevent the query from running if this is the case rather than run without an issue and produce incorrect data. Is there a way to trap this error or stop the query from running rather than produce erroneous null values where my data should be?
 
Last edited:

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
If you get Null for some fields, it must be a valid result from the intersection of the underlying row/field for that query field?
Or the Null is a result of a calculation that involves a Null (thus the result of the calculation is Null).

You might be able to substitute null for a default value using the Nz function, but that's just a guess since I know not where the Null results are or where they might be propagating from.
 
Upvote 0
If you get Null for some fields, it must be a valid result from the intersection of the underlying row/field for that query field?
Or the Null is a result of a calculation that involves a Null (thus the result of the calculation is Null).

You might be able to substitute null for a default value using the Nz function, but that's just a guess since I know not where the Null results are or where they might be propagating from.
I think this is a true glitch in Access that I can reproduce. I have valid values that crosstab correctly. As you add data to a crosstab and begin approaching access field limits either in number of fields or the length in the fields, the crosstab runs but will provide sporadic erroneous null values where there is a valid value that was previously reporting correctly. I realize this is an obscure issue that almost no DB user will encounter but I only see it because of the way my company requires scientific data to be reviewed. Since I cant change that I'm trying to at least trap the error so as not to give a scientist making million dollar decisions on the basis of this crosstab data... erroneous results. In this case, I fixed the crosstab by simply reducing the characters in one of the fields. Since its a sporadic error and it provides data for many other results the error is almost impossible to detect on a mass scale unless I revalidate the data at 100% which isn't feasible. I basically need it to run correctly or not at all until I can figure out which field is causing the offense. I'm guessing the solution for this is to figure out what the access limitation for this is at the point it begins providing erroneous results and stop it from running.
 
Upvote 0
In this case, I fixed the crosstab by simply reducing the characters in one of the fields
By any chance are any of the fields that return null of the memo/long text type, or are of that type and are involved in any links between tables involved in this ct query?

I asked, but you shed no light on whether or not calculations are involved.
 
Upvote 0
field limit is 255, field name limit is 64. I think you could validate that in advance by counting the lengths of the values that will become your column headers and making sure they do not exceed 64, and counting the number of the values that will become your column headers to make sure they will not exceed 255.
 
Upvote 0
By any chance are any of the fields that return null of the memo/long text type, or are of that type and are involved in any links between tables involved in this ct query?

I asked, but you shed no light on whether or not calculations are involved.
no they are all number fields. Example 10.1 returns null if nearing the field limit. And the field that returns null can be corrected by reducing the lengths of say a meta data txt field in the same row. For example, query returns null...for the Field2 10.1 value, I change Field1 from "Sediment near shore" to "SE" or basically shorten any of the text fields the query will return correctly. The problem is not knowing that values are returning null when there are reported values. Its an unforgiveable error in my work environment and I'm not sure how to detect that its happened.
 
Upvote 0
field limit is 255, field name limit is 64. I think you could validate that in advance by counting the lengths of the values that will become your column headers and making sure they do not exceed 64, and counting the number of the values that will become your column headers to make sure they will not exceed 255.
I believe when you exceed these you get the "too many headers" error..which is fine ID rather have it stop, but you can be under those values and have rows that have lengthy meta data as an example and you wont get the too many headers error, it will run normally but provide erroneous results. Maybe I am misunderstanding how to perform the count but I don't believe its related to the column header limits, I believe its related to how many characters are present in each XT row. For example, rows with small amounts of meta data in fields return fine, rows with lengthy meta data return but with erroneous data values. (Just about the worst error I can think of in a Science setting) For example, if I store a lengthy network path in a txt field and I'm near the column limit, it runs but with error, if I reduce the filepath to just a filename, the same exact query runs perfectly fine. This reminds me of how you can store a file with a deep folder structure but you cant copy it or move it....(easily)
 
Upvote 0
Well, seems that neither I or Xenou have experienced this before, but we're trying.
If "Sediment Near Shore" is a table field value you're using as a query column header, then I have no suggestion on how to deal with the fact that shortening the field value helps. If it's a table field name, then there are 2 things you could try.
1) avoid the use of spaces and special characters (save for possibly the underscore _ ) in ALL object names. They are problematic for a number of reasons, albeit I've never heard of this situation being one of them. This I would fix (if practical) regardless.
2) Keep the field names with spaces but assign alias names that follow the recommended naming practice of no spaces or special characters. I believe Access will use the alias for a query field name instead of the actual name, but not 100% certain as it's been so long since I had to use aliases.
Aside from that or any other potential design problem we can't see, perhaps your only solution is to migrate the data to Excel, or use Excel to pull it it, perhaps via MSQuery - seeing as how you're trying to transpose row type data to columnar data anyway.

If none of that helps, then I think the only other thing I can offer is to take a look at your db and see if anything comes up.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top