Hi, I am using MS Acces

[bf2mad]

Hi,

I am using MS Access 2000 and I have a table(FBR Passthru Sessions) that contains a field(Field 1) where data is stored as below

Colin Bottomley/GIS/CSC to EMEA-ML01/SRV/CSC

I want to be able to seperate this data idealaly using a query so that I then have three fields Name, Server1, Server2 and the data would then be displayed as below.

Name | Server1 | Server2
--------------------------------------------------
Colin Bottomley | /GIS/CSC | EMEA-ML01/SRV/CSC

Any ideas ?

Thanks

Phil

 

[fneily]

On a form, create a textbox for Field1. On its property sheet, call it name. Create two more text boxes. I name them firstname, lastname. This is on their property sheet. Click on the text box that's bound to Field1, go to the Event tab of the control and click on After Update. Click on the build button (...) and type in the followingor just copy and paste from here)

Private Sub name_AfterUpdate()
Dim SPos As Integer, EPos As Integer
Dim s As String
Dim SLPos As String, FLen As String
s = Me![name]
SPos = 1
EPos = InStr(SPos, s, "/&quot - 1
Me![firstname] = Trim(Mid(s, SPos, EPos))
SLPos = InStr(s, "to &quot + 3
FLen = Len(s)
Me![lastname] = Trim(Mid(s, SLPos, FLen))
End Sub

As long as your field is the way you presented it, especially the part "server1 to server2", then this will put the name in the first text box and server2 info in the second text box. I'm in a hurry so I didn't do the middle part yet. If I do it, I'll post it, but this may help you in your programming. Don't forget in the SLPos line, it's the word to and a space inbetween the quotes.

Neil

 

[fneily]

I created a third text box and named it midname. The code for the Field1 event now looks like this:

Private Sub name_AfterUpdate()
Dim SPos As Integer, EPos As Integer
Dim s As String
Dim SLPos As String, FLen As String
s = Me![name]
SPos = 1
EPos = InStr(SPos, s, "/&quot - 1
Me![firstname] = Trim(Mid(s, SPos, EPos))
SLPos = InStr(s, "to &quot + 3
FLen = Len(s)
Me![lastname] = Trim(Mid(s, SLPos, FLen))
EPos = EPos + 1
SLPos = SLPos - 4
Me![midname] = Trim(Mid(s, EPos, SLPos - EPos))
End Sub

This separates your field into the needed components.

Neil