Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access 2010 table with null field

Posted on 2015-02-18
10
Medium Priority
?
119 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 40

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 40

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

916 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