Access 2010 table with null field

I have a table I need to sum records on, but one field is keeping me from doing that.  It is a field that appears blank, but is in some instance "Null" and others "Not Null".  See example below of E&O Tag field:

Part Number      Qty      Amount      E&O Tag
1124087      16800      14112      
1124087      -16800      -14112      

The Qty and Amount fields should add to zero, but don't because of the E&O Tag field.  I have taken the E&O Tag out of the query and it works fine, but I need that field in my results as it sometimes has text in it.  I am thinking about an update query, but how do I do that?  Update from Null to ...?  What is the best way to get this done?  Thanks.
LVL 7
tomfarrarAsked:
Who is Participating?
 
PatHartmanConnect With a Mentor Commented:
The problem was with the Tag field which was being grouped on.  In some cases it contained null and in others ZLS which don't group together.  Once the OP got rid of the ZLS, the grouping worked fine.

Last(Tag) isn't an option because there are other non-null, non-ZLS values that would be grouped in error.

Nz(Tag) would work because, it would change the nulls to ZLS so that would allow correct grouping.

But getting rid of the ZLS entirely is a better solution to avoid confusion in other queries.
0
 
Kelvin SparksCommented:
Just add using the Nz Function (Null to Zero) - eg Nz(Qty) + Nz(Amount) + Nz(E&O Tag)

Kelvin
0
 
tomfarrarAuthor Commented:
Hi Kelvin - I am not adding the E&O Tag field, but need it as a transaction identifier. The quatity and amount fields are shown to let you know that if I were summing the two lines in a query (group/sum), the results should only be one line for part number 1124087.  I am currently getting two lines (unsummed) because in one case the E&O Tag is "Null" and the other case it is considered "Not Null".  I want the E&O Tag field to be the same, Null or Not Null so the two lines will sum into one total in my summing query.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Kelvin SparksCommented:
Would need to know more about the query - sounds like the E&O tag is not part of the same table....
I suspect the issue is wider than what you have stated so far.


Kelvin
0
 
Rey Obrero (Capricorn1)Commented:
perhaps you are including the field "E&O Tag" in the group by clause, in which case you need to change it to First or Last

select [Part Number], Sum(Qty), Sum(Amount), Last([E&O Tag])
from tablex
group by   [Part Number]
0
 
tomfarrarAuthor Commented:
It is all coming from the same table, Kelvin.  I have already figured out how I can make it work.  I made the blank field values (some Null, some Not Null) all Null by using an update query.  Using update criteria changing " " to Null.  Now Qty and Amount are summing to zero.  Thanks for your thoughts.
0
 
Rey Obrero (Capricorn1)Commented:
@tomfarrar

did you try the query i posted ?
0
 
PatHartmanCommented:
You should also change the table definition to set the AllowZLS property to No so the ZLS don't creep back in.

They happen when you import files from old systems that use fixed length fields and when your users improperly back out a change in a form.  If a user types something in a control and then backspaces or deletes character by character with the delete key, Access stores that as a ZLS.  The only way to actually remove an unwanted entry is with the esc key (for initial entry) or by selecting all the characters and using the delete key for existing entries.
0
 
Helen FeddemaCommented:
The Nz() function yields a zero for numeric fields that contain a Null, or an empty string ("") for text fields that contain a Null.  What data type is the E&O field?  You may need something more than just Nz if it is a text field.  For example, in addition to using Nz() to convert Nulls to empty strings, you might need to convert "", " " or any other text into a zero so it can be summed.
0
 
tomfarrarAuthor Commented:
Though I found my own workaround, there was some valid and interesting comments, but Pat hit the nail on the head.  Thank you all.  - Reilly
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.