Fixing short date strings in Textworks

by Peter Tyrrell Thursday, June 07, 2007 2:58 PM

The Problem

1000s of date strings in short date format like m/d/yy. Fine as long as system date settings assume month/day/year. Then system date settings change to day/month/year to conform with international standards. 1000s of date strings are misinterpreted.

E.g. 06/01/2007 Before = June 1, 2007 After = January 6, 2007

The Fix

  1. Export date field and unique ID field to delimited text file.
  2. Use regular expression to switch day and month:
    • find expression: (\d+)/(\d+)/(\d{4})
    • replace expression: \2/\1/\3
  3. Import modified file into Excel or Access, treating the date string field as DateTime so it's interpreted as a proper date, not a string.
  4. Change the format of the date field to a Long Date
    • Access query expression: Format([MyDate], "Long Date")
  5. Import the file with long date back into Textworks, matching on unique ID field; replace field values.
  6. Date strings are now in unambiguous Long Date format, e.g. MMM dd, yyyy.

UPDATE: This comic at xkcd.com is totally awesome. And relevant and stuff. comic

Adding a second virtual drive to a VM

by Ted Jardine Wednesday, June 06, 2007 12:46 PM

Jeesh. I feel like a dunce. A dunce who just wasted too much time to figure out something I already knew.

The problem: adding a secondary virtual drive to a Win XP virtual machine. No problems adding it via the Virtual PC 2007 console; no problem once booting up the VM having the drive found ("Virtual Drive found and installed"); but big problem in that the new drive is nowhere to be found in Windows Explorer.

Solution:

  1. Right-click My Computer
  2. Manage
  3. Disk Management
  4. Activate the new virtual drive
  5. Give a drive letter and partition
  6. And if you're like me, change the drive letter on your CD-Rom to E: and then switch your secondary hard drive to D:.

To switch the CD-Rom drive letter:

  1. Make sure you maximize the Computer Management window to see all drives listed (as the CD-Rom won’t be listed in the drive volume listing above)
  2. Right-click your CD-Rom and select Change Drive Letter and Paths
  3. Then in the drive volume listing above, select your secondary virtual hard drive and right-click and select Change Drive Letter and Paths.

I guess virtual drives are a little too virtual to instantly become available in explorer ready and waiting for formatting.

What's most painful about this is that all along while googling everywhere trying to come up with the magic search term combination (i.e. "Virtual PC 2007 secondary hard disk" doesn't quite work - I KNOW IT'S A GOOD IDEA TO INSTALL YOUR VM ON A SECONDARY DRIVE!!!), in the back of my mind I'm thinking I should look in Computer and Disk Management...

Btw, If google doesn't work right away and it's Windows related, check out Microsoft's Community Forums.

No time for Textworks date fields

by Peter Tyrrell Friday, June 01, 2007 12:33 PM

Ha ha! That was a pun, that was. "No time for Textworks date fields." Phew... that is the kind of half-inflated rubber-mattress-smelling type of humour I am capable of, here on this highly informative forum.

So I discovered the other day, quite by accident, that Inmagic textbase Date fields are exactly that and no more. By which I mean: Date fields are not DateTime fields.

Perhaps on the surface this seems a trivial distinction. Let me assure you, my dear confidant, it is not.

You can enter time information in a non-strict Date field, or one that has trailing text enabled, but the time you enter is just a string. It is not understood as a chronological statement. You might as well type "blah blah blah" instead of "09:00:00 AM" for all the good it does you, because you can't search for, or sort by, time. You and I know that 1:00:00 PM comes after 12:00:00 PM, but Inmagic doesn't.

Here's an example where I told the report to sort by a date field. Note that it is NOT sorting chronologically, but rather, numerically.

CropperCapture[4]

I'm so surprised that there is no way to handle chronological data. In the languages, programming environments, and database systems I work with, it's just assumed that Date fields, classes, and variables are date-plus-time: DateTime objects. The assumption is such that you can call a DateTime object a Date, in fact, and everyone already knows you really mean DateTime but aren't pretentious enough to have to say it. (The javascript Date object is an example off the top of my head.)

A DateTime object can of course show a date without time, but that's just a matter of formatting. The information about time is still in there. And, even if you instantiate a DateTime object with a date string and don't include the time, the time defaults to 00:00:00.

Anyway, I'm not sure what to do about this.

Month List