Formatting Trouble - Please Help

[Ant]

I have a form with an unbound text box with the control
source of:-

=UCase(Left([lsurname],4) & (Left([Lforenam],1))) & DCount
("[Learn_id]","Learner Dataset")+1

The problem i am having is i would like the number at the
end of the concatenation of the fields to be in the format
of 0009.

For example my Text box currently displays: WEBBA9

I would like it to display: WEBBA0009

Is this possible.

Any help is greatly appreciated.

Thanks all

Anthony Webb

 

[Marshall Barton]

I have a form with an unbound text box with the control
source of:-

=UCase(Left([lsurname],4) & (Left([Lforenam],1))) & DCount
("[Learn_id]","Learner Dataset")+1

The problem i am having is i would like the number at the
end of the concatenation of the fields to be in the format
of 0009.

For example my Text box currently displays: WEBBA9

I would like it to display: WEBBA0009

You have to use the Format function to get special formats
in a concatenated string:

=UCase(Left([lsurname],4) & (Left([Lforenam],1))) &
Format(DCount("[Learn_id]","Learner Dataset")+1, "0000")

 

[MDW]

Uhm, I think this will work....

=UCase(Left([lsurname],4) & (Left([Lforenam],1))) & "000"
& DCount("[Learn_id]","Learner Dataset")+1

If the "DCount("[Learn_id]","Learner Dataset")+1" part is
what generates the 9 at the end. One thing to note,
though, is that if you're trying to pad this out to be a
specific length, then you'd run into trouble with any
leard_ids that are 2 digits long (i.e., 10 or higher).