IIF Statement

[ricky]

Hi

Probably a simple query, but how do I check for the start of a field begins
with a numeric.

i.e

SELECT
Table1.ID1,
Table1.field1,
Table1.field2,
Table1.field3,

IIf((Left([Table1].[field3],1) Between 0 And 9),"numeric",[Table1].[field3])
AS Expr1

FROM Table1;

Bascially, I just want to flag the records, which have been corrupted,
however when running this query, it places "numeric" in the field correctly
for the TRUE portion of the IIf statement, but I get an #Error, for the
other fields, which do not start with a numeric?

Any ideas?

Kind Regards

Ricky

 

[John Spencer]

Your posted expression has unbalanced parentheses. Two "(" and three ")"
IIf((Left([Table1].[field3],1) Between 0 And
9),"numeric",[Table1].[field3])AS Expr1

IIf(Left([Table1].[field3],1) Between 0 And 9,"numeric",[Table1].[field3])AS
Expr1

Of course if you just want to identify those records where field3 starts
with a number you could use criteria in the query of
WHERE Table1.Field3 Like "#*"

Alternative expressions:
IIf(Table1.Field3 Like "#*" ,"Numeric",Table1.Field3) as Expr1

IIf( IsNumeric(Left([Table1].[field3],1)),"Numeric",[Table1].[field3]) AS
Expr1

 

[ricky]

Hi John

thanks for you email, the statement that you suggested with the keyword
"ISNUMERIC" did the trick, thanks for that, I'd been playing around with
different combinations for almost an hour.....by the way, do you happen to
know what was wrong with my logic?

Kind Regards

Ricky

Your posted expression has unbalanced parentheses. Two "(" and three ")"
IIf((Left([Table1].[field3],1) Between 0 And
9),"numeric",[Table1].[field3])AS Expr1

IIf(Left([Table1].[field3],1) Between 0 And 9,"numeric",[Table1].[field3])AS
Expr1

Of course if you just want to identify those records where field3 starts
with a number you could use criteria in the query of
WHERE Table1.Field3 Like "#*"

Alternative expressions:
IIf(Table1.Field3 Like "#*" ,"Numeric",Table1.Field3) as Expr1

IIf( IsNumeric(Left([Table1].[field3],1)),"Numeric",[Table1].[field3]) AS
Expr1

Hi

Probably a simple query, but how do I check for the start of a field
begins
with a numeric.

i.e

SELECT
Table1.ID1,
Table1.field1,
Table1.field2,
Table1.field3,

IIf((Left([Table1].[field3],1) Between 0 And
9),"numeric",[Table1].[field3])
AS Expr1

FROM Table1;

Bascially, I just want to flag the records, which have been corrupted,
however when running this query, it places "numeric" in the field
correctly
for the TRUE portion of the IIf statement, but I get an #Error, for the
other fields, which do not start with a numeric?

Any ideas?

Kind Regards

Ricky

 

[John Spencer]

Not really, since your posted expression should have always errored since it
was not correct with the fact that it was missing an open parens or had too
many close parens. I did post a version that was formatted correctly. Did
that version work?

Hi John

thanks for you email, the statement that you suggested with the keyword
"ISNUMERIC" did the trick, thanks for that, I'd been playing around with
different combinations for almost an hour.....by the way, do you happen to
know what was wrong with my logic?

Kind Regards

Ricky

Your posted expression has unbalanced parentheses. Two "(" and three ")"
IIf((Left([Table1].[field3],1) Between 0 And
9),"numeric",[Table1].[field3])AS Expr1

IIf(Left([Table1].[field3],1) Between 0 And 9,"numeric",[Table1].[field3])AS
Expr1

Of course if you just want to identify those records where field3 starts
with a number you could use criteria in the query of
WHERE Table1.Field3 Like "#*"

Alternative expressions:
IIf(Table1.Field3 Like "#*" ,"Numeric",Table1.Field3) as Expr1

IIf( IsNumeric(Left([Table1].[field3],1)),"Numeric",[Table1].[field3]) AS
Expr1

Hi

Probably a simple query, but how do I check for the start of a field
begins
with a numeric.

i.e

SELECT
Table1.ID1,
Table1.field1,
Table1.field2,
Table1.field3,

IIf((Left([Table1].[field3],1) Between 0 And
9),"numeric",[Table1].[field3])
AS Expr1

FROM Table1;

Bascially, I just want to flag the records, which have been corrupted,
however when running this query, it places "numeric" in the field
correctly
for the TRUE portion of the IIf statement, but I get an #Error, for the
other fields, which do not start with a numeric?

Any ideas?

Kind Regards

Ricky

 

[ricky]

Hi John

Yes, I did try the amended version that you sent, but this returned the same
error, but it's not a problem, the ISNUMERIC method worked just fine and is
probably more robust when working with numbers, thanks for the QA ....

Kind Regards

Ricky

Not really, since your posted expression should have always errored since it
was not correct with the fact that it was missing an open parens or had too
many close parens. I did post a version that was formatted correctly. Did
that version work?

Hi John

thanks for you email, the statement that you suggested with the keyword
"ISNUMERIC" did the trick, thanks for that, I'd been playing around with
different combinations for almost an hour.....by the way, do you happen to
know what was wrong with my logic?

Kind Regards

Ricky

Your posted expression has unbalanced parentheses. Two "(" and three ")"
IIf((Left([Table1].[field3],1) Between 0 And
9),"numeric",[Table1].[field3])AS Expr1

IIf(Left([Table1].[field3],1) Between 0 And 9,"numeric",[Table1].[field3])AS
Expr1

Of course if you just want to identify those records where field3 starts
with a number you could use criteria in the query of
WHERE Table1.Field3 Like "#*"

Alternative expressions:
IIf(Table1.Field3 Like "#*" ,"Numeric",Table1.Field3) as Expr1

IIf( IsNumeric(Left([Table1].[field3],1)),"Numeric",[Table1].[field3]) AS
Expr1

Hi

Probably a simple query, but how do I check for the start of a field
begins
with a numeric.

i.e

SELECT
Table1.ID1,
Table1.field1,
Table1.field2,
Table1.field3,

IIf((Left([Table1].[field3],1) Between 0 And
9),"numeric",[Table1].[field3])
AS Expr1

FROM Table1;

Bascially, I just want to flag the records, which have been corrupted,
however when running this query, it places "numeric" in the field
correctly
for the TRUE portion of the IIf statement, but I get an #Error, for the
other fields, which do not start with a numeric?

Any ideas?

Kind Regards

Ricky