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

[StarGrabber]

Complicated? Your are right, RainLover, but a few weeks ago I've seen even more complicated ones (in this forum)!

Nevertheless we have to find a solution here. U-Doo, you will find the one I developed attached. - The development included several changes / modifications. I...

- renamed the field Date to WODate in tables WO Main, WO Sales and WO Services
- adapted the queries and subforms concerned by that field
- renamed textbox Date to txtDate in form Daily Stats
- made minor code corrections due to this renamed textbox
- renamed textbox text36 to TotalServices in subform Daily Services Query (yes, I know, but I wanted to go with the "naming convention" of this application here!)
- renamed textbox Text112 to txtTotalPayements
- created various sum queries (noticeable by the prefix "qry")
- removed the control source of the corresponding sum textboxes on form Daily Stats
- added some code in the procedure SetDate_Click of the module from Daily Stats in order to fill these textboxes

You should have a look at the control source of Sales Tax: =([Sales Subtotal]+[Labour Subtotal])*Nz([GST])*100/100. I could not find the field(?) GST !

And like RainLover in post # 35 I strongly recommend you the use of a naming convention. You are giving us a pretty hard time!

 

[RainLover]

Complicated? Your are right, RainLover, but a few weeks ago I've seen even more complicated ones (in this forum)!

Nevertheless we have to find a solution here. U-Doo, you will find the one I developed attached. - The development included several changes / modifications. I...

- renamed the field Date to WODate in tables WO Main, WO Sales and WO Services
- adapted the queries and subforms concerned by that field
- renamed textbox Date to txtDate in form Daily Stats
- made minor code corrections due to this renamed textbox
- renamed textbox text36 to TotalServices in subform Daily Services Query (yes, I know, but I wanted to go with the "naming convention" of this application here!)
- renamed textbox Text112 to txtTotalPayements
- created various sum queries (noticeable by the prefix "qry")
- removed the control source of the corresponding sum textboxes on form Daily Stats
- added some code in the procedure SetDate_Click of the module from Daily Stats in order to fill these textboxes

You should have a look at the control source of Sales Tax: =([Sales Subtotal]+[Labour Subtotal])*Nz([GST])*100/100. I could not find the field(?) GST !

And like RainLover in post # 35 I strongly recommend you the use of a naming convention. You are giving us a pretty hard time!

Thanks for jumping in. Especially your comments on Naming Conventions.

 

[StarGrabber]

U-Doo, in the meantime I found another solution - without all this query stuff.

Use the ZDouble function instead of Nz, e.g.

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

 

[U-Doo]

Wow StarGrabber, thats amazing! I don't know much about SQL code but its not crashing anymore. I'll have to see what you did that prevented the crash. This will take me some time to figure it out really appreciate all the help you have all done for me. Thank you, thank you, thank you.

 

[StarGrabber]

You are welcome!

But please tell me what you found out regarding the field GST.

 

[U-Doo]

You are welcome!

But please tell me what you found out regarding the field GST.

GST is taxes, I just either omitted the column and forgot to delete the reference or forgot to add it.

 

[StarGrabber]

Ah, got it. Thanks for the info.

 

[narang]

In case you wish to display 0 when the RecordSource property of a form has no records. You may try the following steps

Let us say that the name of Textbox that displays the sum function is Text1.

You may add the following code in the Form Open event

if me.recordsetclone.recordcount=0 Then
me.controls("text1").value=0
else
me.controls("text1").controlsource="=sum(-----)"
end if

 

[StarGrabber]

Hi, narang, welcome to the forum!

As you surely have noticed, the search for a solution for U-Doo's problem has required a lot of effort. Several forum members like RuralGuy, RainLover and gemma-the-husky have spent a considerable amount of time in order to help U-Doo. And at the end there was success. If you read post # 44, you'll see that U-Doo is really pleased now by the support which was given to him. His application works fine.

If you think you have to make further contributions, please consider their value carefully before posting them.

If you would have tested your code then you would know that it is completely worthless. It does not work. At least not in U-Doo's application!

If you would have suggested

Private Sub Form_Current()

    If Me.RecordsetClone.RecordCount = 0 Then
        Me.Controls("Grand Total").Value = 0
    Else
        Me.Controls("Grand Total").Value = _
            DSum("Total", Me.RecordSource, "Date=" & Format(Me.Parent.Date, "\#yyyy-mm-dd\#"))
    End If

End Sub

then it would be different.

 

[RainLover]

StarGrabber

Thanks for the effort you put into the final solution.

Well Done.

Narang

Welcome to the forum. I liked your suggestion. The problem was a lot more complex than one would think at first glance.

 

[RuralGuy]

Star Grabber,

I'm with Rain here. Outstanding assistance.