What's the equvalent for SQL "LPAD" Function 2

[Jembo]

I have user data in a table.
In one of the fields there is a text field containing a number.
Some users have input the nuber as "1", some have input it as "001".
I want to write a query that will pad out the single numbers to 3 characters i.e. turn "1" top "001".
I am in SQL view but LPAD function does not work - I get "Undefined function LPAD in expression".
My query reads;

SELECT LPAD(tblAllData.LOPID, 3, '0')
FROM tblAllData
WHERE(
SELECT tblAllData.LOPID
FROM tblAllData
WHERE Len(tblAllData.LOPID)<3);

Is there an equivalent function I can use?

Jembo

 

[PHV]

SELECT Right('000' & [LOPID], 3) As PaddedLOPID
FROM tblAllData

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

 

[Jembo]

Brilliant!!!