Need help with Nz() or IIf() to get a zero value (1 Viewer)

[RainLover]

I wonder if this is on a RunTime system without Error checking?

I doubt if it is Run Time as it is A 2003 and that is not free. Surely he must be developing in MDB.

Error checking would be nice to catch where the crash is happening. It could be that Form, or one the many SubForms.

The design gets more complicated as time goes on. Then there are various queries.

Its just another wonderful day here at AWF.

 

[gemma-the-husky]

Has this been all resolved yet?

If not, maybe this helps?

I have not read the whole thread in detail, but out of interest, your iif statements appear to be reading a numeric "payment value" or some such - but are assigning a "text" value to the alternative clause - and even then, the iif statements do not seem to be correct

so I am not sure if this is part of the problem.


eg - your very first exaample

IIf([Daily WO Query-mastercard].[Form]![TotalPayment],"0","")

the first expression will be resolved a boolean, which is not what you want, I am sure.

if expression (is true) then show "0" otherwise show blank "" - which actually sounds like what you are getting

maybe you just need something like

nz([Daily WO Query-mastercard].[Form]![TotalPayment],0)


-----
(added 31/1/13)

actually if [totalpayment] is a control on a form, maybe the syntax ought to be

forms![Daily WO Query-mastercard]![totalpayment]

 

[U-Doo]

Q. Did the First Calculation work when Data was there. - A. No. it always gave a value of zero; In order to get a value the code would have to look like this:=IIf(IsError([Daily WO Query-mastercard].[Form]![TotalPayment]),0,([Daily WO Query-mastercard].[Form]![TotalPayment])) --> this code gives a zero value with no data but also gives a true value if there is data (but it crashes)

Q. Where the result was Error in the first Text Box, did that convert to 0 (Zero) in the second Text Box.- A. Yes, both boxes showed a zero value

And yes, this was all in mdb

 

[RainLover]

U-Doo

Can you put some more white space in your reply. Gemma The Husky does a very good job at it.

In particular put code on separate lines and use code Tags. or go to advanced reply.

Now back to your problem. What is....

 [[B]Daily WO Query-mastercard].[Form]![TotalPayment][/B]

Your are not using naming conventions so I can only guess what it is. Is it a Text Box on the Form. If so is [TotalPayment] the correct name of the Text box or is it the name of the field in the table. They can and should be different.

BTW

Could you put you location in your Profile so that we can tell what time zone you are in. England or East Coast USA is close enough. This is so I know when to look for your replies.

EDIT

I had another think about this and this should work.

 [[B]Daily WO Query-mastercard].[Form]![TotalPayment][/B]

It should return the value you seek or an error. :banghead:

Where is the value you want located. Is it on the same Form, a SubForm or perhaps a different form.

 

[U-Doo]

I'm in Northwest Alberta, Canada (MST) Mountain Standard time.

[TotalPayment] is a text block (and name) in each separate form/subform. It totals the full value of that form (i.e. -Master Card Payment)

=sum([Payments]) called "TotalPayment"

the results of this is either a number value (or) completely blank if there are no records.

there are 4 forms almost identical, except one totals Cash Payments, one totals Visa Payments, one for Interac, and of course one for Master card.

These 4 forms are put into the main form as subforms

Subform#1- Daily WO Query-mastercard is the name of the subform; TotalPayment is the name of the text block that has the =sum([Payments]) The sum of all the master card payments.

Subform#2- Daily WO Query-visa is the name of the subform; TotalPayment is the name of the text block that has the =sum([Payments]) The sum of all the visa payments.)

Subform#3- Daily WO Query-cash is the name of the subform; TotalPayment is the name of the text block that has the =sum([Payments]) The sum of all the cash payments.)

Subform#4- Daily WO Query-debt is the name of the subform; TotalPayment is the name of the text block that has the =sum([Payments]) The sum of all the debt payments.)

~~~~~~~~~~~~

The Main Form has the calendar control and the 4 subforms (only showing the final totals).

The text blocks we are trying to work on is the 4 text blocks in the main form (called cash, visa, mc, and debt - not that it matters of their names) that go and retrieve the Number value from the "TotalPayment" text blocks from these 4 subforms.

I originally has the code as in these 4 main form text blocks as (except each was linked to the correct form, I am only showing one here):


nz([Daily WO Query-mastercard].[Form]![TotalPayment])

and

IIf([Daily WO Query-mastercard].[Form]![TotalPayment],([Daily WO Query-mastercard].[Form]![TotalPayment]),"0")

both codes gives me an error if no value, or the correct value if there is a number value

and I also tried

nz([Daily WO Query-mastercard].[Form]![TotalPayment],0,"") gives me a blank


IIf([Daily WO Query-mastercard].[Form]![TotalPayment],"0","") This code gives me a Zero but if there is data available, it still shows a zero

the '#error' was because there is no number value - it is completely blank) unless it had an actual number value in which case it showed that number value (i.e. 25.00). so, to clarify: i got either $25.00 or #error

Once again, the code listed below works but crashes MS Access (i.e. it gives me either a zero or the proper value then after a few date changes, it crashes):

=IIf(IsError([Daily WO Query-mastercard].[Form]![TotalPayment]),0,([Daily WO Query-mastercard].[Form]![TotalPayment]))

I sure hope this clarifies it better for you as I don't know if i could get any more specific.

The last code you gave me to try just gave me a zero all the time

 

[RainLover]

Just to throw a few ideas at you.

Requery after changing the dates.

If TotalPayment is the name in the RecordSource then change the Text Box Name and all your statements to txtTotalPayment. This will make sure it is looking at the value on the form not in the query.

If TotalPayment always returns the correct value then something must be wrong with

[COLOR=black] [[B]Daily WO Query-mastercard].[Form]![TotalPayment][/B][/COLOR]

.

The text blocks we are trying to work on is the 4 text blocks in the main form (called cash, visa, mc, and debt - not that it matters of their names) that go and retrieve the Number value from the "TotalPayment" text blocks from these 4 subforms.

This has me concerned as I don’t know if you mean Form or SubForm.

The Location of where the Information is and where you want to copy it to has a bearing on how to write your code. I don’t know if you are right or wrong but it might help to look at this site as it explains the various scenarios.

http://www.mvps.org/access/forms/frm0031.htm

Others here at AWF must also be unsure because someone usually jumps in if they see something wrong or if things are not been explained right by either party. However I am sure others are watching.

If none of the above helps we may need to see a cut down version of your Database. Just bare bones stuff. Just enough to repeat the fault. But that is up to you if you want to do that.

 

[gemma-the-husky]

out of interest, why read these figures off a form?

why not collate them from a subquery?

 

[U-Doo]

[QUOTE This has me concerned as I don’t know if you mean Form or SubForm.
The Location of where the Information is and where you want to copy it to has a bearing on how to write your code. I don’t know if you are right or wrong but it might help to look at this site as it explains the various scenarios. ENQUOTE]

OK, the 4 forms are just that, 4 seperate forms. These same 4 forms are in the main form as 4 subforms. I have text boxes in the MAIN form that are retrieving data from these 4 subforms/forms (whatever you want to call them).

I will change the titles of the text boxes in the subforms but really don't see how that would help. Like I've said before teh code I have works. It just seems to crash after a few searches (or calender date changes).

Maybe this will help confuse you more.
I have 1 Main form which is controlled by the Calandar control and a table i call WO Records

in this main form, I have several SUBFORMS (which have a link or related field to the WO Records table)

This main form (calandar comtrol) will pick a specific date to show, and the data from the WO Records (in the MAIN FORM) will retireve the data from that specific date;

This will also retrieve the data from all the SUBFORMS for that specific date.

Now, all I'm doing is to take the grand totals from these subforms in text boxes in the MAIN FORM and adding, subtracting, mulitplying... whatever. to get a final cashout & profit value. This is where the crash happens, because some of these SUBFORMS contain NO DATA - i.e. The field is completely BLANK, which gives a response of "#error" in one or several of the text boxes.

 

[U-Doo]

out of interest, why read these figures off a form?

Because I need to add, subtract, divide, multiply the totals from several of these subforms. each subform gives me a value that works with teh totals form the other subforms

 

[U-Doo]

if it would help, I can email you a copy of the database. Its about 12MB in size.
Feel free to email me
darren@tagervision.com

 

[RainLover]

if it would help, I can email you a copy of the database. Its about 12MB in size.
Feel free to email me
darren@tagervision.com

To post a Database.

1 Make a copy.
2 Delete unnecessary objects. Tables Queries Forms and Reports. Just leave what is needed to demonstrate your issue.
3 Test that your problem exists and that your Database opens and works correctly.
3 Do a Compact and Repair.
4 Convert to Access 2003 if it is in 2007 or 2010
5 Zip the file.
6 You should have a very small file which you can easilly attach to a post.
7 Download your attachment and test again to make double sure you have done everything right.

We don't like to work in private here at AWF as others may learn from this thread and someone else may have the solution to your problem.

 

[U-Doo]

i have it down to 1.35mb but i don't have anyway to upload it, suggestions?

 

[RainLover]

i have it down to 1.35mb but i don't have anyway to upload it, suggestions?

There are instructions in the FAQs

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[U-Doo]

ok, the database should be here

 

[RainLover]

In post Number 12 I suggested you look at your naming conventions. It is now apparent that this is where you problem lies. You are not refering to the correct Control.

You are using "=Nz([Daily WO Query-debt].[Form]![TotalPayment])"

Daily WO Query-debt is the Source, not the Control.

The Control is "DailyWO Query"

I have not done any testing to prove my point as I spent enough time tracking down your problem.

A Form may be Named "Fred" but when used as a SubForm it becomes a Control of the Form and could be named something toally different. e.g. "Fred Flintstone"

It is now important for you to name objects by a proper convention. like tblTable, qryQuery and frmForm. NO spaces. No special characters. Think of the old DOS naming.

Suggest you read my link from my signature. Naming Conventions. Download it and after you understand you really need to go back and fix everything or you are just making life difficult for your self.

Best of Luck with the project.

 

[RainLover]

Attached is what I did with your form.

 

[U-Doo]

In post Number 12 I suggested you look at your naming conventions. It is now apparent that this is where you problem lies. You are not refering to the correct Control.

Best of Luck with the project.

You'll have to forgive me but I don't see where there is a problem. The names may not be as per the way you recommend them, but whether the form is called frm, or form, how is that going to correct the issue?

The code
=Nz([Daily WO Query-debt].[Form]![TotalPayment])

or

=Nz([frmDailyWOQuerydebt].[Form]![TotalPayment])

I will still have the same issue with it crashing. You now have me confused.

The data is being retrieved from the form and the specific field, how is renaming them going to fix that? It will still be a form and the specific field; unless your trying to tell me that the formula should be linking to the subform (which is just a shortcut to the real form).

Now I'm really not understanding.

 

[RainLover]

In your Form you have

=Nz([Daily WO Query-debt].[Form]![TotalPayment])

Does it work? It give me an error.

=Nz([Daily WO Query].[Form]![TotalPayment])

Is what works for me.

Sorry for confusing you about Naming conventions.

You can use just about anything and it will work.
My point is that coding is much easier to follow if you maintain a convention. This way you can look at a name and without any further investigation know what it is. Form, Table etc. Plus a lot more knowledge can be obtained. e.g. ClientPK would tell me that this is the primary key in the Table tblClient. ClientFK would tell me that this is a foreign key to tblClient.

Hope this helps.

 

[U-Doo]

In your Form you have

=Nz([Daily WO Query-debt].[Form]![TotalPayment])

Does it work? It give me an error.

=Nz([Daily WO Query].[Form]![TotalPayment])

Is what works for me.
.

using Nz([Daily WO Query].[Form]![TotalPayment])

gives you the grand total of all, if you go to Jan-25th, you'll see a value for master card.

the 'transaction types' (showing in white) will give you the totals for each 'type' for that day. This # should be the same that shows in totals (for mastercard, visa, caash, interac).

The columns i added on the far right (in red font) called trial tests, will show the # (or) a zero. The code in these blocks seem to work, but when you try different dates, it will crash.

All the data is in the month of January.

 

[RainLover]

U-Doo

Your Form is so complicated that I can'y find my way around.

Sorry but I just do not have enough time to sort it out.