Coding for Sum in vba

[glen]

Hello,

=Nz(Sum([field1]),0)

If we want to write this expression in vba, then what coding will be used?

Secondly,

I am using this procedure for my combo box to show records of sub form.

Private Sub CBOBATCH_AfterUpdate()
'Find the that matches the control
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[batchno]='" & Me![CBOBATCH] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

It working fine. But when the combo is empty and I press enter then it give error :3021 no current record.
how to resolve this problem?
Thanks

[ssanfu]

Check to see if batch number is not NULL

Private Sub CBOBATCH_AfterUpdate()
'Find the that matches the control
   Dim rs As Object
   Set rs = Me.Recordset.Clone

   'check if there is a batch number
   If Len(Trim(Me.CBOBATCH)) > 0 Then
      rs.FindFirst "[batchno]='" & Me![CBOBATCH] & "'"
      If Not rs.EOF Then
         Me.Bookmark = rs.Bookmark
      End If
   Else
      Msgbox "First select a batch number"
   End If

End Sub

[ssanfu]

Hello,

=Nz(Sum([field1]),0)

If we want to write this expression in vba, then what coding will be used?

Oops, I missed this.

Need more context. What code do you have now?

[glen]

Thank you very much.
This problem is solved.
Plz tell m about the sum coding in vba.
If we want to write the sum procedure in vba.what will be procedure.
and one another problem.
I have an unbound text box where I write this expression for percentage.
=nz([filed1]/[field2],0)
It working fine but when ever field1 and field2 both are "0" then it showing #Num!
I want to show "0" if the values of both fields are zero.
Thanks

[glen]

Oops, I missed this.

Need more context. What code do you have now?

I am using this expression for a Grand total of a column.
and it working for me.
But I just want to know that if I want to create a vba code for grand total of a column then what code will be used?

[ssanfu]

Plz tell m about the sum coding in vba.
If we want to write the sum procedure in vba.what will be procedure.

Again, without more info I don't know what to tell you. You could use a totals query or you could create a recordset and loop through the records adding the values for [field1].

Pretend you met a stranger on the street. Explain what you are trying to do.

=nz([filed1]/[field2],0)

It is displaying #Num because if [field2] is 0, you get a "Divide by zero" error

You could try:

=NZ([filed1],0) / NZ([field2],1)

[glen]

I tried your expression but still same problem.

=NZ([filed1],0) / NZ([field2],1)

[ssanfu]

did you correct thefield name??

=NZ([filed1],0) / NZ([field2],1)

Should be field1..... (assuming that you have field names like field1. field2,....)

[glen]

Yes It is correct.
bcoz my real fields name are different.
=nz([dlvdqty],0) / nz([reqqty],1)

[ssanfu]

The NZ() Function only handles Nulls. It will not change the zero in the denominator to a one. Try this expression:

=IIf(IsNull([dlvdqty]) Or IsNull([reqqty]) Or [reqqty]=0,0,[dlvdqty]/[reqqty])

[glen]

It working fine now.Thanks
Now I have one another question. If you don't mind.
I have another unbound text box there.
which will show status.that the order is complete or Under process.
like this : If text box of percentage is = 100% , "Complete","Under Process".
how we will write this expression [percentage]= 100% ?

[ssanfu]

Something like:

=IIF([percentage]= 100,"Complete","Under Process")

It depends if [percentage] is a text box, or how the calculation is done.

[glen]

I was also writing like this.
But the problem is that there is % sign with 100 So I m not sure that how to make expression for this.
The result always returns with false part even the text box of percentage is equal to 100%.

Note: and this expression is used in the text box of percentage.
=IIf(IsNull([delm]) Or IsNull([reqm]) Or [reqm]=0,0,[delm]/[reqm])

[ssanfu]

A percentage is a number that is between 0 and 1. So .80 = 80%. It doesn't matter is it is 80/100 or 160/200, the result is still 80% or .8

The expression in the text box should be:

=IIF([percentage]= 1,"Complete","Under Process")

[glen]

Thank you very much.
Problem solved.