Re: Multiple date formats in a Table
From: Andrew Smith (andydsmith_at_ntlworld.invalid)
Date: 04/08/04
- Next message: Bailey Assocites: "Review my design"
- Previous message: Roger: "Re: OLE Object Issue"
- In reply to: harvestman: "Multiple date formats in a Table"
- Next in thread: harvestman: "Re: Multiple date formats in a Table"
- Reply: harvestman: "Re: Multiple date formats in a Table"
- Reply: Andrew Smith: "Re: Multiple date formats in a Table"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 8 Apr 2004 20:08:38 +0100
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" <anonymous@discussions.microsoft.com> wrote in message
news:154f801c41d2d$0fcb1dc0$a001280a@phx.gbl...
> 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.
>
- Next message: Bailey Assocites: "Review my design"
- Previous message: Roger: "Re: OLE Object Issue"
- In reply to: harvestman: "Multiple date formats in a Table"
- Next in thread: harvestman: "Re: Multiple date formats in a Table"
- Reply: harvestman: "Re: Multiple date formats in a Table"
- Reply: Andrew Smith: "Re: Multiple date formats in a Table"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
