Ok - let's use your example to work through this:
"Bill.Gates connected for 0 Hours, 7 Minutes, 36 Seconds"
The name has a variable number of characters, so we can't rely on it to know
where to end the name.
There are multiple spaces between the name and the word "connected". If that
is consistently more than one space, we can use it to identify the split.
The word "connected" is always in your record, so we can rely on it to know
the start of the second portion of the information.
The time portions are always a number followed by a "unit" word.
OK? Now let's start on how to get this information parsed (note that I'm
using the fieldname [StringCom] for your text string comment):
(1) Let's use the location of the period in the name to identify the end of
the first name:
Trim(Left([StringCom], InStr([StringCom], ".") - 1))
(2) Let's use the start of the word "connected" to identify the split
between name and the connection time info so that we can find the last name:
Trim(Mid([StringCom], InStr([StringCom], ".") + 1,
InStr([StringCom], "connected") - InStr([StringCom], ".") - 1))
(3) Let's use the fixed length of the text string "connected for " to find
the hours number:
Val(Mid([StringCom], InStr([StringCom], "connected") + 13))
(4) Let's use the location of the first comma after the word "connected" to
find the minutes number:
Val(Mid([StringCom], InStr(InStr([StringCom], "connected"),
[StringCom], ",") + 1))
(5) Let's use the location of the last comma to find the seconds number:
Val(Mid([StringCom], InStrRev([StringCom], ",") + 1))
--
Ken Snell
<MS ACCESS MVP>
J. Shrimp said:
Typical record: "Bill.Gates connected for 0 Hours,7 Minutes, 36
Seconds".
Name is first X number of characters - can be a dozen or 50, who knows?
Ken Snell said:
It'll be easier to suggest some methods if you post some examples of
what
is
in the log file as records.
--
Ken Snell
<MS ACCESS MVP>
JumboShrimps said:
Have a list of log files from Windows 2000 server.
In the same field is the name of person logging on
concatenated with the text "connected for 0 Hours,
7 Minutes, 36 Seconds".
I can highlight the column in the table
press <ctrl> H,
and type "replace connected for 0 Hours" with ""
(remove the string 'cause it's unecessary) BUT
how can I do that with a query so I can build
a macro of queries that strip out
unnecesary verbage from the log files?
Something like: (from Dev Ashsish)
Right$([Name],Len([Name])- InStr(1,[Name],",")-1)
I guess.....
