Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 31st, 2006, 02:44 PM
Registered User
 
Join Date: May 2006
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to return "0" instead of #Error

I have a field in my form that gets the value of a field in a subform, but if there is no data in the subform (it depends on the selection of a listbox in my form) the field gets #error instead of "0". If the subform has data it will show sum([Price]) -> the price total. How can I make this field in my form show "0" instead of #Error in case the subform is empty?

Hope I made myself clear.

 
Old May 31st, 2006, 03:07 PM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Try using the Nz formula when you reference it.

Something like sum(Nz(Price]))

This function changes Null values to 0's

It should work - if not we can try something else.

Mike

Mike
EchoVue.com
 
Old May 31st, 2006, 03:41 PM
Registered User
 
Join Date: May 2006
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

=( it didnt work....

 
Old May 31st, 2006, 06:24 PM
Authorized User
 
Join Date: Apr 2005
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

I use this code and it works fine.

=IIf(IsNull(([subfrmToolShippingTickets].[Form]![Text56])),0,DSum("[LineTotalTools]","tblInvoicesOrdersTool","[InvoiceID]=" & [InvoiceID]))

D. Bartelt
 
Old May 31st, 2006, 07:57 PM
Registered User
 
Join Date: May 2006
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

ok... but where should I use it?

 
Old June 1st, 2006, 07:04 AM
Registered User
 
Join Date: May 2006
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I tried the code, but it did not work =( I dont know why, maybe it is because since there is no data in the subform and the field is on its base, the field will not exist until some data is shown....

 
Old June 1st, 2006, 08:38 AM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Lets take a step back... Would you mind giving a brief explanation of the form and subform and how they are related? Maybe there is a different solution that will be easier to see if we have that.

Thanks

Mike

Mike
EchoVue.com
 
Old June 1st, 2006, 11:43 AM
Friend of Wrox
 
Join Date: Dec 2005
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Use this code as the source of the text box:
=IIf(DCount("[Price]", "subformSource") <> 0, DSum("[Price]", "subformSource"), 0)

Change subformSource to be whatever the subform is pulling its records from, a query works best.

 
Old June 2nd, 2006, 07:31 AM
Authorized User
 
Join Date: Apr 2005
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Do you have the sum([Price]) field in the subforms footer? If so what is the text box name? Not the control source. Also what table is the subform data stored?

D. Bartelt
 
Old June 2nd, 2006, 12:52 PM
Authorized User
 
Join Date: Apr 2005
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Use the formula

=IIf(IsNull(([subfrmYourSubFormName].[Form]![YourTextBoxName])),0,DSum("[Price]","tblYourTableName","[YourID]=" & [YourID]))

as the control source for the text box on your main form. That way if the subform doesn't have any data, "0" will be displayed on the main form.

D. Bartelt


D. Bartelt





Similar Threads
Thread Thread Starter Forum Replies Last Post
Error is using option buttons return value Subuana Beginning VB 6 2 May 1st, 2006 03:39 PM
WebMethod Error when return type is a dataset Hagop .NET Web Services 2 August 18th, 2005 09:13 AM
Webmethod error while return type is a dataset Hagop General .NET 1 August 10th, 2005 05:46 AM
error on Webmethod when return type is a dataset Hagop General .NET 1 August 10th, 2005 05:45 AM
Can I return error codes if I am not using rs? codehappy VB How-To 0 April 19th, 2005 03:19 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.