using Left Function

[paulS30berks]

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

 

[FoFa]

either MID([fieldname],2) or RIGHT([fieldname],len([fieldname]-1))

 

[paulS30berks]

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

 

[FoFa]

Then use [Tablename].[FieldName] instead

 

[paulS30berks]

Thanks for that.

I am trying to convert 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.

Can you help?

 

[FoFa]

Explain how you wish to do this?
query, function, what?
How will it be used?

 

[neileg]

Since UK PAYE codes either start with a K or end with a letter, the following should work.

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

Val() extracts anything to the left of the first letter in a string as a number.

You have remembered about BR haven't you?

 

[paulS30berks]

This will used in a query.

So I have column 1:

Field: Code
Table: Table Name

Then column 2 will be a statement to say:

If [fieldname] Code begins with a "k" then remove 1st character to the left
If [fieldname] Code ends with an L then remove first character to the right
If [fieldname] Code ends with a "T" then remove first character to the right

Thanks

 

[FoFa]

Can they both start with a K and end with either a L or T?
You could either use the IIF function (much like the Excel if) or create a VBA function and reference that in your query

 

[paulS30berks]

Thanks

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

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)

Have not included BR.

Thanks for your help

 

[neileg]

You've used a comma between the table name and field name instead of a fullstop.

 

[paulS30berks]

The statement has been accepted:

Expr1: IIf(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)

However when run an "Enter Parameter value" Box appears with letter "K" how can I remove this?

Thanks

 

[FoFa]

Change [”k”] to "k"

 

[neileg]

FoFa is right. The square brakets are used to denote a field name. If Access can't see a field with that name, it asks you for it, hence the prompt.

 

[paulS30berks]

Thanks

Now receiving an error " Invalid Procedure Call".

 

[neileg]

This works for me

Expr1: IIf(Left([dbo_Payslip_Static_Data].[PAYE_Code],1)="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)

 

[paulS30berks]

Thanks for your help