Solved

Access 2010 table with null field

Posted on 2015-02-18
10
116 Views
Last Modified: 2015-02-22
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.
0
Comment
Question by:tomfarrar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40617123
Just add using the Nz Function (Null to Zero) - eg Nz(Qty) + Nz(Amount) + Nz(E&O Tag)

Kelvin
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 40617261
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40617284
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40617323
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
 
LVL 7

Author Comment

by:tomfarrar
ID: 40617416
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40617483
@tomfarrar

did you try the query i posted ?
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40617505
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40619153
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
 
LVL 38

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40619187
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
 
LVL 7

Author Closing Comment

by:tomfarrar
ID: 40624630
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

Featured Post

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question