calculated field combining multiple fields into one

Question

I'm trying to create a value in a calculated field based on a 4 digit Autonumber, first letter of firstname, and lastname to create a username. I'm intending to create something like this username -> flastname0001.

Here is what I'm using for my expression: LCase(Left([firstname],1) & [lastname]) & Left([idnumber],4)

I have 0000 in the format for the idnumber field and set to long integer and increment.

No matter what I have tried this is the only result I can get:

Table:

idnumber = 0001
firstname = John
lastname = Doe
(calculated field) username = jdoe1

Anyone know how to make the username field create jdoe0001?

HansUp HansUp · Accepted Answer · 2015-03-21T15:54:27

Left() will give you the number of characters you request when the source string contains at least that number of characters. But when the source string contains fewer characters than requested, Left() gives you the source string.

Here is an example from the Immediate window:

? Left("abcde", 4)
abcd
? Left("a", 4)
a

In your case, you have a numeric field with a format. So when the field contains 1, that value is displayed as 0001. However, the actual stored value is just 1, without the leading zeros. And when you give the field's value to Left(), it operates on the un-formatted number 1:

idnumber = 1
? Left(idnumber, 4)
1
' which is equivalent to this ...
? Left(CStr(idnumber), 4)
1

Since you want the leading zeros, you can apply your format to idnumber when you feed it to Left():

? Left(Format(idnumber, "0000"), 4)
0001

But using both Left() and Format() there seems like overkill. If idnumber never contains more than 4 digits, just Format() will give you what you need:

? Format(idnumber, "0000")
0001

So your original expression could become ...

LCase(Left([firstname],1) & [lastname]) & Format(idnumber, "0000")

Finally, make sure the 4-digits requirement is what you will always want. Long Integer can accommodate numbers up to 2,147,483,647.

That approach works for a calculated field in a query. But if you want to define a calculated field in a table, Format() is not available. In that case, you can concatenate "0000" and idnumber, and use Right() to grab the last 4 characters:

? Right("0000" & idnumber, 4)
0001

calculated field combining multiple fields into one

1 Answers