By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,833 Members | 2,076 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,833 IT Pros & Developers. It's quick & easy.

Parsing Linked Table Data?

P: 8
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
Share this Question
Share on Google+
11 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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

P: 8
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
Expert Mod 10K+
P: 14,534
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
Expert Mod 15k+
P: 31,304
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
Expert Mod 10K+
P: 14,534
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
Expert Mod 15k+
P: 31,304
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
Expert Mod 10K+
P: 14,534
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
Expert Mod 15k+
P: 31,304
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

P: 8
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
Expert Mod 10K+
P: 14,534
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
Expert Mod 15k+
P: 31,304
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

Post your reply

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