473,326 Members | 2,104 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Parsing Linked Table Data?

During login and logout, the workstations on my network dump some info into a text file. Part of this info is the current date and time, rendered by doing "echo %date%,%time%" at the command line.

Here's a sample of the data in the linked file:
Expand|Select|Wrap|Line Numbers
  1. Ethan,Thu 01/04/2007, 8:56:52.73,T24-03,0,Logged Off 
  2. Alex,Thu 01/04/2007,10:59:18.89,T24-05,1,Logged On 
  3. Alex,Thu 01/04/2007,12:28:39.45,T24-05,0,Logged Off 
  4. joe,Thu 01/04/2007,12:29:12.23,T24-05,1,Logged On 
As you can see, the date is rendered as DDD MM/DD/YYYY, and the time is a 24 hours clock, with no leading 0.

Is there a way to get Access to trim the first for characters of the date field and to replace the leading space of the time field with a 0? I want to run Query based reports on the data, sorted by date and time, but the leading three letter day code and the lack of a leading 0 on times earlier than 10:00:00 is making a hash of the report.

I suppose I could just not use a linked table, and instead delete and re-import the data whenever I need to run the report, if that's needed as a first step, then that's no problem. Reports would only be generated once a month or so anyway.

Attached is the database, with the table imported rather than linked. The database.

Here's the command the generates the info at logon and writes it to the text file:
Expand|Select|Wrap|Line Numbers
  1. echo %USERNAME%,%DATE%,%TIME%,%COMPUTERNAME%,1,Logged On >> "\\server\shared\Network\stats.txt"
The version run at logoff is the same, except that it replaces "1" with "0" and "Logged On" with "Logged Off".

Any assistance is greatly appreciated.
Jan 25 '07 #1
11 2377
MMcCarthy
14,534 Expert Mod 8TB
I've used Field1, Field2, etc as the fieldnames as I don't know what they are and for the tablename substitute the linked file name.

Expand|Select|Wrap|Line Numbers
  1. SELECT Field1, Format(Right([Field2],10), #dd/mm/yyyy#), Format([Field3], #hh:nn:ss#), Field4, Field5
  2. FROM Tablename;
  3.  
Mary
Jan 25 '07 #2
mmccarthy, thanks for the quick response.

My SQL in the Query "Overall" now looks like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT Stats.User, Format(Right([Stats.Date],10),"mm/dd/yyyy") AS Expr1, Format([Stats.Time],"hh:nn:ss") AS Expr2, Stats.Status, Stats.Workstation
  2. FROM Stats;
It trims the date correctly but times before 10:00 are not getting a leading 0.

When I entered it as you specified, using #mm/dd/yyyy# and #hh:nn:ss#, Access said that the formula was incorrect.
Jan 25 '07 #3
MMcCarthy
14,534 Expert Mod 8TB
Try this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT Stats.User, Format(Right([Stats.Date],10),"mm/dd/yyyy") AS Expr1, Format([Stats.Time],"Long Time") AS Expr2, Stats.Status, Stats.Workstation
  2. FROM Stats;
  3.  
Jan 25 '07 #4
NeoPa
32,556 Expert Mod 16PB
mmccarthy, thanks for the quick response.

My SQL in the Query "Overall" now looks like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT Stats.User, Format(Right([Stats.Date],10),"mm/dd/yyyy") AS Expr1, Format([Stats.Time],"hh:nn:ss") AS Expr2, Stats.Status, Stats.Workstation
  2. FROM Stats;
It trims the date correctly but times before 10:00 are not getting a leading 0.

When I entered it as you specified, using #mm/dd/yyyy# and #hh:nn:ss#, Access said that the formula was incorrect.
You've done a good job there fixing the small error. Formatting dates for all the different situations can get very complicated so there is sometimes confusion of what goes where etc.
This version simply gets the data into Date/Time format :
Expand|Select|Wrap|Line Numbers
  1. SELECT [User],CDate(Right([Date],10)) AS StatsDate,
  2.        CDate([Time]) AS StatsTime,Status,Workstation
  3. FROM Stats;
If you then want it to show as text values use :
Expand|Select|Wrap|Line Numbers
  1. SELECT [User],Format(CDate(Right([Date],10)),'mm/dd/yyyy') AS StatsDate,
  2.        Format(CDate([Time]),'hh:nn:ss') AS StatsTime,Status,Workstation
  3. FROM Stats;
If you require the time displayed in 24 hour format then it's "HH:nn:ss".
In SQL code like this it's more standard to use single quotes (') for strings (Quotes (') and Double-Quotes (") - Where and When to use them.).
Jan 27 '07 #5
MMcCarthy
14,534 Expert Mod 8TB
You've done a good job there fixing the small error. Formatting dates for all the different situations can get very complicated so there is sometimes confusion of what goes where etc.
This version simply gets the data into Date/Time format :
Expand|Select|Wrap|Line Numbers
  1. SELECT [User],CDate(Right([Date],10)) AS StatsDate,
  2.        CDate([Time]) AS StatsTime,Status,Workstation
  3. FROM Stats;
If you then want it to show as text values use :
Expand|Select|Wrap|Line Numbers
  1. SELECT [User],Format(CDate(Right([Date],10)),'mm/dd/yyyy') AS StatsDate,
  2.        Format(CDate([Time]),'hh:nn:ss') AS StatsTime,Status,Workstation
  3. FROM Stats;
If you require the time displayed in 24 hour format then it's "HH:nn:ss".
In SQL code like this it's more standard to use single quotes (') for strings (Quotes (') and Double-Quotes (") - Where and When to use them.).
Thanks Ade. Should have checked out your link before I posted this. ;)
Jan 27 '07 #6
NeoPa
32,556 Expert Mod 16PB
Thanks Ade. Should have checked out your link before I posted this. ;)
If I thought you had anything to apologise for I'd be an idiot.
If you were to check out every minute detail before you posted there would be a lot of members who you wouldn't get the time to help as you do.
A detail here or there is just just gives the impression of being human ;)
Jan 27 '07 #7
MMcCarthy
14,534 Expert Mod 8TB
If I thought you had anything to apologise for I'd be an idiot.
If you were to check out every minute detail before you posted there would be a lot of members who you wouldn't get the time to help as you do.
A detail here or there is just just gives the impression of being human ;)
All part of the programming, can't believe I forgot about the capital H's though.
Jan 27 '07 #8
NeoPa
32,556 Expert Mod 16PB
I wasn't even sure 24 Hr format was required until I just reread the first post.
Having reread it though, they mention ordering the data by Date / Time so, to do that :
Expand|Select|Wrap|Line Numbers
  1. SELECT [User],Format(CDate(Right([Date],10)),'mm/dd/yyyy') AS StatsDate,
  2.        Format(CDate([Time]),'hh:nn:ss') AS StatsTime,Status,Workstation
  3. FROM Stats
  4. ORDER BY CDate(Right([Date],10) & ' ' & [Time]);
NB. With the sorting done this way the layout of the Date and Time fields for selection is purely optional. It could be shown exactly as found if that is preferred. Showing as a single Date/Time item though,could be done in the SELECT clause as :
Expand|Select|Wrap|Line Numbers
  1. ...Format(CDate(Right([Date],10) & ' ' &
  2. [Time]),'mm/dd/yyyy HH:nn:ss') AS DateTime,...
(Line break just for display purposes in the forum - not required.)
Jan 27 '07 #9
Thanks for all the replies. Yet more about Access and SQL that I'd never guessed was there. I'm sorry for not replying sooner, this project was put on hold in favor of others.

Using the SQL string:
Expand|Select|Wrap|Line Numbers
  1. SELECT [User],Format(CDate(Right([Date],10)),'mm/dd/yyyy') AS StatsDate,
  2.        Format(CDate([Time]),'hh:nn:ss') AS StatsTime,Status,Workstation
  3. FROM Stats
  4. ORDER BY CDate(Right([Date],10) & ' ' & [Time]);
I get a "Data type mismatch in criteria expression" error. I think this is because ALL the data being brought in is purely text. Since the incoming date and time fields don't match anything that Access recognizes, it calls everything text.

I'm beginning to think that part of my problem lies in how the data is collected. Here's the command line that is run:
Expand|Select|Wrap|Line Numbers
  1. echo %USERNAME%,%DATE%,%TIME%,%COMPUTERNAME%,1,Logged On >> "\\server\shared\Network\stats.txt"
.

This produces a comma separated line like this:
Expand|Select|Wrap|Line Numbers
  1. brad,Thu 01/04/2007, 7:39:56.39,CAD-01,1,Logged On
Note the leading space on the time stamp, this space is absent on all times 10:00 and higher.

The first line of the stats.txt file, to give Access field names, is this:
Expand|Select|Wrap|Line Numbers
  1. User,Date,Time,Workstation,TF,Status 
So, initially the time reported by the system doesn't have a leading 0. I suspect that to get a leading 0 there I'm going to have to do some fancy tricks in the batch file or just hand edit the file before running reports with a find and replace.

The first answer given by mmccarthy solved the problem with the date. Once the three letter day code and space are trimmed off, Access happily sorts them in ascending order.

Should I abandon linking the table to an outside file and just re-import it when I want to run reports? At least that way I could force a format for a given column.
Jan 30 '07 #10
MMcCarthy
14,534 Expert Mod 8TB
I think the problem is in the time. Try this and see what happens ...

Expand|Select|Wrap|Line Numbers
  1. SELECT [User],Format(CDate(Right([Date],10)),'mm/dd/yyyy') AS StatsDate,
  2.        Format(CDate(Left([Time],8)),'HH:nn:ss') AS StatsTime,Status,Workstation
  3. FROM Stats
  4. ORDER BY CDate(Right([Date],10) & ' ' & CDate(Left([Time],8));
  5.  
Jan 31 '07 #11
NeoPa
32,556 Expert Mod 16PB
If that doesn't work Austin, it'll be because the import routine is trimming the spaces from the text fields before interpreting them. In which case we are looking for something a little less simple than Left([Time],8) but we'll cross that bridge only if we come to it. We rely on you to tell us if that works or not.
Should I abandon linking the table to an outside file and just re-import it when I want to run reports? At least that way I could force a format for a given column.
No, I think this can (and will) work fine for you. It is merely a question of understanding precisely how Access behaves then handling precisely that situation. We're down to the wire now - only the simple stuff left.
Jan 31 '07 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
5
by: deko | last post by:
How to run action query against linked table? I have an Access 2003 mdb with an Excel 2003 Workbook as a linked table. When I attempt to run an action query against the linked table I get this...
3
by: Michael Plant | last post by:
Hello one and all. I have a stored table in my database and the form I'm using is based on a query that draws data from my stored table and a linked table. The linked table is a *.txt file. ...
7
by: Joe | last post by:
I am using Access 2003 and are linking to an Oracle 9i ODBC datasource (using Oracle ODBC drivers). After linking the tables in Access, I inspect the data contained in the linked tables. For...
10
by: Jim Devenish | last post by:
I have a split front end/back end system. However I create a number of local tables to carry out certain operations. There is a tendency for the front end to bloat so I have set 'compact on...
0
by: Grip | last post by:
Hi, I have gone throught the group and Microsoft's online help and have seen many suggestions but I am still seeking clarity: 1. I have an excel spreadsheet. Column A contains text that may...
9
by: erick-flores | last post by:
If you have access to the database that the linked table is in, modify it there. You can't modify a linked table. Alternatively, you can import the linked table, then it won't be linked any more...
2
by: nedelm | last post by:
My problem's with parsing. I have this (arbitrary, from a file) string, lets say: "Directory: /file{File:/filename(/size) }" I would like it to behave similar to LaTeX. I parse it, and then I...
2
by: troy_lee | last post by:
What is the best way of copying all the records from a linked table into a replica table that is local on my computer? This is for development work at home where I can not access the main table. Is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.