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

[Khurramshaikh47]

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:

Left([AccountGroupID],3)

OUTPUT

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

 

[cheekybuddha]

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?

 

[SHANEMAC51]

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

this is only possible if it is a wildcard field

 

[Minty]

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]

What happens if you just use a query?

SELECT
  AccountName,
  AccountGroupID,
  Left(AccountGroupID, 3) AS Code
FROM YourTable;

 

[cheekybuddha]

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"

Looks like you may have hit the nail on the head!

 

[cheekybuddha]

I don't think you can assign a datatype to a calculated column.

Apparently you can, though I have never used calculated fields in Access as I only have Acc2k7!

 

[Minty]

Apparently you can, though I have never used calculated fields in Access as I only have Acc2k7!

Wow, ditto - I've only used them occasionally in SQL Server, where the datatype is determined from the source fields from recollection.

 

[cheekybuddha]

Google "The Evils Of Lookup Fields"

The Evils of Lookup Fields In Tables - The Access Web

The link to which Minty refers

 

[cheekybuddha]

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)

 

[Khurramshaikh47]

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.

 

[Minty]

Thank you for your response, Yes its lookup field.

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.

 

[Khurramshaikh47]

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]

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.

 

[Khurramshaikh47]

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