Need help with a formula!! 1

[khwaja]

I am using the following query to extract certain records in a table based on a text field:

SELECT tblCategoryBayCode.MasterNo, tblCategoryBayCode.BayCode, IIf(Left([Baycode],2)<10,"0"+[Baycode],"") AS Expr1
FROM tblCategoryBayCode;


The sample data is something like as under:

MasterNo BayCode Expr1
31 1A #Error
31 1A #Error
31 1A #Error
31 1A #Error
31 1A #Error
31 1A #Error

I am tryuing to add a zero in the front of middle column when the first letters are less than 10.

Problem is when it matches the criteria, it gives me #error value and rest are blank. Is there something I can do to modify the contents.


Cheers

AK

Note: Using Access 97 for back end A2002 for front end.

 

[PHV]

You want this (I guess no)?
IIf(Left([Baycode],2)<"10","0" & [Baycode],"")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

Perhaps this ?
Right("0" & [Baycode], 2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[khwaja]

Thanks PHV. Your first solution applies the right condition but does not produce any result. I have blank column. Perhaps you cannot apply the 'less than 10' condition when you enclose 10 in quotes. The second option applies globally and I need to restrict so that I only amend the data where field has prefix with value less than 10. To make it easy I am re producing the list showing values you want to amend or also those which need to be left out.

MasterNo BayCode Expr1
9197 19Z
9186 19Z
9186 19Z
9186 19Z
9186 19Z
9186 19Z
9101 1A #Error
9101 1A #Error
9101 1A #Error
9101 1A #Error
9101 1A #Error
9101 1A #Error
9101 1A #Error
9101 1A #Error
9101 1A #Error



Cheers

AK

Note: Using Access 97 for back end A2002 for front end.

 

[alvechurchdata]

You need to convert BayCode to numeric before testing its value. This works for me with your data:

[tt]iif(val(baycode)<10, "0" & BayCode, BayCode)[/tt]

Geoff Franklin

http://www.alvechurchdata.co.uk