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

 

[paul29berks]

Doesnt posting involve downloading extra software???

dbo_Payslip_Static_Data is the table name
PAYE_Codes = field name

what I am trying to do is remove the letter from the tax code so that I am just left with a number that I can use within a calculation...This should be done in an extra column set up in a query.

You suggestion would work if there was 1 tax code

Left([dbo_Payslip_Static_Data]![PAYE_Code], 1) but I have tax codes where letters start at left hand side and others that end to the right hand side i.e

K2316 - remove 1st character to left so remaining is 2316
340L - remove 1st character to right so left with 340
36T - remove 1st character to right so left with 36

Thanks

 

[Norie]

Doesnt posting involve downloading extra software???

No.

Just add type some data into your message, like you have done.

IIf(Left([dbo_Payslip_Static_Data]![PAYE_Code],1)="L",Mid([dbo_Payslip_Static_Data]![PAYE_Code],2),Right([dbo_Payslip_Static_Data]![PAYE_Code],Len([dbo_Payslip_Static_Data]![PAYE_Code])-1))

 

[paul29berks]

Ok. This is what I have written so far:

Taxable: 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 I am receiving an error "Invalid Procedure Call"

Thanks

 

[paul29berks]

Your statement currently:

Removes the Letter K from code K2316 i.e first character to the left of the Tax Code so I receive an output 2316

If I wanted to add to the statement:

If tax code ends in L i.e 238L remove 1st character to the right i.e so left with output 238

If tax code end in T i.e 203T remove 1st character to the right i.e so left with output 203

Thanks

 

[Andrew Fergus]

Hi Paul
The first half of your formula looks fine but I'm not sure about the 2nd half. What is Val? Also, don't you want to remove the rightmost character in the 2nd half of your if statement? (the "L" or the "T"?)
Andrew

 

[paul29berks]

Hello Andrew

Thats correct, thats what i am hoping to achieve

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

Thanks

 

[Norie]

Paul

Did you try my suggestion?

Basically what it should do is if the field begins with an L return all the characters after the 2nd character. otherwise return all the characters except the last one.

Is that not what you want?

By the way your syntax is totally wrong in this.

Taxable: 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)

Does Access even allow you to enter this?

Andrew

From help

Val Function

Returns the numbers contained in a string as a numeric value of appropriate type.

 

[Andrew Fergus]

Thanks Norie & Paul for the tip
I couldn't find it in the Access 2000 help - definitely a useful function! I might have to upgrade my version of Access.....
Andrew

 

[DEEPAK KUMAR TYAGI]

try this

if (left(yourfield,1)="0",mid(yourfield,2,999),yourfield)

 

[Micron]

I think all of these formulas will fail if the number of letters before (and maybe after as well) the value ever change. To deal with K123, KK123, 123K, 123KK reliably requires a function. This seems to work:

Function getVal() As String
Dim svTaxable As String

getVal = ""
svTaxable = [dbo_Payslip_Static_Data]![PAYE_Code]
If IsNumeric(Left(svTaxable, 1)) Then
    svTaxable = Val(svTaxable)
Else
    For n = 2 To Len(svTaxable)
    If IsNumeric(Mid(svTaxable, n)) Then
        svTaxable = Val(Mid(svTaxable, n))
        Exit Function
    End If
    Next
End If
getVal = svTaxable
End Function

I assume the value is derived from a form, otherwise you cannot simply reference a table row/field this way. Sorry I cannot recall if the extracted value is needed directly in a query or in a form control. This code should work either way, but where it needs to reside depends on how it is to be called. Needs to be in a standard module if by query, or on a form if it needs to populate a form control.