Query to pad a text field. 1

[salimnice]

I have a text field that contains a text value that is either 5 or six charters. I need a way to add a "0" to the first position of all the fields that are five charters in length. An example would be "50536" would become "050536" but "600001" would remain as "600001". Can anyone help me out with this?

 

[PHV]

UPDATE yourTable
SET yourField = "0" & yourField
WHERE Len(yourField) = 5;

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

 

[salimnice]

Is there a way to do it within the query with a function?

 

[PHV]

So, you don't want to update your table.
You may try either this:
Expr1: Right("0" & yourField, 6)
Or this:
Expr1: Format(yourField, "000000")

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

 

[salimnice]

Thanks. That's exactly what I needed.