Solved

Access 2010 table with null field

Posted on 2015-02-18
10
114 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 36

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 36

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

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Creating a Temp Table in MS Access 5 43
MS Access Query Question 2 33
Find unused columns in a table 12 67
Access #Deleted data 20 35
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

685 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