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

[RenaissanceJo]

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]

 

[Robertd]

Hi Jo

if the data items are strings then the vb string functions will allow you to extract the required part of the string for further processing.
check these functions out

Mid$(string, start, length)
Left$(string, length)
and others (in the help system)
you could do something like

if Left$(combobox1data, 1) = "(" then

tempstring = Mid$(combobox1data, 2, 6)
' what ever else you need to do

end if

for the second requirement

dim combobox2len as int
dim combobox2index as int
dim testval as string
dim firstpart as string
dim secondpart as string

combobox2len = Len(combobox2data)
combobox2index = 1

While combobox2index <= combobox2len

testval = Left$(combobox2data, combobox2len)
if testval = &quot;-&quot; or testval = &quot;/&quot; then
' you have found the seperator
firstpart = left$(combobox2data, combobox2index - 1)
secondpart = right$(combobox2data, combobox2len - ombobox2index - 1)
' exit while loop
combobox2index = combobox2len + 1
end if

combobox2index = combobox2index + 1

Wend

well that should get you started

see ya
Robert

[sig][/sig]

 

[PaulF]

Ok you can try this, create a Public Function to determine where the / or - is at in the text

Public Function ParseTextFld(strTextFld As String) As Integer
Dim intLen As Integer
intLen = Len(strTextFld)
If InStr(1, strTextFld, &quot;/&quot > 0 Then
ParseTextFld = InStr(1, strTextFld, &quot;/&quot
ElseIf InStr(1, strTextFld, &quot;-&quot > 0 Then
ParseTextFld = InStr(1, strTextFld, &quot;-&quot
Else
ParseTextFld = 0
End If

End Function


then you can build a query that contains the following

Assume that the field names are
field with time data = textfld
field with text data = timefld

left value: IIf(ParseTextFld([textfld])=0,[textfld],Left([textfld],ParseTextFld([textfld])-1))

right value: IIf(ParseTextFld([textfld])=0,Null,Right([textfld],Len([textfld])-ParseTextFld([textfld])-1))

Adj Time: Mid([timefld],2,Len([timefld])-2)



If you get the expected results you can either convert the select query to an update query or a maketable query.

HTH
PaulF [sig][/sig]