Stripping only required data from a long text line

[Mark B]

I have to produce a monthly report which requires only 3 pieces of data from
a long text string acquired from another source.

this is an example line:

itime=1217886061 date=2008-08-04 time=22:40:59 devname=FUCHS_STOKE_100A
device_id=FG100A3907502636 log_id=0101023002 type=event subtype=ipsec
pri=notice vd=root loc_ip=217.40.90.181 loc_port=4500 rem_ip=90.202.60.7
rem_port=4500 out_if=wan1 vpn_tunnel=Remote_Workers_VPN_P1
cookies=59ee20bc69051f2d/d88fa2237634903a action=negotiate status=success
msg="XAUTH user: JohM authentication successful"

The bits I need to strip are the 10 chars after "date=", the 8 chars after
"time=" and the final bit of the e.g XxxX authentication successful (without
the quotes and omitting the "XAUTH user: " bit.

The length of these lines is not uniform which makes char selection a little
more difficult. Also the number of lines in the file varies on a monthly
basis.

What I would like to achieve is - to import the file into a new table
containing only the 3 fields and I guess a primary key - the table name must
remain the same each month for the queries I have written to work following
its creation so it would need to rename last month's for history purposes.

Any and all advice on the best way to perform this would be greatly
appreciated.

Thanks in advance.

Mark

 

[Jeff Boyce]

Have you looked into Access HELP on the InStr() function? It seems like
using InStr() to find "date=" and " time=" (note the preceding space before
'time', since your string also contains "itime=") and "XAUTH user: " would
give you a way to know where to start. Then you could use the Mid()
function to grab as many characters (or "the rest" for the last field) as
you needed.

Regards

Jeff Boyce
Microsoft Office/Access MVP