Trim? in SQL Query

[doodle]

greetings from ky

i have coded my forms and reports so that when a user opens them it
creates a log in a user table to tell me how often they are used.

The result of WhosOn() could be something like this:
ADRAUGHN-2045;PAURAYBURN-1513;SPINDRM-983;

I need to find a way to code this so that it will remove the hyphen and
all to the right of it. So that the above would be this:

ADRAUGHN,PAURAYBURN,SPINDRM

HELP!!!! Here's my code:

-doodle

Private Sub Form_Open(Cancel As Integer)
On Error GoTo tagError
Dim strSQL As String

DoCmd.SetWarnings False

strSQL = "Insert INTO tblSys_TrackUser(User,UserObject)" & _
"Values ('" & WhosOn() & "','" & Me.Name & "')"
DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

DoCmd.Restore


Exit Sub

tagError:
MsgBox Err.Description

End Sub

 

[James A. Fortune]

greetings from ky

i have coded my forms and reports so that when a user opens them it
creates a log in a user table to tell me how often they are used.

The result of WhosOn() could be something like this:
ADRAUGHN-2045;PAURAYBURN-1513;SPINDRM-983;

I need to find a way to code this so that it will remove the hyphen and
all to the right of it. So that the above would be this:

ADRAUGHN,PAURAYBURN,SPINDRM

HELP!!!! Here's my code:

-doodle

Private Sub Form_Open(Cancel As Integer)
On Error GoTo tagError
Dim strSQL As String

DoCmd.SetWarnings False

strSQL = "Insert INTO tblSys_TrackUser(User,UserObject)" & _
"Values ('" & WhosOn() & "','" & Me.Name & "')"
DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

DoCmd.Restore


Exit Sub

tagError:
MsgBox Err.Description

End Sub

If you can nip the problem where it starts,

Replace

sUser = sUser & Chr(.bUser(i)) & ";"

with

sUser = sUser & Left(Chr(.bUser(i)), InStr(1, Chr(.bUser(i)), "-") -
1) & ";"

in the WhosOn() function. If you cannot do that (perhaps because the
WhosOn() function is used in other places), try:

....
Dim strIn As String
....
strIn = WhosOn()
Do While InStr(1, strIn, "-", vbTextCompare) > 0
strIn = TrimBetween(strIn, "-", ";")
Loop
....

Public Function TrimBetween(strIn As String, strLSymb As String,
strRSymb As String) As String
Dim intLeft As Integer
Dim intRight As Integer
Dim strTemp As String

TrimBetween = ""
If Len(strIn) = 0 Then Exit Function
TrimBetween = strIn
If Len(strIn) < 3 Then Exit Function
'The symbols must exist, be at least two chars into the string
'and have first left symbol before first right symbol
intLeft = InStr(1, strIn, strLSymb)
intRight = InStr(1, strIn, strRSymb)
If intLeft <= 1 Or intRight <= 1 Or intRight < intLeft Then
Exit Function
End If
If intRight = Len(strIn) Then
strTemp = Mid(strIn, 1, intLeft - 1)
Else
strTemp = Mid(strIn, 1, intLeft - 1) & "," & Mid(strIn, intRight + 1,
Len(strIn) - intRight)
End If
TrimBetween = strTemp
End Function


Test results:

strIn = "ADRAUGHN-2045;PAURAYBURN-1513;SPINDRM-983;" =>
"ADRAUGHN,PAURAYBURN,SPINDRM"
strIn = "ADRAUGHN-2045;" => "ADRAUGHN"
strIn = "" => ""

I'm not sure my newsreader will respond right now. If not, I'll reply
again when Google gets the original message.

I hope this helps,

James A. Fortune
(e-mail address removed)