DataType Conversion Speed

J

John

Hi all,

I have a problem that needs help. I have a report that needs to show
some discounts but these discount are not always contain a value. So, they
are concatenated using the formula like this:

…… IIf([discount2]>0,Str([discount2]) & "% ","") &
IIf([discount3]>0,Str([discount3]) & "% ","")……

However, user found that if the discount starts with "0" such as 0.25%,
the print out will be ".25%" with the starting 0 missed.

So, I have written a function in the module. It accepts a currency
variable, converts the input to string and check if the first 2 character are
" .". If so, add a "0" in front of it.

When I run the report again. I found it run very very slow.

Consequently, I want to ask if there are any alternatives instead of
writting a function to do this?

If a function is really required, how can I speed up the report printing?

The module written by me is as follows:

Function CurrencyToString(inCurrency As Currency) As String

CurrencyToString = Str(inCurrency)
If Left(CurrencyToString, 2) = " ." Then
CurrencyToString = " 0" & LTrim(CurrencyToString)
End If

End Function


Thanks for your attention.

Rgs,
John
 
A

Allen Browne

I assume:
- discount2 and discount3 are Number type fields,
- the value 10 means 10%,
- if neither field contains a value, the discount is 0,
- if either field contains a value, that's the discount,
- if both fields contain a value, the sum is the discount.

Try entering something like this in a fresh column of the Field row in
query design:
CDbl(Nz([discount2],0) + Nz([discount3],0) / 100)

The Nz() converts null to zero.
It then sums the two.
Then divide by zero to conver to percent.
Then expllicitly typecast to double to prevent the problem described in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Without the user-defined function calls and converting to string and back
again, that should fix both the performance issues and the
misinterterpretation of the results. To display the result as a percent, set
the Format property of your text box to:
Percent
 
D

Douglas J Steele

Allen's given you some good advice. Looking specifically at the function you
created, the Format function would probably have been more appropriate. Note
that the Format function can have up to 4 parameters: one format for
positive values, one for negative values, one for values of 0 and one for
Null values.
 
Top