Solved I am trying to use Left function but I could not find my desired output. (1 Viewer)

Local time
Today, 14:43
Joined
Aug 19, 2021
Messages
212
Hi,
I am trying to use Left Function in Calculated column to get starting 3 characters of a specific field.
Expression I am writing is:
Code:
Left([AccountGroupID],3)

1652336543412.png


OUTPUT
1652336961436.png


1) In "1.1 Current Asset" starting 3 characters are 1.1 so I want output in Code column 1.1 but its giving 1.

2) In "1.2 Fixed Asset" starting 3 characters are 1.2 but its giving 2 as a result.

3) In "2.1 Current Liability" starting 3 characters are 2.1 but its giving 4.

Please guide me where I am doing wrong.

Thank you
 

Attachments

  • 1652336943180.png
    1652336943180.png
    51.1 KB · Views: 155

cheekybuddha

AWF VIP
Local time
Today, 09:43
Joined
Jul 21, 2014
Messages
2,237
I'm not sure of the benefit of using a calculated column versus just using an expression in a query.

However, if you wish to use this feature, then you should check the datatype you assigned to the calculated column. Perhaps choose 'Text' or 'Currency' instead of 'Number'.

Maybe 'Number' assumes an integer?
 

Minty

AWF VIP
Local time
Today, 09:43
Joined
Jul 26, 2013
Messages
10,353
I don't think you can assign a datatype to a calculated column.

You could try Cstr(Left([AccountGroupID],3))

Or is AccountGroupID a lookup field? In which case I suspect it is returning the ID of the lookup.
Google "The Evils Of Lookup Fields"
 

cheekybuddha

AWF VIP
Local time
Today, 09:43
Joined
Jul 21, 2014
Messages
2,237
What happens if you just use a query?
SQL:
SELECT
  AccountName,
  AccountGroupID,
  Left(AccountGroupID, 3) AS Code
FROM YourTable;
 

cheekybuddha

AWF VIP
Local time
Today, 09:43
Joined
Jul 21, 2014
Messages
2,237
They are available in other RDBMS's too (eg MySQL).

The benefit being that the calculated column can be indexed. AFAIU, indexing calculated columns in Access is not possible, so their value is limited (none) IMHO!

(I may be wrong and am happy to be corrected)
 
Local time
Today, 14:43
Joined
Aug 19, 2021
Messages
212
I don't think you can assign a datatype to a calculated column.

You could try Cstr(Left([AccountGroupID],3))

Or is AccountGroupID a lookup field? In which case I suspect it is returning the ID of the lookup.
Google "The Evils Of Lookup Fields"
Thank you for your response, Yes its lookup field.
 
Local time
Today, 14:43
Joined
Aug 19, 2021
Messages
212
This is why they are confusing, the displayed data is not the actual data.

Read the link @cheekybuddha put up and you will see the issue described and how to resolve it.
Thank you but I could not understand properly. I am beginner. Can you please guide me about this?
 

Minty

AWF VIP
Local time
Today, 09:43
Joined
Jul 26, 2013
Messages
10,353
The lookup field hides the fact that the lookup data is stored with an ID.

Remove the lookup, and you will see that the data stored in your main table is the ID to the table containing the AssetGroup Text Description.
In your query, you will need to join that AssetGroup table to your main table.
 
Local time
Today, 14:43
Joined
Aug 19, 2021
Messages
212
The lookup field hides the fact that the lookup data is stored with an ID.

Remove the lookup, and you will see that the data stored in your main table is the ID to the table containing the AssetGroup Text Description.
In your query, you will need to join that AssetGroup table to your main table.
Thank you
 

Users who are viewing this thread

Top Bottom