Solved Hi, I am trying to Concatenate 2 fields but I am not getting the desired result. (1 Viewer)

Local time
Today, 05:56
Joined
Aug 19, 2021
Messages
212
Hi, I want to Concatenate 2 fields to make a separate Code for each account in chart of account.
I have a fields:
1) ID containing numbers 0001, 0002, 0003 etc
2) AGCode (AccountGroup Code) containing 11, 12, 13, 21, 22 etc this is a code to Identify each Account Group such as Current Asset, Fixed Asset, Current Liability etc.
3) COACode (ChartOfAccount Code) this is the field in which I am trying to apply an expression to generate separate code for each head of account for general ledger.
1652521241121.png


The Code I am trying is:
Code:
[AGCode] & "." & [ID]
1652521382883.png

and Output for the above code is:
11.1, 11.2, 11.3, 21.1, 21.2 etc.

1652522065237.png


While my desired output is:
11.0001, 11.0002, 11.0003, 21.0001, 21.0002 etc.

Please guide me how to change its output to 11.0001 to 11.1

Thank you
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:56
Joined
May 7, 2009
Messages
19,169
on design view of your table.
select COACode field.
change the "Expression" to: [AGCode]+[ID]/10000
below Panel, "Result Type" should be set to double or decimal.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 00:56
Joined
Feb 19, 2013
Messages
16,553
try [AGCode] & "." & format([ID],"0000")

Not sure why you want it as a calculated field - it can't be indexed so not much good performance wise for filtering or sorting once you get a lot of data. Better to just display in a form or report as required using the same calculation.

And trust your ID never goes over 9999 and you are not worried about gaps
 
Local time
Today, 05:56
Joined
Aug 19, 2021
Messages
212
on design view of your table.
select COACode field.
change the "Expression" to: [AGCode]+[ID]/10000
below Panel, "Result Type" should be set to double or decimal.
1652524849473.png

Thank you arnelgp
Can we change 11.001 to 11.0010 and 11.002 to 11.0020?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:56
Joined
May 7, 2009
Messages
19,169
again on table design, on the "Lower Panel", add "Format" to the field:

#.0000
 
Local time
Today, 05:56
Joined
Aug 19, 2021
Messages
212
try [AGCode] & "." & format([ID],"0000")

Not sure why you want it as a calculated field - it can't be indexed so not much good performance wise for filtering or sorting once you get a lot of data. Better to just display in a form or report as required using the same calculation.

And trust your ID never goes over 9999 and you are not worried about gaps
Thank you CJ, Yes actually I want to use this code to sort my accounts by their groups.
Can you please explain if you have better Idea to do this?
 

GPGeorge

Grover Park George
Local time
Yesterday, 17:56
Joined
Nov 25, 2004
Messages
1,776
You are confusing the stored values, i.e. the AutoNumbers, with a FORMATTED DISPLAY of the values.

In other words. 1 is the same VALUE as 0001. The only difference is the FORMAT of the DISPLAY. That is why the suggestions to use a format expression are effective. Like CJ says, FORMAT is a property of forms and reports and should be handled in forms and reports, not at the table level.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:56
Joined
Feb 19, 2002
Messages
42,981
Keep all three parts of the composite in separate fields. Concatenate them for display. For searching, use three cascading combos to let the user drill into the records.

To concatenate the fields:

Format(fld1, "0000") & "." & Format(fld2, "0000") & "." & Format(fld3, "0000")

This assumes you want four digit numbers. If you want 3 digits, use three zeros. If you want 5 use five zeros.
 
Local time
Today, 05:56
Joined
Aug 19, 2021
Messages
212
You are confusing the stored values, i.e. the AutoNumbers, with a FORMATTED DISPLAY of the values.

In other words. 1 is the same VALUE as 0001. The only difference is the FORMAT of the DISPLAY. That is why the suggestions to use a format expression are effective. Like CJ says, FORMAT is a property of forms and reports and should be handled in forms and reports, not at the table level.
Thank you so much dear but actually 11.1 and 11.0001 are different if I am not wrong. After concatenation the 1 and 0001 will be 11.10 and 11.0001.
 
Local time
Today, 05:56
Joined
Aug 19, 2021
Messages
212
Keep all three parts of the composite in separate fields. Concatenate them for display. For searching, use three cascading combos to let the user drill into the records.

To concatenate the fields:

Format(fld1, "0000") & "." & Format(fld2, "0000") & "." & Format(fld3, "0000")

This assumes you want four digit numbers. If you want 3 digits, use three zeros. If you want 5 use five zeros.
Thank you Pat Hartman for your response.
 
Local time
Today, 05:56
Joined
Aug 19, 2021
Messages
212
You are confusing the stored values, i.e. the AutoNumbers, with a FORMATTED DISPLAY of the values.

In other words. 1 is the same VALUE as 0001. The only difference is the FORMAT of the DISPLAY. That is why the suggestions to use a format expression are effective. Like CJ says, FORMAT is a property of forms and reports and should be handled in forms and reports, not at the table level.
I'm sorry if I said something wrong.
 

GPGeorge

Grover Park George
Local time
Yesterday, 17:56
Joined
Nov 25, 2004
Messages
1,776
I'm sorry if I said something wrong.
I apologize. You said nothing wrong. Language can be an obstacle unfortunately.

What I meant to say is that the way a number is STORED can be different from the way it is DISPLAYED.

I used the example of 1 and 0001. They are mathematically equal. The three leading zeros are meaningless in this context. They DISPLAY the value 1 in a different way, but don't change the value. When you talk about formatting AutoNumbers with leading zeroes, for example, all that means is that you are adding them for DISPLAY, but the real value doesn't change.

Again, I apologize for implying there was anything wrong with what you said.
 
Local time
Today, 05:56
Joined
Aug 19, 2021
Messages
212
I apologize. You said nothing wrong. Language can be an obstacle unfortunately.

What I meant to say is that the way a number is STORED can be different from the way it is DISPLAYED.

I used the example of 1 and 0001. They are mathematically equal. The three leading zeros are meaningless in this context. They DISPLAY the value 1 in a different way, but don't change the value. When you talk about formatting AutoNumbers with leading zeroes, for example, all that means is that you are adding them for DISPLAY, but the real value doesn't change.

Again, I apologize for implying there was anything wrong with what you said.
Thank you so much George
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:56
Joined
Feb 19, 2013
Messages
16,553
To add to George's comments, it is strongly recommended that you do not apply any formatting to fields in tables which mask it's underlying value. Other examples other than the '0000' you have used include dates (don't exclude the time element) and decimals/currency (don't round). It is also one of the reasons for not using lookups in tables which masks the underlying value (the ID) with the displayed value (name/whatever)

The reason is that you as a developer need to know exactly what you are dealing with, user should not be looking at tables or queries so restrict your formatting to forms and reports.

examples:
decimals

actual..formatted to 2dp
1.014....1.01
2.014....2.01

when summed you will get 3.03, not 3.02 because the actual value is 3.028
Often happens when multiplying quantity * price or calculating a tax percentage

dates
actual......................................formatted to only show the date
01/01/2020 09:30:00.......01/01/2020

if you are using a criteria <=somedate, then this record will not be returned since with the time element it is not <=somedate



Other times it matters is when you are importing/exporting from Excel - it is the underlying value that is used, not the formatted value you see
 

spaLOGICng

Member
Local time
Yesterday, 17:56
Joined
Jul 27, 2012
Messages
105
try [AGCode] & "." & format([ID],"0000")

Not sure why you want it as a calculated field - it can't be indexed so not much good performance wise for filtering or sorting once you get a lot of data. Better to just display in a form or report as required using the same calculation.

And trust your ID never goes over 9999 and you are not worried about gaps
Calculated or computed columns do perform better than Query Expressions. It also saves time sourcing it in other areas, Also, if the DB ever gets migrated to SQL Server, it is the preferred method. Nothing wrong with calculated columns in Access.

The Format function will not work in computed columns.
 

Users who are viewing this thread

Top Bottom