spooky

[Mcgrco]

Hi

Got a strange one:

In a query Ive put the function below.

AccRef: IIf(IsNumeric([BP3]),Format([BP3],"000",[BP3]))

the problem is if the field BP3 contains eg 3D4, access appends it as 30000. It also classes it as numeric rather than string. Both field in both tables are text . Can anyone think of away round it. I think it has something to do with acess trying to truncate large numbers.

Strange, very strange


Any help is apprciated

 

[ritchieroo]

I've never come across this 'D' thing before, but it seems to be the same as 'E' (i.e. Exponentiation)

I tried typing these into the immediate window...

? 3D4
30000

? 3E4
30000

? 3e-4
0.0003

The main problem is the IsNumeric function, which just determines whether a value can be interpreted as numeric (and all of these can).

?IsNumeric("3D4")
True

I was having trouble figuring out how to get around the issue, and then it came to me...

?IsNumeric("3D43D4")
False

so this should work for you...

IIf(IsNumeric([BP3] & [BP3]),Format([BP3],"000"),[BP3])