Format Calculated Formula Field

[SantasLittleHelper]

I have a formula field in an access table which basically combines 2 fields. However, the calculated field changes the number field to remove the zeros. Is the below possible?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Letters[/TD]
[TD]Numbers[/TD]
[TD]What the Calculated Field shows[/TD]
[TD]What I want the calculated field to show[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]0001[/TD]
[TD]AA1[/TD]
[TD]AA0001[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]0002[/TD]
[TD]AA2[/TD]
[TD]AA0002[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]0001[/TD]
[TD]BB1[/TD]
[TD]BB0001[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]0002[/TD]
[TD]BB2[/TD]
[TD]BB0002[/TD]
[/TR]
</tbody>[/TABLE]

 

[Micron]

I have a formula field in an access table which basically combines 2 fields

Just trying to be informative here, not condescending. Generally, you shouldn't be combining data in table fields but instead, use forms/reports for combining. To help you better going forward, provide more details around your issue, such as data types and field names.

That being said, the way to combine is to concatenate. = [Letters] & [Numbers]
However, you may have to coerce a number type to text (which you cannot do in a table field), and if you're really doing this in a table, I don't think the table will accept just any expression (what you're calling a formula). Thus to store this concatenation in a table, you may have no choice but to do this in an update query that creates the data via concatenation. Again, it's generally considered not to be best practice to keep both the separate values and the concatenation. Nor is it considered good practice to store calculations such as qty x price.

 

[xenou]

if "numbers" is a numeric data type (number, rather than text) then you probably "see" 0001 only because it is displayed that way with formatting options. The underlying value would be merely the number.

If so, the calculated field should do the same formatting if you want to get the leading zeros:
= [Letters] & Format([Numbers], "0000")

 

[SantasLittleHelper]

the calculated field should do the same formatting if you want to get the leading zeros:
= [Letters] & Format([Numbers], "0000")

I have just tried ths but when I try to save it, an error pops up saying this expression cannot be used in a calculated column.

Do you know how I can get around this?

 

[Micron]

Do you know how I can get around this?

Sure. Follow the advice already given and don't use calculated fields in tables. Aside from that, the types of calculations allowed in a table is limited and this isn't one of them.

 

[stumac]

I agree with not using the calculated fields for the most part... Although I have had a requirement to use them once myself before.

It wont accept format, however something like this should work:

[Letters] & String(4-Len([Numbers]),"0") & [Numbers]

If your number will ever be more than 4 digits you will get an error for that record.

 

[xenou]

Along the same lines,
[Letters] & Right("0000" & [Numbers],4)

Which would also calculate wrong for numbers greater than 9999 and would return 0000 for null numbers.