Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access 2010 table with null field

Posted on 2015-02-18
10
Medium Priority
?
118 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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 39

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 39

Accepted Solution

by:
PatHartman earned 2000 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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

704 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