DataType Conversion Speed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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.
 
Then divide by zero to conver to percent.
I'm sure you mean divide by 100

Allen Browne said:
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

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

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

John said:
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
Click to expand...
Click to expand...
 
Thanks, Klatuu.

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

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

Klatuu said:
Then divide by zero to conver to percent.
I'm sure you mean divide by 100

Allen Browne said:
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

John said:
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
Click to expand...
Click to expand...
Click to expand...
 
Dear Allen Browne,

I have temporarily solved the problem by changing the formula like this:
... IIf([discount2]>0,Str(nz([discount2])) & "% ","") & _
IIf([discount3]>0,Str(nz([discount3])) & "% ","").....

Thank you for your help. And also thanks for Klatuu and Douglas J Steele.

Rgs,
John




Allen Browne said:
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

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

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

John said:
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
Click to expand...
Click to expand...
 
Back
Top