Code Help (Access 97)

Topic

New Reply

I use the code below on a report. The code works fine as long as a value is enter in any of the fields [24 Hrs],[48 Hrs],[72 Hrs],[1 Week],[More Than 1 Week]. If there is no value it gives me this debug error. “Run time error 13” , Type mismatch. Is there a way I can modify this so that it can handle the null values? Thanks…

Private Sub Report_Activate()
Dim var24Hrs As Integer
Dim var48Hrs As Integer
Dim var72Hrs As Integer
Dim var1Wk As Integer
Dim varMoreThan1Wk As Integer

var24Hrs = IIf(IsNull([24 Hrs]), 0, [24 Hrs])
var48Hrs = IIf(IsNull([48 Hrs]), 0, [48 Hrs])
var72Hrs = IIf(IsNull([72 Hrs]), 0, [72 Hrs])
var1Wk = IIf(IsNull([1 Week]), 0, [1 Week])
varMoreThan1Wk = IIf(IsNull([More Than 1 Week]), 0, [More Than 1 Week])

[24 Hrs] = [EIR Total] – var48Hrs – var72Hrs – var1Wk – varMoreThan1Wk

End Sub

Reply | Quote

Replies

In witch line do you get the error ?
What is the meaning of doing calculations in the on activate event?
Can you explain a little more what you are trying to do ?

Reply | Quote

I agree with Francois. You’d normally do calculations in the Format event of the Detail section or of group headers or footers. Aside from that, what kind of values *should* be in these fields and what are you calculating? For instance, what kind of a value would you expect to find in a field called [More Than 1 Week], a Yes/No, a count of something, what?

Reply | Quote

try using the NZ function rather than the iif statement that you are using

ie
Private Sub Report_Activate()
Dim var24Hrs As Integer
Dim var48Hrs As Integer
Dim var72Hrs As Integer
Dim var1Wk As Integer
Dim varMoreThan1Wk As Integer

var24Hrs = nz([24 Hrs]), 0)
var48Hrs = nz([48 Hrs]), 0)
var72Hrs = nz([72 Hrs]), 0)
var1Wk = nz([1 Week]), 0)
varMoreThan1Wk = nz([More Than 1 Week]), 0)

[24 Hrs] = nz([EIR Total],0) – var48Hrs – var72Hrs – var1Wk – varMoreThan1Wk

End Sub

what datatypes are the fields, the iif statement you posted and the nz function just check for null. Are the fields numeric or is it possible that you are passing a string value for a calculation.

Reply | Quote