Extracting Data from a txt box displayed between ( ) & either side o

[RenaissanceJo]

Extracting Data from a txt box displayed between ( ) & either side of - or /

Hi, I have a problem with Access 2K that is driving me to distraction. I
have tried everything from querys to code but I do not think my level of
Access/VB knowledge is good enough to figure this out on my own.

Here's my problem.

I have two text boxes on an imported table, Lets call them [txt1] and
[txt2].

[txt1] displays data in "Short Time format", inclosed in brackets like
(17:35).
I need to be able to extract the data contained between the brackets so it
looks like 17:35 using either a query or code so I can then append the
extracted data to another table.

[txt2] displays two set of data seperated by either a - or a / like
Microsoft - Access 2000 or Microsoft / Access 2000.
I need to be able to extract the data either side of the - or / and append
it to different txt boxes in a new table, again either by code or a query.


Can anyone point me in the right direction please.

Jo [sig][/sig]

 

[jimmythegeek]

Copy everything between the lines into a new module and save it as anything you want.

====================
Public Function GetLeftSide(strValue As String) As String
Dim intPlace As Integer

intPlace = InStr(1, strValue, "-&quot
If intPlace > 1 Then
GetLeftSide = Left(strValue, intPlace - 2)
Else
intPlace = InStr(1, strValue, "/&quot
If intPlace > 1 Then
GetLeftSide = Left(strValue, intPlace - 2)
Else
GetLeftSide = "No dash or slash"
End If
End If
End Function

Public Function GetRightSide(strValue As String) As String
Dim intPlace As Integer

intPlace = InStr(1, strValue, "-&quot
If intPlace > 1 Then
GetRightSide = Mid(strValue, intPlace + 2)
Else
intPlace = InStr(1, strValue, "/&quot
If intPlace > 1 Then
GetRightSide = Mid(strValue, intPlace + 2)
Else
GetRightSide = "No dash or slash"
End If
End If
End Function
====================

Now, In a new query, copy the following to three columns (changing [txt1] and [txt2] appropriately):

Time: Mid([txt1],2,(Len([txt1])-2))
Mfg: GetLeftSide([txt2])
Product: GetRightSide([txt2])

Now run the query! [sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]