Need help Split phrase

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

This might be really simple but i need help figuring out how to do it.

If field "Ref#" contains "091/16"
How can i split the numbers to use only the "091" in another field?

Thanks in Advance.
Ernie
 
Hi all,

This might be really simple but i need help figuring out how to do it.

If field "Ref#" contains "091/16"
How can i split the numbers to use only the "091" in another field?

Thanks in Advance.
Ernie
Click to expand...

Using an unbound text control.....
If the number of characters before the / can vary:

=Left([FieldName],InStr([FieldName],"/")-1)

If there are always 3 characters, then
=Left([FieldName],3)

This value should not be stored in any table. Anytime you need the
value, re-compute it, as above.
 
Field: BeforeSlash: LEFT(YourField, Instr(1,YourField & "/","/")-1)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
fredg,
i tried the following code using the click event of the command buttom, to
display in a msgbox but i got the error below:

Private Sub Test ()
fpart = Left([Forms]![Lodgement]![Lodgement_No], 3)
MsgBox = fpart
End Sub

ERROR: Compile error: "Function call on left-hand side of assignment must
return Variant or Object"


Thanks your reply
Ernie

fredg said:
Hi all,

This might be really simple but i need help figuring out how to do it.

If field "Ref#" contains "091/16"
How can i split the numbers to use only the "091" in another field?

Thanks in Advance.
Ernie
Click to expand...

Using an unbound text control.....
If the number of characters before the / can vary:

=Left([FieldName],InStr([FieldName],"/")-1)

If there are always 3 characters, then
=Left([FieldName],3)

This value should not be stored in any table. Anytime you need the
value, re-compute it, as above.
Click to expand...
 
Hi spencer

I got the following error when i use your code.

Your Code:
Private Sub Test ()
fpart = Left([Lodgement_No], InStr(1, [Lodgement_No] & "/", "/") - 1)
MsgBox = fpart
End Sub

ERROR: Compile error: "Function call on left-hand side of assignment must
return Variant or Object"

thank you for the reply i appreciate it.
Ernie
 
Hi Bewyched,

I tried your coding:
fpart = Split(Me.Lodgement_No, "/")
Textbox1 = fpart(0)

But got the following error:
Compile error: Expected Array

Thank you for replying.
Ernie
 
Msgbox fPart

NOT

MsgBox = fPart



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

ernie said:
Hi spencer

I got the following error when i use your code.

Your Code:
Private Sub Test ()
fpart = Left([Lodgement_No], InStr(1, [Lodgement_No] & "/", "/") - 1)
MsgBox = fpart
End Sub

ERROR: Compile error: "Function call on left-hand side of assignment must
return Variant or Object"

thank you for the reply i appreciate it.
Ernie

John Spencer said:
Field: BeforeSlash: LEFT(YourField, Instr(1,YourField & "/","/")-1)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Click to expand...
Click to expand...
 
I use this:

Public Function SafeSplit(V As Variant, _
Delim As String, Item As Long) As Variant

On Error Resume Next 'to return Null if Item is out of range
SafeSplit = Split(V, Delim)(Item)

End Function

Assuming you're using it in a query, call it with something like this:

SafeSplit([Ref#], "/", 0)
 
Thank you, Spencer it worked just fine. i appreciate your help.
Ernie

John Spencer said:
Msgbox fPart

NOT

MsgBox = fPart



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

ernie said:
Hi spencer

I got the following error when i use your code.

Your Code:
Private Sub Test ()
fpart = Left([Lodgement_No], InStr(1, [Lodgement_No] & "/", "/") - 1)
MsgBox = fpart
End Sub

ERROR: Compile error: "Function call on left-hand side of assignment must
return Variant or Object"

thank you for the reply i appreciate it.
Ernie

John Spencer said:
Field: BeforeSlash: LEFT(YourField, Instr(1,YourField & "/","/")-1)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi all,

This might be really simple but i need help figuring out how to do it.

If field "Ref#" contains "091/16"
How can i split the numbers to use only the "091" in another field?

Thanks in Advance.
Ernie
Click to expand...
Click to expand...
Click to expand...
 
Back
Top