Multiple date formats in a Table

[harvestman]

I have a quite large Access db with about 6,000 records,
each with 32 fields divided into three tables. I'm having
a problem with dates in one of my tables, especially since
I'm trying to get the dates listed in chronological order.

My db relates to my Uni thesis here in Oz, recording
occurrences of invertebrate species with dates running
from 1883 to 2004. I have reconfigured my short date under
Regional Settings Properties to read as: dd-mmm-yyyy
(e.g., 07-Apr-2004). However, the problem I have is the
fact that for about a quarter of my records I only have
a "mmm-yyyy" date or "yyyy" date and have no date
information at all for about a third of my records.

If I select "Date/Time" as my data type in the field I
have shown for the Collection Date, I lose all my "mmm-
yyyy" and "yyyy" dates. Similarly, under the General Tab
for Field Properties, in the first property: "Format", I
can only select a single (particular) date style
e.g., "short date", rather than the three date style
values I want to select... i.e.,
dd-mmm-yyyy e.g., 07-Apr-2004
mmm-yyyy e.g., Apr-2004
yyyy e.g., 2004

So if anyone can advise me here, it would be much
appreciated... and in the instances where I have no date
info, can someone please advise if I should leave the
value for this field blank, or write something like "null"
or "unknown".

Many thanks...

 

[Andrew Smith]

From what you say I assume that you currently have the dates stored in a
text field - is that correct?

Access stores dates as numbers where 1 represents 31 Dec 1899 so today is
stored as (7 Apr 2004) 38084. If you change your text(?) field to a date
field then I think that Access will do its best to convert the text into a
date. However, text that it does not recognise as a date will get discarded.
So 2004 or Apr-2004 could well get lost.

In any case, a date field could not store just the year 2004 - it would have
to store a specific date in that year. You could decide to store it as 1 Jan
2004, but then you would not be able to tell if that really meant 1 Jan 2004
or just 2004. When it came to formatting the date you would either have to
display it as 1 Jan 2004, or you would have to just display the year for all
the dates in the database.

So, for table storage purposes you are probably best leaving things as they
are.

However, for sorting then you do need to convert your text into real dates
in some way. You will have to decide how you want to sort a value of 2004,
or Apr-2004. Realistically you only have two options - treat them as if they
were on the first or last of the year/ month.

The conversion of your existing text format date should be done in a query,
leaving the original text in place in the table. This will allow you to sort
by the converted value, but display the original text so you don't then have
any problems with formating the dates in reports etc. The big problem, of
course, is how you convert the text into a date. I suspect you will have to
write a VBA function to do this. How easy this is will depend on how
consistent the date entries are in your table.

If they are all either; a recognisable date (eg 1-Apr-2004, or 1/4/04), or a
year (eg 2004), or a month and year in the format Apr-2004, then it should
be fairly simple. The following code should work in this case - it will
return the first of the year/ month for partial dates, the full date for
complete dates or 1 Jan 1900 for unrecognised dates. This is a simple
example, and could be extended to recognise more options if your data is
less consistent than this.

Public Function TextToDate(strDate As String) As Date

Dim strYear As String
Dim strMonth As String

If IsDate(strDate) Then
TextToDate = strDate
ElseIf IsNumeric(Right(strDate, 4)) Then
'Last 4 digits are numeric, so assume it is the year
If Len(strDate) = 4 Then
TextToDate = DateSerial(CLng(strDate), 1, 1)
ElseIf Len(strDate) = 8 Then
strYear = CLng(Right(strDate, 4))
strMonth = Left(strDate, 3)
TextToDate = CDate("01-" & strMonth & "-" & strYear)
Else
'Unrecognised format - treat all these cases as 1 Jan 1900
TextToDate = DateSerial(1900, 1, 1)
End If
Else
'Unrecognised format - treat all these cases as 1 Jan 1900
TextToDate = DateSerial(1900, 1, 1)
End If

End Function

 

[John Nurick]

Hi,

As you've found, the Access date/time field stores a fairly precise
point in time, not just a day or month or year. When you enter
07-Apr-2004, what is actually stored is the number 38,084, which
represents 00:00 hr on 7 April. So there's no way in the date-time field
to store just the year 2004; the nearest you could get would be 00:00
1-Jan-2004.

I feel the simplest way round this would be to use one (date/time) field
to store a date just for sorting, and a second (text) field to store the
date as you want it printed (e.g. "before 1900", "1948", "June 1980", "7
April 2004").

Another approach - which might be better if you need to pull out records
for certain periods - would be to use two date-time fields to store two
dates for each item. For an observation on a known date, both fields
would store the same value (e.g. 07-Apr-2004). Where the date is only
known approximately, store the first and last relevant dates (e.g. for
an observation in 1825, store 1-Jan-1825 and 31-Dec-1825). Where the
date is unknown, store two Nulls (Null is conventionally used to
represent an unknown value).

This approach lets you choose how to sort your approximate dates: do you
want to sort "January 1925" before or after "5 January 1925"? It also
makes it simple to handle really vague dates (e.g. between 1950 and
1955). To display approximate dates, however, you'd need to use some
cunning SQL or VBA code to use the difference between the "first" and
"last" dates to control how the date is formatted.

 

[harvestman]

Many thanks to John Nurick and Andrew Smith for their
respective feedback & suggestions.

However, before I take either of their suggestions on
board, I would like to mention/ or ask the following:

1: Yes, at present my dates are stored as a Text file, but
I have duplicated the field of date data to another
successive field (adjoining column), so I can experiment
with date format and field properties. In regard to the
latter, I was hoping that by creating an expression in the
Validation Rule, this would enable me to have the dates:
(i) - displayed in multiple formats (which it does);
(ii) - stored (as sequential coded number figures) which
it apparently does not.

2: I was already aware - that like in Excel - the dates in
Access are stored as numbers based on a three figure dd-
mmm-yyyy date. However,

(a): I had not figured out what the starting date is/ was,
i.e., 31 Dec 1899;

(b): I was hoping that Access would have been clever
enough to permit some alternate method or coding that
would enable sequential recording of mmm-yyyy dates or
yyyy dates, but it appears not!

3: In view of "2(a):" above, assuming I change my present
text field into a Date/Time field... if I then convert a
pre-1900 mmm-yyyy dates (e.g., May-1891) or pre-1900 yyyy
dates (e.g., 1883) to first day of the month date or first
day of the year respectively... will these dates get
recorded (stored in Access) with a negative (minus) number
figure, or will they disappear into the ether? And if
they are stored, will they still show/ list
chronologically with post 31 Dec 1899 dates?

4: I had already tried recording mmm-yyyy dates by
creating a "00" (for "dd"), but this obviously did not
work. If I go back into my unmodified original text
field, I can obviously create the 01-mmm-yyyy dates
easily... I presume by just doing a "replace" of say: "00-
mmm-yyyy" with "01-mmm-yyyy", but then I will not be able
to distinguish between genuine first day of the month
records and these new "month" dates, apart from doing what
John suggests and creating a duplicate column which shows
last day of month.

5: Regarding the coding suggested by Andrew - using
the "query" mode - I'm sorry to say that coz I'm not a
programmer/ coder, I am a bit lost here: I don't know how/
where I should insert this coding in order to get a
specific set of values for a specific field to respond...
and whether I do this while the field is set as "Text" or
as "Date/Time".

Any more feedback to these points and/ or my original
query - about having multiple date formats in an Access
Table - would be much appreciated.

BTW: in case you are wondering, "harvestman" is the common
name for an oplionid (one of the many groups of arachnid
species, that include beasties such as spiders, mites,
ticks, scorpions, pseudoscorpions, whip scorpions etc.)
and the "harvestman" is one of my favourite mini beasts,
hence the nick!

Thanks again,
Cheers,
harvestman.

 

[Andrew Smith]

Some comments on your points:

1. Duplicating the date in a second date field is certainly one way of doing
it, but it would be better not to do this as you'll have extra data to
input, and there is strong chance of introducing errors this way - ie the
two fields could easily show different data.

2/ 3. Just because 31 Dec 1899 is arbitrarily given serial number of 1 does
not mean that this is the earliest data that Access can store. I'm not
entirely sure of the earliest possible date that Access can cope with, but
from a bit of experimentation it appears to be 1 Jan 100 AD. I don't know
how Access deals with Julian dates. Dates prior to 31 Dec 1899 are simply
stored as negative numbers.

So, dates as far back as at least 1 Jan 100AD will all work perfectly well
(with possible problems caused by the Julian calendar that I've not
explored).

4. I think I covered this point in my first reply.

5. I think this is the way to go. A while back whilst I was learning Access
I thought that I'd have a go at creating a genealogy database for my wife.
This brings up exactly the same problem with dates that you have in your
system as it is quite common that you don't know the exact date of a
particular event, but you still need to be able to order events
chronologically. I chose to do something similar to what I've suggested to
you and it worked fine. It does, though, rely on your text dates being in a
consistent format.

Implementing it is not difficult. Just follow these steps:

i) Create a new standard code module - click on "modules" in the database
window, then click on "New". This will bring up the code editor. Copy and
paste in the code that I posted yesterday, and then save the module with a
suitable name.

ii) Test the code by using the "immediate" window (this may be visible below
the code window, but if it isn't press crtl-g to show it). In the
intermediate window type each the following and then press the return key:

?TextToDate("1 Jan 2004")
?TextToDate("Jan 2004")
?TextToDate("2004")
?TextToDate("some rubbish")

If the code is working then each of these result in a date being printed in
the immediate window.

iii) Once you've got a function that works, create a new query in design
view. Add your table to it, then double click the date field to add this to
the query. Then in an empty field cell, to the right of the name of the date
field, type

ConvertedDate:TextToDate([PutTheNameOfYourDateFieldHere])

Set the sort order of this new field (ConvertedDate) to Ascending or
Descending, and then look at the query results. If your text date is
formatted as I assumed in the function then it should work. I expect,
however, that it will need a bit of tweaking to allow for how your text is
actually formatted.

 

[harvestman]

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

 

[John Nurick]

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.

No: I meant the value Null, not the word "null". Access distinguishes
between an unknown value (which it stores as Null) and a value that is
known to be nothing (which it can store as an empty string). When Access
imports a text file it normally stores a Null in the Access table each
time there's an empty field in the text file.

 

[Andrew Smith]

Harvestman,

My ISP's news service has become steadily worse and you latest message has
not appeared. However I knew it was there as I saw John's reply to it.
Anyway I've found it on Google, so here's the reply anyway:

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.

That's good - my code should work fine.

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?

Yes. I'm suggesting you use the function to create a date value purely for
sorting purposes. You'd still display the original text so you could
distinguish partial dates from full dates.

(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?

It just means anything that the code hasn't allowed for. As written it will
convert any text that Access recognises as a date, just the month and year
(provided there were 8 characters with the first three being the month, and
the last three the year), or just the year. Anything else would come into
the "unrecognised format category.

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

Shouldn't matter.

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

No - you can do what you want with it. If you choose a date that you know is
before the earliest known date then these will all sort at the start, or you
could choose a future date so they all sort at the end. You have to decide
how you want to sort records where you don't know the date. You may find
that some entries do have dates, but that the data entry is wrong so they
don't get converted. If these all sort in the same place then they will be
easy to spot and correct.

(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"?

It shouldn't make any difference. Access will recognise numerous text
strings as dates, and will convert them accordingly. For example, all of the
following (and more) are successfully converted to a valid date:

28/2/04
28/02/04
28/Feb/04
28 Feb 04
28 February 04
February 28 2004

The only ones you have to be cautious of are where the dates are all
numbers, and the day is 12 or less. Here the regional settings on your PC
will affect how the text gets converted. For example, if your PC is set on
US date formats of mm/dd/yy then "04/12/04" will be recognised as meaning 12
Apr 04. However if you are set up to use UK format dates of dd/mm/yy, then
it will be converted to 4 Dec 04.

The way that date fields are displayed is also affected by the regional
settings of the PC, but this should not matter to you too much as you only
need to use the converted dates for sorting purposes. However, you do need
to be aware of this when you are checking that it's working properly.

In your case you have used the month names, so there will be no problem
converting any entries where you know the full date.

Some comments on your points:

1. Duplicating the date in a second date field is certainly one way of doing
it, but it would be better not to do this as you'll have extra data to
input, and there is strong chance of introducing errors this way - ie the
two fields could easily show different data.

2/ 3. Just because 31 Dec 1899 is arbitrarily given serial number of 1 does
not mean that this is the earliest data that Access can store. I'm not
entirely sure of the earliest possible date that Access can cope with, but
from a bit of experimentation it appears to be 1 Jan 100 AD. I don't know
how Access deals with Julian dates. Dates prior to 31 Dec 1899 are simply
stored as negative numbers.

So, dates as far back as at least 1 Jan 100AD will all work perfectly well
(with possible problems caused by the Julian calendar that I've not
explored).

4. I think I covered this point in my first reply.

5. I think this is the way to go. A while back whilst I was learning Access
I thought that I'd have a go at creating a genealogy database for my wife.
This brings up exactly the same problem with dates that you have in your
system as it is quite common that you don't know the exact date of a
particular event, but you still need to be able to order events
chronologically. I chose to do something similar to what I've suggested to
you and it worked fine. It does, though, rely on your text dates being in a
consistent format.

Implementing it is not difficult. Just follow these steps:

i) Create a new standard code module - click on "modules" in the database
window, then click on "New". This will bring up the code editor. Copy and
paste in the code that I posted yesterday, and then save the module with a
suitable name.

ii) Test the code by using the "immediate" window (this may be visible below
the code window, but if it isn't press crtl-g to show it). In the
intermediate window type each the following and then press the return key:

?TextToDate("1 Jan 2004")
?TextToDate("Jan 2004")
?TextToDate("2004")
?TextToDate("some rubbish")

If the code is working then each of these result in a date being printed in
the immediate window.

iii) Once you've got a function that works, create a new query in design
view. Add your table to it, then double click the date field to add this to
the query. Then in an empty field cell, to the right of the name of the date
field, type

ConvertedDate:TextToDate([PutTheNameOfYourDateFieldHere])

Set the sort order of this new field (ConvertedDate) to Ascending or
Descending, and then look at the query results. If your text date is
formatted as I assumed in the function then it should work. I expect,
however, that it will need a bit of tweaking to allow for how your text is
actually formatted.

Many thanks to John Nurick and Andrew Smith for their
respective feedback & suggestions.

However, before I take either of their suggestions on
board, I would like to mention/ or ask the following:

1: Yes, at present my dates are stored as a Text file, but
I have duplicated the field of date data to another
successive field (adjoining column), so I can experiment
with date format and field properties. In regard to the
latter, I was hoping that by creating an expression in the
Validation Rule, this would enable me to have the dates:
(i) - displayed in multiple formats (which it does);
(ii) - stored (as sequential coded number figures) which
it apparently does not.

2: I was already aware - that like in Excel - the dates in
Access are stored as numbers based on a three figure dd-
mmm-yyyy date. However,

(a): I had not figured out what the starting date is/ was,
i.e., 31 Dec 1899;

(b): I was hoping that Access would have been clever
enough to permit some alternate method or coding that
would enable sequential recording of mmm-yyyy dates or
yyyy dates, but it appears not!

3: In view of "2(a):" above, assuming I change my present
text field into a Date/Time field... if I then convert a
pre-1900 mmm-yyyy dates (e.g., May-1891) or pre-1900 yyyy
dates (e.g., 1883) to first day of the month date or first
day of the year respectively... will these dates get
recorded (stored in Access) with a negative (minus) number
figure, or will they disappear into the ether? And if
they are stored, will they still show/ list
chronologically with post 31 Dec 1899 dates?

4: I had already tried recording mmm-yyyy dates by
creating a "00" (for "dd"), but this obviously did not
work. If I go back into my unmodified original text
field, I can obviously create the 01-mmm-yyyy dates
easily... I presume by just doing a "replace" of say: "00-
mmm-yyyy" with "01-mmm-yyyy", but then I will not be able
to distinguish between genuine first day of the month
records and these new "month" dates, apart from doing what
John suggests and creating a duplicate column which shows
last day of month.

5: Regarding the coding suggested by Andrew - using
the "query" mode - I'm sorry to say that coz I'm not a
programmer/ coder, I am a bit lost here: I don't know how/
where I should insert this coding in order to get a
specific set of values for a specific field to respond...
and whether I do this while the field is set as "Text" or
as "Date/Time".

Any more feedback to these points and/ or my original
query - about having multiple date formats in an Access
Table - would be much appreciated.

BTW: in case you are wondering, "harvestman" is the common
name for an oplionid (one of the many groups of arachnid
species, that include beasties such as spiders, mites,
ticks, scorpions, pseudoscorpions, whip scorpions etc.)
and the "harvestman" is one of my favourite mini beasts,
hence the nick!

Thanks again,
Cheers,
harvestman.

 

[Guest]

OK... Andrew.

Once again, many thanks for your prompt reply.
Incidentally, I have not received a second reply from
John Nurick, which you seem to intimate that I should
have.

I shall do some coding and experimentation in the next
few days and see how / what works. I have my regional
settings (under control Panel) set to dd-mmm-yyyy, i.e.,
in UK format. In regards to having unrecognisable
formats set to 01-Jan-1900, I'm wondering in light of
your response whether I should arbitarily set a date that
precedes my data set (but this would be stored as a
negative date number by Access)... say 01-Jan-1850
perhaps. Re-examining my db, I have found a record going
back to 1871 and in recent historical research, I located
a reference for one of my cave invertebrate beasties
being found in the early 1860's.

I'm still not clear whether I should attempt to replace
my blank spaces (where no values are entered) in my text
formatted date field with the word "null" as suggested by
John. If so, is there a coding method that will enable
this, bearing in mind that in a blank space there will be
no value to be identified or read by the coding?

BTW: I know it's off topic here, but in my occasional
spare time I'm also dabbling with attempting the
compilation of a db relating to family genealogy, so I
would be interested to possibly get a copy of the db you
compiled for your wife (if you were OK about that),
though that might mean me having to give you my name
(IRL) and private (home or university) email address and
that might not be an acceptable protocol via this forum.
I sometimes try to send copies of my latest version of my
research db to myself on email (from Uni to home), though
my home based XP version of Outlook deletes attachments
with ".mdb" file suffixes.

Cheers again,
harvestman.

-----Original Message-----
Harvestman,

My ISP's news service has become steadily worse and you latest message has
not appeared. However I knew it was there as I saw

John's reply to it.

 

[harvestman]

Edited re-send of previous posting.

OK... Andrew.

Once again, many thanks for your prompt reply.

A few final (?) remarks:

Athough you suggest doing the conversion of my original
text field data (into date/time) as a query, I shall
first try to copy all the date data values into another
duplicate field column (as "text" by default), then run
the query on that field... in case there are any
hiccups. Because of the problem with my dates (and not
being able to sort them), I have not added in the data I
need for another crucial couple of fields and have not
yet tried doing any queries... though I know that this
latter query function is one of the many aspects that
makes Access so valuable as a db tool.

I shall do some coding and experimentation as a query
tonight and in the next few days and see how / what
works. I have my regional settings (under control Panel)
set to dd-mmm-yyyy, i.e., in UK format. In regards to
having unrecognisable formats set to 01-Jan-1900, I'm
wondering in light of your response whether I should
arbitarily set a date that precedes my data set (but this
would be stored as a negative date number by Access)...
say 01-Jan-1850 perhaps. Re-examining my db, I have
found a record going back to 1871 and in recent
historical research, I located a reference for one of my
cave invertebrate beasties being found in the early
1860's.

I'm still not clear whether I should attempt to replace
my blank spaces (where no values are entered) in my text
formatted date field with the word "null" as suggested by
John. If so, is there a coding method that will enable
this, bearing in mind that in a blank space there will be
no value to be identified or read by the coding?

BTW: I sometimes try to send copies of my latest version
of my research db to myself on email (from Uni to home),
though my home based XP version of Outlook deletes
attachments with ".mdb" file suffixes.

Is it possible to request a copy of your genealogy db? I
realise that would mean having to give you my IRL name
and email address which might not be acceptable protocol
for this newsgroup forum.

Cheers again,
harvestman.

 

[harvestman]

Second edited re-send of two previous postings.

OK... Andrew.

Once again, many thanks for your prompt reply.

A few final (?) remarks:

Athough you suggest doing the conversion of my original
text field data (into date/time) as a query, I shall
first try to copy all the date data values into another
duplicate field column (as "text" by default), then run
the query on that field... in case there are any
hiccups. Because of the problem with my dates (and not
being able to sort them), I have not added in the data I
need for another crucial couple of fields and have not
yet tried doing any queries... though I know that this
latter query function is one of the many aspects that
makes Access so valuable as a db tool.

I shall do some coding and experimentation as a module in
a query and in the next few days and see how / what
works. I have my regional settings (under control Panel)
set to dd-mmm-yyyy, i.e., in UK format. In regards to
having unrecognisable formats set to 01-Jan-1900, I'm
wondering in light of your response whether I should
arbitarily set a date that precedes my data set (but this
would be stored as a negative date number by Access)...
say 01-Jan-1850 perhaps. Re-examining my db, I have
found a record going back to 1871 and in recent
historical research, I located a reference for one of my
cave invertebrate beasties being found in the early
1860's.

BTW: I sometimes try to send copies of my latest version
of my research db to myself on email (from Uni to home),
though my home based XP version of Outlook deletes
attachments with ".mdb" file suffixes.

Is it possible to request a copy of your genealogy db? I
realise that would mean having to give you my IRL name
and email address which might not be acceptable protocol
for this newsgroup forum.

Cheers again,
harvestman.

-----Original Message-----

-----Original Message-----
From: "Andrew Smith" <[email protected]>
Sent: 4/9/2004 3:01:41 PM
Subject: Re: Multiple date formats in a Table

 

[Andrew Smith]

Harvestman,

A simple select query will not modify any data in your table, so there is no
risk involved in running this type of query.

The problem with Outlook can be fixed - I got the solution from an Outlook
newsgroup, so a search on Google should bring up the answer to this one.

I'd rather not send you the genealogy database. I never really finished it
so it's probably not good enough to show to anyone else (and if it was I
might not feel like giving it away for free )

Andrew