Why is this a number?

[Dave F.]

Hi

I'm using VBA 6.3

If I pass 45- to this:

If IsNumeric(OffsetNo) = False Then
...

it returns True.

1) Why?

2) How to I prevent this happening.

Thanks in Advance.

Dave F.

 

[Tony Jollans]

Hi Dave,

1) Because it is numeric!

2) You can't!

The best way of proceeding may well depend on the type of values you are
expecting but you may find the Val function useful - or perhaps make use of
the CDbl function (trapping errors) - or maybe using the two together and
comparing the results.

 

[Dave F.]

Thanks for replying

I'll look into what you've said.

I'm trying to allow only positive numeric values being typed & displayed in
a textbox:

If IsNumeric(OffsetNo) = False Then ' if there is a value in the
textbox & it's not a number
OffsetNo = Left$(OffsetNo, Len(OffsetNo) - 1) ' remove last typed
TxtBx_Dist.Value = OffsetNo ' & put it back in the box
End If

Is there a better overall way of doing this?

Cheers
Dave F.

 

[Tony Jollans]

Hi Dave,

From the look of your code you have it in a Change Event and you are falling
into the trap that most people do of assuming

(a) A change is always a single character and
(b) More significantly, a change is always at the end of the value

Personally, as a user, I hate this kind of keystroke censorship - even when
considerately done - and would prefer validation on exit.

Whatever you do, you need to understand what constitutes a number - for
example 1E3 (=1000) - and then decide what you really want to accept.

If you want the input to be a string of digits, you really need to fairly
explicitly check for it - a number (as far as VBA is concerned) may contain,
at least, any digit, a minus sign (or maybe two), a plus sign (or two), an
"E", and/or a decimal separator (decimal point or comma).

 

[TC]

(untested)

dim s as string ' not as numeric.
s = "45-" ' whatever.
if s like "*[!0-9]*" then
' the value contains one or more characters
' OTHER-THAN the decimal digits 0-9
else
' the value contains ONLY the decimal digits
' (or is empty).
endif

HTH,
TC [MVP Access]

 

[Dave F.]

Thanks Top Cat. That appears to sort it.

 

[TC]

No probs, glad it helped.

Haven't been called "top cat" for years ...

TC [MVP Access]