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

[Khurramshaikh47]

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.

The Code I am trying is:

[AGCode] & "." & [ID]

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

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

 

[arnelgp]

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.

 

[CJ_London]

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

 

[Khurramshaikh47]

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.

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

 

[arnelgp]

again on table design, on the "Lower Panel", add "Format" to the field:

#.0000

 

[Khurramshaikh47]

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?

 

[Khurramshaikh47]

again on table design, on the "Lower Panel", add "Format" to the field:

#.0000

Thank you dear. I got my desired output.

 

[CJ_London]

Can you please explain if you have better Idea to do this?

as I said
Better to just display in a form or report as required using the same calculation.

 

[GPGeorge]

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]

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.

 

[Khurramshaikh47]

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.

 

[Khurramshaikh47]

as I said
Better to just display in a form or report as required using the same calculation.

Thank you so much dear

 

[Khurramshaikh47]

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.

 

[Gasman]

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.

That is not what GPGeorge said.

 

[Khurramshaikh47]

That is not what GPGeorge said.

Oh, maybe I didn't understand what he was saying.

 

[Khurramshaikh47]

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]

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.

 

[Khurramshaikh47]

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]

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]

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.