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

[U-Doo]

I'm using Access 2003 and have an issue with getting a zero value from a query (or) form that has no records. It works great as long as there is at least 1 record but not for a 'no record' issue.
I've used Nz; IIf, and IsNull and the codes work providing i have at least one record with data in it, but I need it to show as a 'zero' if there are no records showing in the table
This is for a form, or query and is the end result from a Sum([---]) equation
any help would be greatly appreciated
1. This code gives me a Zero but if there is data available, it still shows a zero and doesn't take the real value (i.e. $35.85) =IIf([Daily WO Query-mastercard].[Form]![TotalPayment],"0","")
2. This one will show a vaue if it has one, but an error is it doesn't: =IIf([Daily WO Query-mastercard].[Form]![TotalPayment],([Daily WO Query-mastercard].[Form]![TotalPayment]),"0")

-

 

[RuralGuy]

...RecordSource.RecordCount will tell you if there are any records. How anout IsError() function?

 

[RainLover]

Would setting the default value to Zero in the form work for you.

 

[U-Doo]

Its not in a table, its from a query as a Sum calculation. But that record does have a default of zero, but in order for that to take action, there has to be a record created each day
The issue is that there are no records, but i need the value to add in a total because i'm using 4 different query results to total (its for a debit machine, visa, master card, AMEX, debt, & cash) so if no one uses an AMEX is giving me an error because there's no data to add.
I have it working in a report, but need it to add in a form.

I'll check into the IsError() function, but not sure what that is.

 

[U-Doo]

OK, here is an update. This code works BUT, seems to crash Access:
=IIf(IsError([Daily WO Query-mastercard].[Form]![TotalPayment]),0,([Daily WO Query-mastercard].[Form]![TotalPayment]))
Any suggestions?

 

[RuralGuy]

It should not crash Access. Try *importing* your db into a fresh new db: http://www.btabdevelopment.com/ts/impnew

 

[U-Doo]

I did as you said and imported it into a new db; worked for about 5 clicks then got a crash and this popup
"Microsoft Office Access has stopped working
A problem casued the program to stop working correctly.
Windows will close the program and notify you if a solution is available"
Then it crashes and makes a backup copy of the database

 

[RainLover]

You have said "No Record". If the is no record then there is nothing there.
However if there is a Record with a Field that is blank then that would be a different story.

Which is your situation.

 

[RuralGuy]

I did as you said and imported it into a new db; worked for about 5 clicks then got a crash and this popup
"Microsoft Office Access has stopped working
A problem casued the program to stop working correctly.
Windows will close the program and notify you if a solution is available"
Then it crashes and makes a backup copy of the database

I would try a /decompile : http://www.granite.ab.ca/access/decompile.htm and then a Compact and Repair *before* doing a Compile.

 

[U-Doo]

@Rainlover - you are correct, there are no records for some of the forms. But the code works and gives me a zero value if there is no record (or has an #error) but also gives me the proper value if there is something entered. - but its crashing access.
I am trying to take a value that counts the total sales/services and subtracts the total payment amounts from visa (desperate form), mastercard (separate form), interac (seperate form), and cash(seperate form). So if there is no record for visa, it gives me a #error result. The code below appears to work but also crashes the DB if i navigate different dates using a calendar control
=IIf(IsError([Daily WO Query-mastercard].[Form]![TotalPayment]),0,([Daily WO Query-mastercard].[Form]![TotalPayment]))

@RuralGuy - tried the decompile and other then shrinking the file size a bit, it still crashes

 

[RainLover]

For now forget the Decomplie.

Instead disable the Calendar Control and manually insert the date.

If you no longer get a crash then you will know the cause is the Calendar.

 

[RainLover]

@Rainlover - you are correct, there are no records for some of the forms. But the code works and gives me a zero value if there is no record (or has an #error) but also gives me the proper value if there is something entered. - but its crashing access.

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

I am still a little confused.

Please tell me where this value comes from. WO Query-mastercard]

If you did a copy paste, which you should do rather than retype the code, then you are missing the opening Square Bracket.

As a bit of additional advise, which can wait until you fix this problem, go to my Signature where you will find a link to my Sky Drive.

Download the Document on Naming Conventions. You may find it helpful. It also helps others to understand your code when you post it here.

 

[U-Doo]

@Rainmaker:
I have a table that has all the records for each sale slip which has the $ value, discounts, gst, client, and payment method (The payment method is a picklist from another table - mastercard, visa, cash, debt, etc).
From this I use multiple queries to seperate the $ values by the payment method (1 query for each) and these go to their own form.
Now to combine all of these forms including forms from sales and another from services, i have a separate form that has all of these 7 forms on one page controlled by a calander control. When i select a date from teh calander control, it will show me all data for that day for sale, services, clients, total payments, gst, cashout etc. The total payments from each payment method comes from the applic. form (i.e. "Daily WO Query-visa" for the visa records, "Daily WO Query-mastercard" for the master card etc). If there is no record for one of them, it will show as an "#error" because there is no record; so i have a text block for each with this new IIf(IsError) equation. I'm trying to make that text come out with a zero value if no record is found, but it also needs to give a value if there is a record found. These text block all total and are subtracted from the total cashout. It works good (for months) and i've never had any issue with crashing until I tried to 'fix' the #error (because it worked well if I had at least one record for each payment method, but this showed up when i didn't have a record for one of them on day and it wouldn't total because one field came out as an error for no data). I should point out that I had a simple Sum equation before, the IIf(IsError) i just tried out lately to try and get things to total if there was no record.
Does that clarify things for you?

 

[RainLover]

Try this.

Create a new Text Box Control. Name it anything you like. eg txtCheckError.

Make its Source.

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

Go back to the original control with a Record Source of.
= IIF(IsError([txtCheckError]),0,[txtCheckError])

Again Check the syntax as I free typed and did not test.
I am reasonably sure this will work.

 

[U-Doo]

I tried your suggestion and it didn't crash but it also would not take any record that had data -always showed zero even though there was a record for 25.00.
(Note* when i put this code into 4 seperate text boxes (one for each category) the system eventually crashed)

 

[RainLover]

I tried your suggestion and it didn't crash but it also would not take any record that had data -always showed zero even though there was a record for 25.00.
(Note* when i put this code into 4 seperate text boxes (one for each category) the system eventually crashed)

The crashing can wait till we get the result.

Unfortunately you confused me with things like "Take" and "It"

Did the First Calaulation work when Data was there.

Where the result was Error in the first Text Box, did that convert to 0 (Zero) in the second Text Box.

A Decompile will be of no use in this situation. AFAIK the Decompile works on the VBA not on the SQL.

Please take an extra minute or two when replying to make sure things are clear.

Please also post your Statements for each of the two text boxes. Do a Copy Paste rather than retype, as you may make a mistake.

Rural Guy,

Where are you. I could use a second opinion.

 

[RuralGuy]

Rain,
I think you are zeroing in on the problem. I didn't want to interrupt the flow.

 

[RainLover]

Rain,
I think you are zeroing in on the problem. I didn't want to interrupt the flow.

OK

It was your's to start with, but I will follow through.No Problems.

But don't be a stranger. We still have to solve the crash. I can't see where a fairly simple Function could posibilly cause that.

There must be more than meets the eye.

 

[RuralGuy]

I agree and am following this thread closely. Believe me, if I think of something I'll jump in.

 

[RuralGuy]

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