Zero doesn't equal Zero

[DOSRoss]

I have a TEXT string "Elapsed time 609ms" called 'Field4'
I first find the 'ms' with - Loc1: InStr([field4],"ms") ... this returns 17, the beginning location of where the search characters are.

I have a column


IsNum2: IIf(Val(Mid([Field4],[Loc1]-2,1))=0,'no','yes') which is saying ... Does the Value of Location 17-2 = 0 ... which should be 'yes'.
When I would expect the IIF statement to say 'yes' - meaning '0' is a number, it does not. It comes back equating to the 'no' side of the IIF.
The 6 and the 9 are found as 'yes' though.

Why does '0' not equal '0' as a digit?

[ItsMe]

If a number is within a function, it will, typically, be evaluated as a number. SQL will evaluate as literal text. Neither of these two nuances is your issue. Your field is type Text. So, you need to pass it as text. Try = '0'

[DOSRoss]

If a number is within a function, it will, typically, be evaluated as a number. SQL will evaluate as literal text. Neither of these two nuances is your issue. Your field is type Text. So, you need to pass it as text. Try = '0'

I do have the VAL() function in there to evaluate the TEXT of '0' to the Number of 0 ...
Are you saying that you would not expect it to do that?

[DOSRoss]

I do have the VAL() function in there to evaluate the TEXT of '0' to the Number of 0 ...
Are you saying that you would not expect it to do that?

The IIF statements with VAL() function evaluate the actual numbers correctly, but say 'no' to "0".
I am expecting the VAL() function to evaluate the '0' to a Number 0 ...

In the end, after five nested IIF statements get to a blank or space, parsing backwards from the 'ms' location
... then take all the found digits, put them together and come up with a Whole number.
So '9901' would be 9901. But if the evaluation blows out at the '0', I won't come up with the correct number.
Probably nested IIF's aren't my best way exactly, but I am importing the data into Access and need a query that gets the whole number ... eventually.

Extract Number1
ID	Field1	Field4	Loc1	IsNum1	Num1	IsNum2	Num2	IsNum3	Num3	IsNum4	Num4	IsNum5	Num5
85	2015/04/23	Elapsed time 9901ms	18	yes	1	no	0	yes	9	yes	9	no	0
86	2015/04/23	Elapsed Time 1230ms	18	no	0	yes	3	yes	2	yes	1	no	0
89	2015/04/23	Elapsed Time 7ms	15	yes	7	no	0	no	0	no	0	no	0
93	2015/04/23	Elapsed time 1002ms	18	yes	2	no	0	no	0	yes	1	no	0
94	2015/04/23	Elapsed time: 10113ms	20	yes	3	yes	1	yes	1	no	0	yes	1

Thank you for your help!

[DOSRoss]

I had highlighted the '0's and 'no's in the post but it doesn't appear to have saved that way.

In record 1, the location of 'ms' is found using Instr() function.
Then I proceed to parse backwards testing to find a 'non-number'
So the ms 'Loc1' is 18, so the VAL(Loc1-1) would be 1, then VAL(Loc1-2) etc until I get to a 'space'
This should provide me a way to get a whole number out of the text.

[ItsMe]

I do have the VAL() function in there to evaluate the TEXT of '0' to the Number of 0 ...
Are you saying that you would not expect it to do that?

To be honest, I did not even pay much attention to the fact that you used Val(). My point is, it is a text field and you are trying to extract a character from within a string. So, treat it as text.

It seems you are using the Val() function after you retrieve actual number characters (using Instr() to get the characters and then Val ). That sounds appropriate but...

This is problematic when using Alias' in Queries. When you execute a query/SQL, it will not evaluate an expression within an alias to, then, evaluate a second, aggregate, expression.

In order to accomplish this, you need to use another query and execute one expression in this query. Get the aggregate calc using subqueries. However, you may be able to avoid all of that.

Evaluate the character as literal text and do not try to convert it to a number. In other words = "0" or = '0'

Use ='0' within your InStr function

[John_G]

In your statement: IIf(Val(Mid([Field4],[Loc1]-2,1))=0,'no','yes'), you have the "no" and "yes" reversed.

It is giving you a "no" if the middle digit IS 0 !

[CJ_London]

IsNum2: IIf(Val(Mid([Field4],[Loc1]-2,1))=0,'no','yes') which is saying ... Does the Value of Location 17-2 = 0 ... which should be 'yes'.

but the code says 'no'

Note the OP seems to have abandoned this thread, despite solutions being posted and not acknowledged

https://www.accessforums.net/program...tml#post273417

[ItsMe]

Thanks Ajax