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

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(y)
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

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

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

Brian

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

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

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.