Re: Multiple date formats in a Table

• Previous message: wally: "Linking and importing excel tables"
• In reply to: Andrew Smith: "Re: Multiple date formats in a Table"
• Next in thread: John Nurick: "Re: Multiple date formats in a Table"
• Reply: John Nurick: "Re: Multiple date formats in a Table"
• Messages sorted by: [ date ] [ thread ]

Date: Fri, 9 Apr 2004 10:53:17 -0700

Andrew (and John):

Thank you again both for your replies.

Andrew: In regard to your most recent reply and the
previous reply, I have not yet tried running your
suggested code, because I am at home presently away from
my Uni computer for a few days over Easter.

I have a few more related queries re your coding and
other bits to explain and question you about.

Currently, I have my original set of dates as a text
field in the following formats, exactly as shown:
1: blank where no date details are known;
2: yyyy;
3: mmm-yyyy;
4: dd-mmm-yyyy.

All three part dates are as shown: e.g., 09-Apr-2004.

In traditional biological recording, I originally had the
month component recorded in Roman numerals, but this was
obviously not recognised by Access, so I converted these
as to mmm, being careful to avoid replacing "x" and "v"
values until I had done the surrounding Roman numerals:
i.e., xii to Dec, xi to Nov, ix to Sep, x to Oct, etc. to
give my current "mmm" month component.

John has suggested that I insert the word "null" into
those field entries where the current value is unknown or
blank, as in "1:" above. I'm not sure is there is an
easy way to do this by coding, or whether I should just
painstakingly go through my 6000 records and insert the
word "null" in using ctrl V or ctrl " for multiple
successive blank records.

Regarding your coding for my mixed date formats: am I
right in understanding that this will then convert
this "Text" field to a "Date/Time" field?

Some queries re your coding:

(a) In the first lot, you have suggested that
'Unrecognised format - treat all these cases as 1 Jan 1900

(i) what do you mean by "'Unrecognised format"?? Would
this include my blank spaces, where there are no values?

(ii) at present, I have my day dates as "dd", so for your
example above should I type it in as "01 Jan 1900"

(iii) is there any particular reason for using this "01
Jan 1900" date?

(b) as in (a - iii" above, from your most recent lot
of "TextToDate" code testing, I note you show the terst
date as "1 Jan 1900". Should I use "01" instead of "1"?

Incidentally... another little problem. I tried to
recreate another identical text date field (new field,
adjoining table column, different name), then did a copy
of my Date records from another identical version of my
db, but when I pasted it into the version I'm working
with, the dates went askew relating to a slightly
different ordering of my records!

Enough for now,
kind regards,
harvestman

>-----Original Message-----
>Some comments on your points:
>

• Previous message: wally: "Linking and importing excel tables"
• In reply to: Andrew Smith: "Re: Multiple date formats in a Table"
• Next in thread: John Nurick: "Re: Multiple date formats in a Table"
• Reply: John Nurick: "Re: Multiple date formats in a Table"
• Messages sorted by: [ date ] [ thread ]