HELP WITH MID FUNCTION 1

[Sdaddy]

I am really tired and should know this.... but I need help getting an ID field on a text that is right justified to grab the up to 4 number id from right to left. PLEASE HELP!!! I'm stuck until i figure this out.

Here's my function
Option Compare Database
Option Explicit

Public Function TU1()

Dim dbsTime As Database
Dim rsttable As recordset
Dim f As String
Dim a As Integer, b As Integer
Dim datastring As String

f = InputBox("What is the TXT file", "Text file", "&quot
f = "L:\HFC Trouble Ticket Database\" & f & ".TXT"

Open f For Input As #1
Set dbsTime = CurrentDb()
Set rsttable = CurrentDb().OpenRecordset("TmeUtl&quot

Dim mu As Integer, ID As String, min As Integer
Dim datee As String, fn As String

datee = ""
mu = 0
ID = 0
min = 0
fn = ""
b = 0

Do While Not EOF(1)

a = 0

Line Input #1, datastring

If Mid(datastring, 4, 1) = "MU: " Then mu = LTrim(RTrim(Mid(datastring, 5, 3)))

If Mid(datastring, 37, 6) = "Date: " Then datee = Mid(datastring, 43, 8)

If Mid(datastring, 9, 1) = "0" Then a = 1
If Mid(datastring, 9, 1) = "1" Then a = 1
If Mid(datastring, 9, 1) = "2" Then a = 1
If Mid(datastring, 9, 1) = "3" Then a = 1
If Mid(datastring, 9, 1) = "4" Then a = 1
If Mid(datastring, 9, 1) = "5" Then a = 1
If Mid(datastring, 9, 1) = "6" Then a = 1
If Mid(datastring, 9, 1) = "7" Then a = 1
If Mid(datastring, 9, 1) = "8" Then a = 1
If Mid(datastring, 9, 1) = "9" Then a = 1

If a = 1 Then
b = 0
ID = mid(datastring, 1,5)

a = 0
End If

If Mid(datastring, 1, 7) = "Summary" Then b = 1

If Mid(datastring, 70, 1) = "." And Mid(datastring, 73, 1) = "%" And b = 0 Then

fn = Trim(Mid(datastring, 33, 22))
min = 60 * Trim(Mid(datastring, 59, 3)) + Trim(Mid(datastring, 63, 2))
If b = 0 Then AddInTU rsttable, datee, mu, ID, fn, min
End If

Loop


Close #1

End Function
Public Function AddInTU(rsta, datea, mua, IDa, fna, mina)

With rsta
.AddNew
!IEXid = IDa
!Date = datea
!Function = fna
!minutes = mina
!Time = Now()
!mu = mua
.Update
.Bookmark = .LastModified
End With

End Function

As a follow up, my problem is that I need the line that reads
ID = mid(datastring, 1,5) to start at the 9th position and look back 4 positions and input that into the first column of my table. PLEASE HELP

 

[RickSpr]

I presume that positions 5 through 9 of the record contain the number, right justified with leading spaces. You can extract the numeric value with:
Val(Mid(datastring, 5, 5))

If you need a string with leading zeros instead, use:
Format(Val(Mid(datastring, 5, 5)), "00000&quot

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Sdaddy]

u is da man. Thanks.. I didn't think anyone would respond so I cheated and came up with this.


LTrim(Left(datastring, 9))

I am going to use yours though because it will be much more efficient. Thanks!!!!

 

[RickSpr]

BTW, the expression
LTrim(RTrim(Mid(datastring, 5, 3)))
can be rendered more simply as
Trim(Mid(datastring, 5, 3))

Trim() trims spaces from both the left and right ends.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein