concatenation problem

[bnageshrao]

I have a field called duns number which is a text field 9 charsacters long. I want to update this field if it is 8 field long by adding one zero in front of it and two zeroes in front of it. I wrote the statement, but it shows all kinds of errors, it says it cannot update due to conversion failure,due to key violations, due to lock violations,due to validation rule etc. Id i simply say update all the fileds by adding any number of zeroes it does it, only when i put the conditional statement it doesnot work. My satement is as follows

UPDATE Account_Only_Fields SET Account_Only_Fields.[Duns Number] = IIf(Len([Account_Only_Fields]![Duns Number]=8),"0"+[Account_Only_Fields]![Duns Number],IIf(Len([Account_Only_Fields]![Duns Number]=7),"00"+[Account_Only_Fields]![Duns Number],[Account_Only_Fields]![Duns Number]));

Any suggestions? Thanks

 

[PHV]

Something like this ?
UPDATE Account_Only_Fields
SET [Duns Number] = Right("00" & [Duns Number], 9)
WHERE Len([Duns Number]) Between 7 And 8

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

 

[bnageshrao]

Thanks PHV, it worked like a charm, u r real Guru, thanks once again