Calling a function

[Sandra]

Hello -

I need to access a roundup function, for which I have found several examples.

I have the following DLookup value that i want to roundup the result for:

=(DLookUp(" [ChCrAmt]","t_ChargesCredits","[ChCr_ID]= " &
[ChCrID]))+(DLookUp(" [CourseFee]","t_Courses","[Course_ID]= " &
[CourseID]))*(DLookUp(" [ChCrperc]","t_ChargesCredits","[ChCr_ID]= " &
[ChCrID]))*-1

I created the function and added it to a module but I am not sure how to
apply it to this value.

I tried =Roundup((DLookUp(" [ChCrAmt]","t_ChargesCredits","[ChCr_ID]= " &
[ChCrID]))+(DLookUp(" [CourseFee]","t_Courses","[Course_ID]= " &
[CourseID]))*(DLookUp(" [ChCrperc]","t_ChargesCredits","[ChCr_ID]= " &
[ChCrID]))*-1)

but I am getting a "wrong data type" error

The field I am using this on is a currency field.

The function in the module is as follows:
Public Function Roundup(dblNumber As Double, varRoundAmount As Double, _
Optional varUp As Variant) As Double

Dim dblTemp As Double
Dim lngTemp As Long

dblTemp = dblNumber / varRoundAmount
lngTemp = CLng(dblTemp)

If lngTemp = dblTemp Then
Roundup = dblNumber
Else
If IsMissing(varUp) Then
' round down
dblTemp = lngTemp
Else
' round up
dblTemp = lngTemp + 1
End If
Roundup = dblTemp * varRoundAmount
End If


End Function


Any help much appreciated.
sandra

 

[Allen Browne]

Your RoundUp() function accepts data of type Double. None of the VBA data
types can handle null, so it's going to fall over when a Null is passed in.

I would have expected Error 94 though (invalid use of Null), rather than
Error 13 (Type Mismatch.)

In any case, the simplest way to round up it to negate, chop of the fraction
part, and negate again, i.e.:
- Int( - [put your expression here])

Details of this and other options for rounding in Access:
http://allenbrowne.com/round.html

 

[Sandra]

Thanks Allen -

The following edit worked:

=Int(100*((DLookUp(" [ChCrAmt]","t_ChargesCredits","[ChCr_ID]= " &
[ChCrID]))+((DLookUp(" [CourseFee]","t_Courses","[Course_ID]= " &
[CourseID]))*(DLookUp(" [ChCrperc]","t_ChargesCredits","[ChCr_ID]= " &
[ChCrID])))*-1))/100

Much appreciated,
sandra

Your RoundUp() function accepts data of type Double. None of the VBA data
types can handle null, so it's going to fall over when a Null is passed in.

I would have expected Error 94 though (invalid use of Null), rather than
Error 13 (Type Mismatch.)

In any case, the simplest way to round up it to negate, chop of the fraction
part, and negate again, i.e.:
- Int( - [put your expression here])

Details of this and other options for rounding in Access:
http://allenbrowne.com/round.html


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello -

I need to access a roundup function, for which I have found several
examples.

I have the following DLookup value that i want to roundup the result for:

=(DLookUp(" [ChCrAmt]","t_ChargesCredits","[ChCr_ID]= " &
[ChCrID]))+(DLookUp(" [CourseFee]","t_Courses","[Course_ID]= " &
[CourseID]))*(DLookUp(" [ChCrperc]","t_ChargesCredits","[ChCr_ID]= " &
[ChCrID]))*-1

I created the function and added it to a module but I am not sure how to
apply it to this value.

I tried =Roundup((DLookUp(" [ChCrAmt]","t_ChargesCredits","[ChCr_ID]= " &
[ChCrID]))+(DLookUp(" [CourseFee]","t_Courses","[Course_ID]= " &
[CourseID]))*(DLookUp(" [ChCrperc]","t_ChargesCredits","[ChCr_ID]= " &
[ChCrID]))*-1)

but I am getting a "wrong data type" error

The field I am using this on is a currency field.

The function in the module is as follows:
Public Function Roundup(dblNumber As Double, varRoundAmount As Double, _
Optional varUp As Variant) As Double

Dim dblTemp As Double
Dim lngTemp As Long

dblTemp = dblNumber / varRoundAmount
lngTemp = CLng(dblTemp)

If lngTemp = dblTemp Then
Roundup = dblNumber
Else
If IsMissing(varUp) Then
' round down
dblTemp = lngTemp
Else
' round up
dblTemp = lngTemp + 1
End If
Roundup = dblTemp * varRoundAmount
End If


End Function


Any help much appreciated.
sandra