double type

[Guest]

I have a range of double values that I need to compare and assign a label
depending on that value. For example, if the value falls in the range of 4.7
To 5.0, I will assign an "A" into a textbox or a label box. I use the Case
Select but it does not work. My questions are:

1. Can I use Case Select or any comparison operator to compare a double value?
2. When I type the 5.0 in the code zone of VBA, it becomes 5#. Why??

Thank you for your help in advance

 

[Douglas J. Steele]

You're saying "Case Select", but the syntax is "Select Case". Might that be
your problem?

# is a short-hand way of declaring a value (or variable) as being double.
Other possibilities are 5! to ensure that it's a Single, 5% to ensure it's
an Integer, or 5& to ensure it's a Long.

 

[Guest]

Thank you for your help. Yes, I did use the Select Case and after testing, it
has worked fine except the # sign. I did not "short-hand" to declaring a
value. My code as following
Case 4.7 To 5.0
lblSample.Caption = "A"
...
Case 1.0 To 1.6
lblSample.Caption = "F"
Case Else
lblSample.Caption = "Please enter the score and click on this box"
End Select

When I was typing, "Case 4.7 To 5.0", the 5.0 number automatically becomes
5#. Same as 1.0 becomes 1#. When I test the code, the value 5.0 will be
skipped (not recognized). How can I keep the number 5.0 and 1.0 in the coding
as they should be? Why does VBA in Access automatically convert a X.0 to a X#?
Thank you for your help.
tim

 

[Allen Browne]

Tim, VBA stores the literal value in your code as a double quite correctly,
even though the display might not be what you expect.

As Doug explained, the # is the type declaration character for a Double. If
you just type 5, VBA assumes it to be an integer. If you type 5.0, VBA
assumes you want a fractional value, so gives you a double. The trailing #
is the type declaraction character that indicates the value is not an
Integer nor a Single (since 5.0 is ambiguous - it could be a single or a
double.)

These arcane type declaration characters are a hang-over from very early
days of BASIC, where you would declare a string like this:
Dim A$
Almost no-one uses them now, though they are still useful for literal values
in your code.

 

[Guest]

Hi Allen,
Thank you for pointing out the mystery of the #. Surprisingly, although my
code won't work right when it encounters 5#, it does fine with 1#. I have no
idea why. Do you think I should declare a string type variable to hold 5.0
and do the string comparison ? I would need a sample about this.
Again, my code as following. I use the Format$ because I want to force the
number to display only one digit after decimal to do the comparison.

Select Case Format$(txtTotal.Value, "0.0")
Case 4.7 To 5#
lblSample.Caption = "A"
------
Case 1# to 1.6
lblSample.Caption = "F"
Case Else
lblSample.Caption = "Please enter the score and click on this box"
End Select

Thank you for your time and help.
Tim

 

[Allen Browne]

The output of the Format() function is Text. So when you use Format() in
your select case, you are getting a Text comparison. In a string comparsion
"11.6" is greater than "100".

If the problem is caused by rounding, you might be able to solve it with:
Select Case Round(txtTotal.Value, 1)
or even:
Select Case CCur(Nz(Round(txtTotal.Value, 1),0))

 

[Guest]

I use Select Case Round(txtTotal.Value, 1) and it works! Thank you very much
for your excellent help. I appreciate it!
Tim

 

[onedaywhen]

I have a range of double values that I need to compare and assign a label
depending on that value. For example, if the value falls in the range of 4.7
To 5.0, I will assign an "A" into a textbox or a label box. I use the Case
Select but it does not work.

If you are only interested in 1 decimal place, consider changing data
type to DECIMAL with a scale of 1.

DOUBLE is a floating/approximate/vague/inaccurate data type, whereas
DECIMAL is fixed/exact/precise/accurate data type. Which description
best fits your data? Given the choice, which would your customer pick?

Jamie.

--