Left of Character query

[Huck]

Hi
I'm trying to creat a field that picks up only the characters to the left of either "-" or "/" in the string. The number of characters to return can vary. eg the field [billref] can be 1234567-001 or 1234567/abc-001 or 12345678-001 or 12345678/abcdef-001.
I only need the numbers to the left of / or if that is not included in the string then to the left of -.
Please help if you can as this is driving me bonkers!
Many thanks
Huck

 

[raskew]

Hi -

Function GetNumer2(), below, will return the first numerical sequence in a string. Examples:

x = "1234567-001"
? GetNumer2(x)
1234567

y = "1234567/abc-001"
? GetNumer2
1234567

To use, copy/paste to a standard module. Call as shown.

Public Function GetNumer2(ByVal pstr As String) As Currency
'*******************************************
'Purpose:   Returns the first numerical
'           sequence in a string
'Coded by:  raskew
'Inputs:    ? getNumer2("ABC123")
'Output:    123
'*******************************************
Dim n       As Integer
Dim strHold As String
Dim strKeep As String

   strHold = Trim(pstr)
   n = Len(strHold)
   Do While n > 0
      If val(strHold) > 0 Then
         strKeep = val(strHold)
         n = 0
      Else
         strHold = Mid(strHold, 2)
         n = n - 1
      End If
   Loop
   
   GetNumer2 = val(strKeep)
        
End Function

HTH - Bob

 

[Brianwarnock]

You are going to need to write a function I think

If instr(originalfield,"/") <> 0 then
newfield= left(originalfield,instr(originalfield,"/")-1)
else
newfield = left(originalfield.instr(originalfield,"-")-1)
end if

You could try

IIf(instr(originalfield,"/") <> 0,left(originalfield,instr(originalfield,"/")-1),left(originalfield,instr(originalfield,"-")-1))

in the query I suppose, yeah that might work, not sure if Access might throw an error if / or - don't exist in the string

Brian

 

[Brianwarnock]

Bob beat me to it, also i hadn't realised that it was the first numeric sequence that was required.

Brian

 

[Huck]

Thank you Bob & Brian - I've now got what I need!
Your help is much appreciated.
Huck

 

[raskew]

Huck -

Glad it helped.

Note that while you didn't say explicitly that you were looking for the first numeric sequence, I took it from your examples that that was what you were after. If in fact what you need is all characters to the left of "/" or "-" regardless if they are were numeric or alpha, my solution won't work without modification.

Bob

 

[MSAccessRookie]

Huck -

Glad it helped.

Note that while you didn't say explicitly that you were looking for the first numeric sequence, I took it from your examples that that was what you were after. If in fact what you need is all characters to the left of "/" or "-" regardless if they are were numeric or alpha, my solution won't work without modification.

Bob

An SQL Query that looks something like this might also work:

=IIf(instr(1, YourString, "/"), left(YourString,  instr(1, YourString, "/")-1), 
IIf(instr(1, YourString, "-"), left(YourString, instr(1, YourString, "-")-1), "Unknown"))

Note: You did not indicate what to do if a string had neither "/" nor "-" (or even whether the string could have neither), so the second IIf was added to handle that case, and may not be necessary.