IIF Statement


R

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
 
Ad

Advertisements

J

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
 
R

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

John Spencer said:
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 said:
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
 
J

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?
ricky said:
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

John Spencer said:
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 said:
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
 
Ad

Advertisements

R

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

John Spencer said:
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?
ricky said:
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

John Spencer said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top