Blank field problem at access 2007

Hi,

I have a query with 3 columns. 1st and 2nd columns are from a table that 
includes numbers. 3rd column is for summation of 1st and 2nd columns. I have 
no summation if a field is blank. For example, query gives a result 
something like below:

1st_______2nd________3rd

10________20_________30
3_________5__________8
Blank______7__________Blank (must be 7 ???)
15________Blank_______Blank (must be 15 ???)


How can I solve this problem?

Thanks a lot in advance. 


0
Art
11/22/2007 9:50:57 AM
access 16762 articles. 3 followers. Follow

2 Replies
1276 Views

Similar Articles

[PageSpeed] 25

Use Nz() around each of the numbers.

Presumably you have a calculated query field that looks like this:
    Col3: [Col1] + [Col2]
Try:
    Col3: Nz([Col1], 0) + Nz([Col2], 0)

For an explanation of what's going on see issue #2 in this article:
    Common Errors with Null
at:
    http://allenbrowne.com/casu-12.html

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Art Vandaley" <kestafir@hotmail.com> wrote in message
news:O1R4vxOLIHA.5580@TK2MSFTNGP02.phx.gbl...
> Hi,
>
> I have a query with 3 columns. 1st and 2nd columns are from a table that 
> includes numbers. 3rd column is for summation of 1st and 2nd columns. I 
> have no summation if a field is blank. For example, query gives a result 
> something like below:
>
> 1st_______2nd________3rd
>
> 10________20_________30
> 3_________5__________8
> Blank______7__________Blank (must be 7 ???)
> 15________Blank_______Blank (must be 15 ???)
>
>
> How can I solve this problem?
>
> Thanks a lot in advance. 

0
Allen
11/22/2007 11:46:26 AM
Hi Art,

You need to understand your table data.  I assume the two columns are 
defined in the table as numeric?  If so your 'blank' is a Null.

1. Define default values for your table columns as 0 (zero) and update all 
existing records.  Your existing query should then work.

2. If you don't want that or can't change the table's metadata then use the 
Nz function when summing col1 and col2.  Your third column specification 
should look something like the following.

Nz([col1],0) + Nz([col2],0)

Rod

"Art Vandaley" wrote:

> Hi,
> 
> I have a query with 3 columns. 1st and 2nd columns are from a table that 
> includes numbers. 3rd column is for summation of 1st and 2nd columns. I have 
> no summation if a field is blank. For example, query gives a result 
> something like below:
> 
> 1st_______2nd________3rd
> 
> 10________20_________30
> 3_________5__________8
> Blank______7__________Blank (must be 7 ???)
> 15________Blank_______Blank (must be 15 ???)
> 
> 
> How can I solve this problem?
> 
> Thanks a lot in advance. 
> 
> 
> 
0
Utf
11/22/2007 11:52:00 AM
Reply:

Similar Artilces:

Money 2007 E-Pay
Noticed this morning when I was paying my bills this anomaly. Perhaps I am missing something. Scenario: Paying a credit card bill. In Money 2004 (my previous version of Money) I would use Credit Card as the category and in the subcategory use the particular credit card account I was paying. Worked great. Money 2007, there is a new category named Credit Cards/Transfer. When selecting this there are no payee categories listed. However further down the list is another category named Credit Card. Using Credit Card shows the various payees. Not sure what the difference is between Credit C...

Customizing Recipient Polices to filter for FROM email field?
I need to find a solution to a new policy that our Exchange server must follow. We currently run with an email retention limit of one year, no problem. Now I need to run an exception to that Policy where email from one particular user must be deleted from any mailbox if it's older than 14 days. I'm at a loss finding information that indicates this is even possible. Is it? Any suggestions would be greatly appreciated. I am running Exchange 2003 SP1 on Win2k3. Thanks, Mark ...

Word problem
Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Word will not let me insert a bibliography into my word document. When i go onto document elements - it is not allowing me to click as it is shadowed - ???? Since you don't specify otherwise, start with the basics: 1- Make sure Office is fully updated to 12.2.4 2- Make sure OS X is fully updated to 10.6.3 3- Repair Disk Permissions 4- Restart your Mac Confirming all of that it will make it possible to determine how to approach the problem. Until that's all done it's pointless to chase symptoms. Regar...

RPC over HTTP problem #3
Hi, All! My network configuration: DC1, DC2 and MX (MS Exchange 2003, sp1). All of them Windows Server 2003. What was done: In the registry on dc1 and dc2 was created a new key: "NSPI Interface protocol sequences" with value: ncacn_http:6004. MX was promoted to be a GC. Installed RPC over HTTP windows component. MX was changed to be RPC-HTTP back-end server. On the MX Default Web Site was installed cerificate from the local authority running on DC2. On the RPC virtual directory anonymous access and integrated windows authentication were disabled. In the registry of MX the key HK...

Problems saving a worksheet with Links
Does anyone know how I can resolve this issue ... I have a directory which contains 129 worksheets which have links to external data (in a Master Spreadsheet) -- I need to copy these files into a New Directory, but kee the Master Spreadsheet (which they are linked to) in the original location. If I do a simple Cut & Past, the Reference Link to the Master Spreadsheet gets moved to the New Directory (where the file does not exist), but if I open the worksheet (in the original directory/location) and Save As to the New Directory, the worksheet saved in the New Directory maintains its link t...

Ho to make one field required based on critera of another field?
I'm creating a form and need to make the "comments" field required if the "code" field is =>20. I appreciate suggestions! Deadline Monster is lurking! User enters the job processing endcode value (numeric) into the "code" field. If the endcode is =>20, comments are required. (P.S. I don't know VB) Thanks! Star You would put your validation code in the Form's BeforeUpdate event. If Me.EndCode >19 Then If Len(Me.Comments & "") = 0 Then MsgBox "Comments are required" Cancel = True End If End If ...

Numbers in a text field-can I add them up?
Hi everyone! Using A02 on XP. I have a table of data with survey response fields that contain a 0,1,2,3,4 or 5. However, the fields are formatted as text, not numbers. I need to add up certain blocks (Items 1-6, Items 7-23, etc.) and then do some averaging. I cannot change the field types from text. Must I append to a new table or can I do something right in my query? I've got one field in my query like this: ES: [Item1]+[Item2]+[Item3]+[Item4]+[Item5]+[Item6] My result is: 553453 or 554444, etc. I want: 25 or 22, etc. I would really appreciate any help or advice. Thanks...

data value in Form field if no table entry
I have a form with a field which pulls through and concentenates 2 fields called [ContactFirstname] and [ContactLastName]from my table There are however some customers for whom I do not have names and therefore instead I would like Sir/Madam to appear in the field in the form I think I have seen this done somewhere using ELSE? but can't find it Any help/ideas gratefully received Perhaps something like this: Nz(Trim([ContactFirstname] & " " + [ContactLastName]), "Sir/Madam") -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access use...

Constructing Hyperlink from the Database Record fields
I am working on a Windows XP environment using MS Office 2007 including Access 2007. I want to open a document from Access 2007 which I can easily do with Hyperlink type field. However since all the necessary information is already in the Database Record I try to avoid creating additional field which would be a Hyperlink type on the Form unless it is absolutely necessary. Below is the code that I have to construct the FullFileName which consisted of ServerName, Division, Unit, RequirementDirectory, FolderName and the FileName itself. As you can see the Database records has al...

How do I create a bookfold document in Word 2007?
I am new to Word 2007. How do I create a document in booklet form? Thanks I'm sure you'll get better answers in an MSWord forum. dadolim wrote: > > I am new to Word 2007. How do I create a document in booklet form? Thanks -- Dave Peterson ...

2007 calendar
How can I create a 1 page 2007 calendar in publisher? File, new, publications for print, calendars, select one, select yearly... click Change date range. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Angel" <Angel@discussions.microsoft.com> wrote in message news:7253E645-B0BA-4B90-BF7E-7083802EA379@microsoft.com... > How can I create a 1 page 2007 calendar in publisher? ...

making an equation in access
Hi, I would like to create a field that works out a formula based on other fields. i.e field z=field4, when field 1<field 2 - field 3 etc. Yes, it may be a silly question but I am only new to this....Thanks, Dani. Dani, recommend against creating a "field" in a table and storing this value. One of the generally accepted rules of relational database development is not to store a "computed" (any value that is based on the value of other fields in your database) value in the table. It is a waste of database space, and will eventually result in bad data (...

User Defined Required Fields
I have set several field on sub window Sales User-Defined Fields Entry of Sales Transaction Entry as "Required". If the user remembers to click User-Defined, then they become required. But if the user never clicks on User-Defined from Sales Transaction Entry, then they can still save the new document without the required fields entered. Does anyone know what I can do to fix this asside from continual user training? Your answer is VBA. You own Modifer, so you also have VBA enabled. You'll need to write VBA code to open the window (literally, push the button) then set th...

VISIO 2007 -Text direction
can some one tell me how to change text to be type in vertically. Under tools, options there is no regional tab or under format text the change text direction command does not work. "kgbrat" <kgbrat@discussions.microsoft.com> wrote in message news:2DBF18B5-E1C8-4493-8BEF-F7D4C1538781@microsoft.com... > can some one tell me how to change text to be type in vertically. Under > tools, options there is no regional tab or under format text the change > text > direction command does not work. You can use the Text Tool (The A with an circular arrow around it) and gr...

Send/Receive Problem
I am using Outlook 2002 on an XP platform. I cannot get Outlook to check for Email at regular intervals. I have the my Outlook set to Send and Receive all my accounts every 10 minutes but nothing happens. The only way I can receive Emails is by manually using the Send/Recv button or pressing F9. Can anyone offer any help. In case it is relevant I am using Norton Internet Security 2003. PWS Not sure it it Yor problem, but Outlook has some problems with Noroton Antivirus runing and chekking e-mails. As far as I know, Outlook may stop recieving e-mails from POP3 servers due to very le...

2007 License
I received a free Office SBA 2007 via an MS Partner Program as a download together with key There is no FPP, OEM or MLK designation. (The EULA in Word being dependant on the above) How can I determine if this is licensed to install on a second device, other than trying? - The PID? Contact the MS Partner Program? "DL" <notvalid@spoofaddress.co.uk> wrote in message news:uVA$w8lpKHA.4648@TK2MSFTNGP06.phx.gbl... :I received a free Office SBA 2007 via an MS Partner Program as a download : together with key : There is no FPP, OEM or MLK designation. : (The EULA...

Field mapping for Opportunity Products
Why I can't add some field mapping for OpportunityProduct system relation with Opportunity? I need to know the default pricelist that was assigned for an Opportunity when I am at OpportunityProduct form. ...

auto forward problems
I setup a 'contact' for 5 existing users in Exchange 5.5 Administrator. I give the contacts the desired SMTP address where they want their mail forwarded to. I set the corresponding 'contact' as the 'alternate recipient' for each of the 5 as detailed in Q255697. 2 of 5 work, the other 3 do not. When sending to each of the 5, 3 return undeliverable stating "A configuration error in the e-mail system caused the message to bounce between two servers or to be forwarded between two recipients." Any ideas? -adam Adam SK wrote: > I setup a 'con...

Using Excel spreadsheet as input to Access
Hello, I posted this in the New Users forum but only got one answer, so thought I'd try here as well. Like so many others, I am an Excel newbie. I was a mainframe COBO programmer in another life, but that was a few years back My manager would like me to write an app that will take tracking dat from an existing Excel spreadsheet (generated by our system) but onl use a select handful of columns as input to a new Access database tha I will create. I'm guessing that I can either a) create a new edited spreadsheet to b used as input to the Access database or b) use the Import wiza...

Office 2007 11-23-09
Of all the choices out there (community discussion, dashboard, live, etc.) how can I contact someone with input on Microsoft's software. For example, I want to know why any one would not display the most common commands on the HOME tab in Office 2007. Print, Save, Open, Close, Spell Check, Undo, etc. etc. etc. Yes, I know you can customize your own Toolbar, but not a beginner. How can I emphasize how important this is, I don't suppose you can customize the Tabs. I would like to ask someone why they can't get input from those of us who teach their products. ...

small problem
hi every body; i wrote a program that it has error ;plz help me :( using System; namespace ConsoleApplication45 { class Program { static void Main(string[] args) { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("***********"); for (int i = 0; i < 1008; i++) { Console.BackgroundColor = ConsoleColor.DarkCyan; Console.Write(" "); } move(); // ***********error is for here***********************************************...

Excel problem #3
I am attaching an excel file where i have a problem In the file are 2 sheets, Main & second I want to get data from second sheet to the main sheet by a formula by which the amount in the total column will be posted in the second sheet falling under various dates. I have done for 6 sept 2003 by way of example I do not know any formula by which i can do this automatically Please help me Attachment filename: example.xls Download attachment: http://www.excelforum.com/attachment.php?postid=444742 --- Message posted from http://www.ExcelForum.com/ Hi one way: ...

Using Risk+ with MSP 2007
We use Risk+ as a risk simulation tool. We have discovered that it works approximately 20 times SLOWER in MSP 2007 than MSP 2003. Has anyone else run into this problem. If so, is there a remedy? My hunch this not a generic issue, but surely best that you consult with the Risk+ people on this. --rms www.rmschneider.com On 02/03/10 18:08, Tom Mc wrote: > We use Risk+ as a risk simulation tool. We have discovered that it works > approximately 20 times SLOWER in MSP 2007 than MSP 2003. Has anyone else run > into this problem. If so, is there a remedy? W...

Concatenating cells but excluding blanks
Hello, I am trying to create a result field, concatenating populated cells from the previous 12 columns on that line, but excluding blank cells and putting a * delimiting character between each instance - please find below a 4 column example. ID 1 2 3 4 Result Z A C D A*C*D Y B C B*C X A B D A*B*D Each of the 10,000 lines of the spreadsheet is different - there are at least 5 blank cells on each line Any help gratefully received. I am working in Excel 2007 Many thanks. Bob Try this: http://img690.imageshack.us/img690/5826/nonamee.png Micky "Bob Fr...

Sync net folder problem
I am sharing my calendar to my workmate with net folder. My PC is Win XP and Office 2000 and my workmate's is Win 98 and also Office 2000. I always find that My calender can't be updated from my workmate when I return office after I've taken my notebook for a few days. Can't net folder sync. data offline? Thx. your attention. Ken So Net Folders uses e-mail messages to send updates between computers so naturally you would have to be connected to your e-mail to get any updates. When you leave the office and are not connected you won't get any updates but as soon as ...