Adding leading zero's to a string

[irade92]

In Clipper there is a function REPLICATE...which means adding leading zeros to some string to get desirefull lenght...for example REPLI("0",10-LEN(string))...What is correspond function in Access...Please Help

 

[JamesMcS]

I usually do

right("0000000000" & [fieldname], x)

or

left([fieldname] & "00000000000", x)

, x being string length.

 

[DCrake]

Or

Format([FieldName],"0000000000")

 

[JamesMcS]

Didn't know you could do that!

 

[vbaInet]

I think what irade92 is after is a dynamic way of padding, so something like this:

String(10 - Len("String"), "0")

 

[JamesMcS]

Didn't know about that one either - does it do the same as my reply?

 

[irade92]

I usually do

right("0000000000" & [fieldname], x)

or

left([fieldname] & "00000000000", x)

, x being string length.

Thanks James
I tried your code in Immediate Window and it doesnt work. I have decimal numbers like 55.68 ...or 345.74....and I want to get 0000055.68 or 0000345.74... Lenght should be 10.

 

[JamesMcS]

and the right function doesn't do that? Try

 right("0000000000" & cstr([fieldname]),10)

If the data is formatted as a number, it'll remove the leading zeros, I think. It has to be a string to leave the zeros in there.

 

[DCrake]

try

format([FieldName],"0000000.00")

 

[vbaInet]

Didn't know about that one either - does it do the same as my reply?

The String() function is the equivalent of the Replication() function, so I was just expressing that point. From the help files, String() - Returns a Variant (String) containing a repeating character string of the length specified. From msdn, Replicate() - Returns a character string that contains a specified character expression repeated a specified number of times. Your function isn't quite the same.

From irade's recent posts, it seems he/she isn't after the Replication() function equivalent afterall. I would go for DCrake's Format() suggestion.

 

[irade92]

try

format([FieldName],"0000000.00")

Yes..I tried this eather and it works in Immediate Window but when I put it in a Query it changes to format([fieldName],"000.000.000") Where to correct not to change the format

 

[vbaInet]

It cannot change the Format() condition, it is probably changing the result when viewed in Datasheet.

Is this a Currency data type? Did you apply a format in the Format property of that field? Also, write down some examples of what some records are like BEFORE and AFTER.

 

[Galaxiom]

It cannot change the Format() condition, it is probably changing the result when viewed in Datasheet.

After seeing what can happen to functions in the Greek variant of Access I would't rule out any possibility.

 

[irade92]

It cannot change the Format() condition, it is probably changing the result when viewed in Datasheet.

Is this a Currency data type? Did you apply a format in the Format property of that field? Also, write down some examples of what some records are like BEFORE and AFTER.

The field in the table is Single with 2 decimals...i.e 56.78....In Query design When I use Format([field],"0000000.00") and press Enter it changes to Format([field],"000.000.000") and the result is only 000 in Datasheet view

 

[DCrake]

Change the data type from single to double.

 

[vbaInet]

After seeing what can happen to functions in the Greek variant of Access I would't rule out any possibility.

Quite interesting GalaxiomAtHome. I would imagine that things can get changed in the VBA editor but in a query it should throw a syntax error. Anything put in quotes should remain as it was typed in a query (especially if it wasn't put in the Criteria row).

 

[irade92]

I think what irade92 is after is a dynamic way of padding, so something like this:

String(10 - Len("String"), "0")

Many thanks to you..
Your suggestion works...STRING(10-LEN(STRING),"0") & STRING I got exactly what I want...0000059.68
Thanks