Using IF ISNUMERIC inside SELECT statement

[dwinn]

Hello,

I am currenrtly trying to write a query in Microsoft Access 2010.

This query retreives a list of values from a table I have called tblTable

Some of the values are numeric and some are alpha numeric.

I am trying to do the query so that when it comes across numeric values, it places a 'Z' at the beginning of it.

If it is alpha numeric, the value is returned as it is.

I have made an attempt so far...but I keep getting an error message saying 'Missing Expression'.

My code is as follows:

SELECT PaperNo, IF( ISNUMERIC(PaperNo) = 1) THEN "Z" + PaperNo ELSE PaperNo
FROM tblTable

Can anybody help and advise on what I am doing wrong at all please?

Many Thanks,

Dan

[dwinn]

Sorry, I meant that I get an error saying: 'Missing Operator'.

Dan

[BitBlt]

Okay, a couple of things going on here. First off, the IsNumeric operator returns 0 for false and -1 for true. You might consider using the MSAccess constant "True".

That being said, the proper syntax for a conditional in MSAccess SQL is the "IIF" function, rather than an IF...THEN construct. Your expression should then look something like this:

SELECT tblTable.PaperNo, IIf(IsNumeric([PaperNo])=True,"Z"+[PaperNo],[PaperNo]) AS Expr1
FROM tblTable;

Hope that solves your issue!

[dwinn]

Hello,

Yeah that did solve it...thank you very much!