How in a query to do I left pad a string with zeros?

A

aym_playing

I have a long integer field residentID with values ranging from 1-2800. I
want to use this field to create dummy SSNs with the same value but left
padded with zeros to nine spaces. If Visual FoxPro, I would do

update residents
set SSN = padl(alltrim(str(residentID)),9,'0') ;

How do I do it in MS Access?

Also, is there any document that gives details about every MS Access
function I can use to create expressions in queries?

Thanks!
 
A

Ayman Fadel

String(9-Len([trim(STR(ResidentID)),"0") & trim(str(ResidentID))

I also realized I could hit the Help button in the Expression Builder tools
for function help.

Is there a better way to do this?
 
J

JohnFol

format(residentID,"000000000")


Ayman Fadel said:
String(9-Len([trim(STR(ResidentID)),"0") & trim(str(ResidentID))

I also realized I could hit the Help button in the Expression Builder
tools
for function help.

Is there a better way to do this?

aym_playing said:
I have a long integer field residentID with values ranging from 1-2800. I
want to use this field to create dummy SSNs with the same value but left
padded with zeros to nine spaces. If Visual FoxPro, I would do

update residents
set SSN = padl(alltrim(str(residentID)),9,'0') ;

How do I do it in MS Access?

Also, is there any document that gives details about every MS Access
function I can use to create expressions in queries?

Thanks!
Click to expand...
Click to expand...
 
Top