text null or number

[Guest]

I have a query that is based on a text field, however, the value of the field
could on any occasion be actual text, or null or a number with or without
deicmal places,

How can i verify easily between text and number within a query usi9ng an iif
statement

 

[Guest]

Result = IIf(IsNull(A), "Null", IIf(Val(Nz(A, 0)) = A, "Number", "Text"))

This first IIf checks to see if A is null. If not, the second IIf checks to
see if the Value of A is the same as A (this is true only of numbers, not of
text). If neither are true, it must be text.

The Nz in there is because the expression will attempt to evaluate Val(Null)
when A is null, even though it is irrelevant because the first IIf evaluates
to 'True'. Without it, you will probably get Invalid Use of Null errors.

 

[Guest]

Thanks for that
--
Regards


Patrick Stubbin

Result = IIf(IsNull(A), "Null", IIf(Val(Nz(A, 0)) = A, "Number", "Text"))

This first IIf checks to see if A is null. If not, the second IIf checks to
see if the Value of A is the same as A (this is true only of numbers, not of
text). If neither are true, it must be text.

The Nz in there is because the expression will attempt to evaluate Val(Null)
when A is null, even though it is irrelevant because the first IIf evaluates
to 'True'. Without it, you will probably get Invalid Use of Null errors.

 

[Guest]

I guess you could see that I didn't actually run this in a query. I just ran
it in VBA (which treats the IIf the same as the query). I was just using
MsgBox Result on the next line of my VBA to display the results. I hope that
didn't cause any confusion. In your query, the field entry might look more
like this:

DataType: IIf(IsNull(A), "Null", IIf(Val(Nz(A, 0)) = A, "Number", "Text"))

 

[John Vinson]

I have a query that is based on a text field, however, the value of the field
could on any occasion be actual text, or null or a number with or without
deicmal places,

How can i verify easily between text and number within a query usi9ng an iif
statement

Just as an alternative to Brian's sneakily elegant suggestion, try

Iif(IsNumeric([fieldname]), <something>, <something else>)

John W. Vinson[MVP]

 

[Guest]

Thanks, John. I just knew there was something else out there. I even tried
IsNumber (which, of course, did not show up in the help files) before I made
the alternative. So close, and yet so far...

I have a query that is based on a text field, however, the value of the field
could on any occasion be actual text, or null or a number with or without
deicmal places,

How can i verify easily between text and number within a query usi9ng an iif
statement

Just as an alternative to Brian's sneakily elegant suggestion, try

Iif(IsNumeric([fieldname]), <something>, <something else>)

John W. Vinson[MVP]

 

[Guest]

thankyou genteman, for your interest, this is the final query
----------------------------------------------------------------------

TRANSFORM First(iif(IsNull([result]),"Waiting Result", IIf(Val(Nz([result],
0))=[Result],
IIf(nz([Result])<[firstoflod],"< " & [firstoflod],IIf(nz(Result)<1,
Format$(nz(Result),"Fixed"),IIf(nz(Result)<10,
Format$(Result,[SF]),IIf(nz(Result)<100,Format$((Result),"00"),IIf(nz(Result)<1000,
Format$(CInt(Int((Result)*1+0.5)/10)*10,"000"),IIf(nz(Result)<10000,
Format$(CInt(Int((Result)*1+0.5)/100)*100,"0000"),IIf(nz(Result)<100000,
Format$(CInt(Int((Result)*1+0.5)/1000)*1000,"00000"),IIf(nz(Result)<100000,
Format$(CInt(Int((Result)*1+0.5)/1000)*1000,"00000"),IIf(nz(Result)<1000000,
Format$(CInt(Int((Result)*1+0.5)/10000)*10000,"000000")))))))))),[result])))
AS FirstOfResult
SELECT a3.[Batch Number], a3.[Sample Number], a3.Expr1, a3.Expr2, a3.[Sample
Identity]
FROM a3
GROUP BY a3.[Batch Number], a3.[Sample Number], a3.Expr1, a3.Expr2,
a3.[Sample Identity]
ORDER BY a3.[Batch Number], a3.[Sample Number], a3.ColumnAlias
PIVOT a3.ColumnAlias In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)
----------------------------------------------------------------------------------------------
--
Regards


Patrick Stubbin

Thanks, John. I just knew there was something else out there. I even tried
IsNumber (which, of course, did not show up in the help files) before I made
the alternative. So close, and yet so far...

I have a query that is based on a text field, however, the value of the field
could on any occasion be actual text, or null or a number with or without
deicmal places,

How can i verify easily between text and number within a query usi9ng an iif
statement

Just as an alternative to Brian's sneakily elegant suggestion, try

Iif(IsNumeric([fieldname]), <something>, <something else>)

John W. Vinson[MVP]

 

[tina]

gotta love those Access Help files, especially the wonderful improvements in
A2003 that threw out the Index option!

Thanks, John. I just knew there was something else out there. I even tried
IsNumber (which, of course, did not show up in the help files) before I made
the alternative. So close, and yet so far...

I have a query that is based on a text field, however, the value of the field
could on any occasion be actual text, or null or a number with or without
deicmal places,

How can i verify easily between text and number within a query usi9ng an iif
statement

Just as an alternative to Brian's sneakily elegant suggestion, try

Iif(IsNumeric([fieldname]), <something>, <something else>)

John W. Vinson[MVP]

 

[John Vinson]

gotta love those Access Help files, especially the wonderful improvements in
A2003 that threw out the Index option!

.... and the utterly inept "search assistant" who could not find its
own... well, we won't go there!

John W. Vinson[MVP]

 

[Guest]

what have i started!
--
Regards


Patrick Stubbin