Left and InStr Function to Split Record

[jdawg_1989]

Hi All,

I currently have a field (Allocation) in access that contains information like the following
123456: 1/1/2/1
73846272:1/3/4/1
726353- 8/9/4/1
27364521-3/5/1/3

I have build an expression:

Number: LEFT([Allocation],InStr([Allocation],":")-1)

This would work great if I knew all the records were seperated by ":" but this is not the case.

Ideally I want an expression that would just return the number no matter what the separator is.

Thanks in advance.

 

[Mr. B]

Try using an IF statement to determine which, if any, of the characters is in the string.

If instr(InStr([Allocation],":") > 0 then
     Number: LEFT([Allocation],InStr([Allocation],":")-1)
ElseIf instr(InStr([Allocation],"-") > 0 then
     Number: LEFT([Allocation],InStr([Allocation],"-")-1)
Else
     'Here you might need some other defined way to extract a string
Endif

Just an idea of how you could do it.

 

[jdawg_1989]

Try using an IF statement to determine which, if any, of the characters is in the string.

If instr(InStr([Allocation],":") > 0 then
     Number: LEFT([Allocation],InStr([Allocation],":")-1)
ElseIf instr(InStr([Allocation],"-") > 0 then
     Number: LEFT([Allocation],InStr([Allocation],"-")-1)
Else
     'Here you might need some other defined way to extract a string
Endif

Just an idea of how you could do it.

Hi Thanks for the reply,

I think I'm being daft here. But this won't work as part of a query expression. How should I apply this?

Many Thanks.

 

[bparkinson]

Can you take a pass through the data first, and replace the - with a : ?

 

[Mr. B]

You can create a User Defined Function and call that function in your qurey.

 

[Galaxiom]

If you really want *any* separator (ie any character other than a numeric digit you could loop through the value character by character until you find the first non-numeric character and return those which precede it.

The IsNumeric function is quite useful for this.

 

[Nikita.24]

HI,

I tried left and instr function for string extraction but I'm getting #error and I cant resolve it iserror null nothing working on it.

Please if you could suggest me anything

 

[Gasman]

HI,

I tried left and instr function for string extraction but I'm getting #error and I cant resolve it iserror null nothing working on it.

Please if you could suggest me anything

Not really? as you have not given anyone anything to work with?

Bit like asking 'how long should my piece of string be'?

Post what you have tried within code tags for clarity and what you are trying to return.? Use the </> icon above for code tags.

This is a 9 year old thread. You would have also been better starting your own thread.?

 

[MajP]

This works for me.

Public Function GetLeadingNumbers(strIn As String) As String
  Dim char As String
  Dim strOut As String
  Dim i As Integer
  For i = 1 To Len(strIn)
    char = Mid(strIn, i, 1)
    If Asc(char) > 47 And Asc(char) < 58 Then
      If strOut = "" Then
        strOut = char
      Else
       strOut = strOut & char
      End If
    Else
      Exit For
    End If
  Next i
  GetLeadingNumbers = strOut
End Function

 

[theDBguy]

Hi. Just my 2 cents added into the mix... Another option might be to use Regular Expressions?

 

[CJ_London]

you can use the val function - based on the original OP's requirement

?val("123456: 1/1/2/1")
123456

?val("726353- 8/9/4/1")
726353