Using Left Function

[paul29berks]

I have a column in access containing codes in format K2316 and wish to remove Prefix (K) from this code.

How can i do this as a calculated field?

thanks

 

[Norie]

Try this

Mid([YourField], 2)

 

[paul29berks]

Thanks for your reply

Have tried this but receive an error:

The Specified field '[PAYE_Code]' could refer to more than one table listed in the FROM clause of your SQL statement.

Thanks

 

[Norie]

Well you need to reference the table.

[YourTable].[PAYE_Code]

 

[paul29berks]

Sorry had done that, this part seems to work, but am having trouble converting this an excel formula:

=IF(LEFT(D2,1)="k",(((E2)*-10)-9),(IF(RIGHT(D2,1)="L",(((E2)*10)+9),(IF(RIGHT(D2,1)="T",(((E2)*10)+9),0)))))

into an access statement.

If a code begins with a K - remove 1st character to left
If a code begins with an L remove first charcter to right
If a code begins with a T remove first charcter to right

Thanks

 

[Norie]

Can you post some examples of the data?

 

[paul29berks]

I have a query which contains:

column 1 = Tax Codes

column 2 = a statement to say:

If a code begins with a K - remove 1st character to left
If a code begins with an L remove first charcter to right
If a code begins with a T remove first charcter to right

I have written the statement

IF(Left([dbo_Payslip_Static_Data],[PAYE_CODE]=”k”, Right([dbo_Payslip_Static_Data],[PAYE_CODE], len([dbo_Payslip_Static_Data],[PAYE_CODE])-1)*-10)-9),(Val([dbo_Payslip_Static_Data],[PAYE_CODE])*10)+9)

[PAYE_CODE] being the table

but having problems as error appears:

"The expression has a function containing the wrong number of arguments"

Thanks

 

[Norie]

Can you post an example of the data?

By the way the syntax for Left to get the 1st character from a field would be as follows.

Left([dbo_Payslip_Static_Data], 1)

By the way I don't understand what you mean by 'remove the first character to the right/left'.

To the right/left of what?

 

[paul29berks]

Unfortunately cannot post data

For example:

K316 = starts with "k" remove first charcter to left i.e K so I am left with number only - 316

and visa versa.

So would this written as

Left([dbo_Payslip_Static_Data], 1), [PAYE_Code], 1)

(As I am having to include [table name] and [field name]

Thanks

 

[Norie]

I'm totally confused now.

Which field do you actually want to change?

Is dbo_Payslip_Static_Data the table name?

If it is why are you using it like that? Did you not see my example?

Just replace MyTable with dbo_Payslip_Static_Data.

Left([dbo_Payslip_Static_Data]![PAYE_Code], 1)

By the way why can't you post some data?

If it's confidential surely you could create some dummy data to illustrate what you want.