Need help to make a custom format expression

M

M.Tavassoli

Hi;
I need a special format expression for a number: the total number of digits
must be 10,but i must put a * between the adding zeroes and the first digit
of the entered number.For example if the entered number is 123 ,the
formatted expression must be 000000*123 and if the row number is 12345 the
resulted number must be 0000*12345 .How would be this custom format
expression?
Any help is appreciated.
 
G

Granny Spitz via AccessMonster.com

M.Tavassoli said:
How would be this custom format
expression?
Click to expand...

IIF(IsNull(fld1), Null, (Mid(Format(fld1, "0000000000"), 1, 9 - Len(fld1)) &
"*" & fld1))
 
J

John Vinson

Hi;
I need a special format expression for a number: the total number of digits
must be 10,but i must put a * between the adding zeroes and the first digit
of the entered number.For example if the entered number is 123 ,the
formatted expression must be 000000*123 and if the row number is 12345 the
resulted number must be 0000*12345 .How would be this custom format
expression?
Any help is appreciated.
Click to expand...

I can't imagine any way to do so using the Format property of a field.
You cannot store an asterisk in a number field, either. You could
create a Text field with an expression like

String(10-Len(Cstr(Val([input]))), "0") & "*" & Val([input])

to assemble this string from pieces.

The logic of this is... assuming an input text field named Input, the
Val() function converts it to a number (trimming any leading zeros or
blanks that the user might have typed); CStr() converts this integer
back to a string; Len() determines the length of that string; and the
String() function generates a string of "0"s to make a total of 10.

John W. Vinson[MVP]
 
G

Granny Spitz via AccessMonster.com

John said:
Val() function converts it to a number (trimming any leading zeros or
blanks that the user might have typed); CStr() converts this integer
back to a string; Len() determines the length of that string; and the
String() function generates a string of "0"s to make a total of 10.
Click to expand...

... and when the input is null it generates #Error, but that's easily fixed
with an IIF function.
 
M

M.Tavassoli

Thank you.

John Vinson said:
Hi;
I need a special format expression for a number: the total number of
digits
must be 10,but i must put a * between the adding zeroes and the first
digit
of the entered number.For example if the entered number is 123 ,the
formatted expression must be 000000*123 and if the row number is 12345
the
resulted number must be 0000*12345 .How would be this custom format
expression?
Any help is appreciated.
Click to expand...

I can't imagine any way to do so using the Format property of a field.
You cannot store an asterisk in a number field, either. You could
create a Text field with an expression like

String(10-Len(Cstr(Val([input]))), "0") & "*" & Val([input])

to assemble this string from pieces.

The logic of this is... assuming an input text field named Input, the
Val() function converts it to a number (trimming any leading zeros or
blanks that the user might have typed); CStr() converts this integer
back to a string; Len() determines the length of that string; and the
String() function generates a string of "0"s to make a total of 10.

John W. Vinson[MVP]
Click to expand...
 
M

M.Tavassoli

Yes.you r right.but i have already handeled the input to prevent it being
null using a NZ function,so,if the input field is null nothing happens.
Thank you for ur help.
 
Top