Strange Action in Public Function

[bw]

I have a field [BillingAmt] that comes from the same table as [WaivedAmt].

I have a Public Function as follows:

Public Function TheBillingTotal()
If BillDate < #10/18/2006# Then
If Nz([WaivedAmt]) = Nz([BillingAmt]) Then
TheBillingTotal = 0
Else
TheBillingTotal = Nz([BillingAmt])
End If
Else
TheBillingTotal = Nz([Meals]) + Nz([Airfare]) + Nz([Hotel])
End If
End Function

In stepping through the code, I see that: [BillingAmt]=MSAccess can't
find the field "BillingAmt" referred...

I have, by experimenting found that if I, in another Public Function, define
a variable Test as a variant, then insert the following code:
Test=Nz(BillingAmt]), then the Public Function TheBillingTotal works Okay???

Can someone explain why I need this weird work around to get my function to
work? Why can't Access find [BillingAmt]?

By the way, in the whole report, this Public Function is the only place that
[BillingAmt] is referenced.

Thanks,
Bernie

 

[Duane Hookom]

Two comments:
1) consider always using a default value in Nz(). I don't leave values to
chance
If Nz([WaivedAmt],0) = Nz([BillingAmt],0) Then
2) BillingAmt must be bound to a control in the report if you want reference
the value in the field.

 

[bw]

Thanks for the reply, Duane.

Your number 2 solved the problem, and of course, I don't understand why.
Both your solution and my "work around" seem to be pretty strange.

And by the way, I didn't know you could use a default value in Nz().

Thanks for your help!
Bernie

Two comments:
1) consider always using a default value in Nz(). I don't leave values to
chance
If Nz([WaivedAmt],0) = Nz([BillingAmt],0) Then
2) BillingAmt must be bound to a control in the report if you want
reference the value in the field.

--
Duane Hookom
MS Access MVP

I have a field [BillingAmt] that comes from the same table as [WaivedAmt].

I have a Public Function as follows:

Public Function TheBillingTotal()
If BillDate < #10/18/2006# Then
If Nz([WaivedAmt]) = Nz([BillingAmt]) Then
TheBillingTotal = 0
Else
TheBillingTotal = Nz([BillingAmt])
End If
Else
TheBillingTotal = Nz([Meals]) + Nz([Airfare]) + Nz([Hotel])
End If
End Function

In stepping through the code, I see that: [BillingAmt]=MSAccess can't
find the field "BillingAmt" referred...

I have, by experimenting found that if I, in another Public Function,
define a variable Test as a variant, then insert the following code:
Test=Nz(BillingAmt]), then the Public Function TheBillingTotal works
Okay???

Can someone explain why I need this weird work around to get my function
to work? Why can't Access find [BillingAmt]?

By the way, in the whole report, this Public Function is the only place
that [BillingAmt] is referenced.

Thanks,
Bernie

 

[Duane Hookom]

No, you can't set the default to Nz() without at least one value. What I
thought I wrote was that Nz(....) should have 2 arguments. The first is the
expression to test for null and the second is the value to return if the
first is null. I suggested you replace:
If Nz([WaivedAmt]) = Nz([BillingAmt]) Then
with
If Nz([WaivedAmt],0) = Nz([BillingAmt],0) Then

--
Duane Hookom
MS Access MVP

Thanks for the reply, Duane.

Your number 2 solved the problem, and of course, I don't understand why.
Both your solution and my "work around" seem to be pretty strange.

And by the way, I didn't know you could use a default value in Nz().

Thanks for your help!
Bernie

Two comments:
1) consider always using a default value in Nz(). I don't leave values to
chance
If Nz([WaivedAmt],0) = Nz([BillingAmt],0) Then
2) BillingAmt must be bound to a control in the report if you want
reference the value in the field.

--
Duane Hookom
MS Access MVP

I have a field [BillingAmt] that comes from the same table as
[WaivedAmt].

I have a Public Function as follows:

Public Function TheBillingTotal()
If BillDate < #10/18/2006# Then
If Nz([WaivedAmt]) = Nz([BillingAmt]) Then
TheBillingTotal = 0
Else
TheBillingTotal = Nz([BillingAmt])
End If
Else
TheBillingTotal = Nz([Meals]) + Nz([Airfare]) + Nz([Hotel])
End If
End Function

In stepping through the code, I see that: [BillingAmt]=MSAccess can't
find the field "BillingAmt" referred...

I have, by experimenting found that if I, in another Public Function,
define a variable Test as a variant, then insert the following code:
Test=Nz(BillingAmt]), then the Public Function TheBillingTotal works
Okay???

Can someone explain why I need this weird work around to get my function
to work? Why can't Access find [BillingAmt]?

By the way, in the whole report, this Public Function is the only place
that [BillingAmt] is referenced.

Thanks,
Bernie