Solved How can I get first word from a sentence using Left function?

[Khurramshaikh47]

Hi, I have an excel formula to get the first word from a sentence.
=LEFT(CellName,FIND(" ",CellName)-1)
Example:

I want to use this in Microsoft Access to find the first word from a particular field by using calculation column.
Can anyone guide me how to use this?

Thank you

 

[isladogs]

Assuming Text String is the name of a field :

Left([Text String], InStr([Text String], " ")-1)

 

[Khurramshaikh47]

Assuming Text String is the name of a field :

Left([Text String], InStr([Text String], " ")-1)

Thank you isla, but nn error occurred.

 

[isladogs]

Don't use a calculated field as that's unnecessary duplication of data.
The expression I gave was intended for use in a query where it will work

 

[arnelgp]

you only use it in Query.
also you first Test if there is indeed a Space(" ") character:

Iif(Instr(1, CellName, " ") <> 0, Left(CellName, Instr(1, CellName, " ") - 1), CellName)

 

[Khurramshaikh47]

Don't use a calculated field as that's unnecessary duplication of data.
The expression I gave was intended for use in a query where it will work

Thank you so much dear! Now I am using it in a Query & its working. But there is a problem. Actually I want to combine some numerical values from 3 fields and concatenate them to make a numerical field. But its result type is not number. So I can not sort it as (Smallest to Largest).
I have the following fields.
1) AccountGroup
Examples: "1 Current Asset", "2 Fixed Asset", "3 Intangible Asset", "1 Current Liability", "2 Long Term Liability" etc.
2) AccountType
Examples: "1 Asset", "2 Liability", "3 Proprietorship", "4 Revenue", "5 Expense", "6 Stock" etc
3) ID (Autonumber field)
I am using the following code:

CD: Left([AccountType],InStr([AccountType]," ")-1) & Left([AccountGroup],InStr([AccountGroup]," ")-1) & "." & [ID]

The result is fine like:

Can I change its result type to number to sort it as (Smallest to Largest).

 

[Minty]

Use the CDec() function around the whole concatenation

MS Access: CDec Function

This MSAccess tutorial explains how to use the Access CDec function with syntax and examples. The Microsoft Access CDec function converts a value to a decimal number.

www.techonthenet.com

 

[arnelgp]

you can format the ID to "00" so it will sort correctly:

CD: Left([AccountType],InStr([AccountType]," ")-1) & Left([AccountGroup],InStr([AccountGroup]," ")-1) & "." & Format([ID], "00")

 

[Khurramshaikh47]

you can format the ID to "00" so it will sort correctly:

CD: Left([AccountType],InStr([AccountType]," ")-1) & Left([AccountGroup],InStr([AccountGroup]," ")-1) & "." & Format([ID], "00")

Thank you so much solved.

 

[Khurramshaikh47]

Use the CDec() function around the whole concatenation

MS Access: CDec Function

This MSAccess tutorial explains how to use the Access CDec function with syntax and examples. The Microsoft Access CDec function converts a value to a decimal number.

www.techonthenet.com

I Could not found CDec() in expression builder in Query.

 

[Minty]

Simply

CD: CDec(Left([AccountType],InStr([AccountType]," ")-1) & Left([AccountGroup],InStr([AccountGroup]," ")-1) & "." & Format([ID], "00"))