Re: Multiple date formats in a Table
From: Andrew Smith (andydsmith_at_ntlworld.invalid)
Date: 04/09/04
- Next message: Jonathan Mulder: "Create in index in VB Code"
- Previous message: John Nurick: "Re: Multiple date formats in a Table"
- In reply to: Andrew Smith: "Re: Multiple date formats in a Table"
- Next in thread: anonymous_at_discussions.microsoft.com: "Re: Multiple date formats in a Table"
- Reply: anonymous_at_discussions.microsoft.com: "Re: Multiple date formats in a Table"
- Reply: harvestman: "Re: Multiple date formats in a Table"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 9 Apr 2004 23:01:41 +0100
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.
"Andrew Smith" <andydsmith@ntlworld.invalid> wrote in message
news:Vphdc.99$Xc6.45@newsfe1-win...
> 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: Jonathan Mulder: "Create in index in VB Code"
- Previous message: John Nurick: "Re: Multiple date formats in a Table"
- In reply to: Andrew Smith: "Re: Multiple date formats in a Table"
- Next in thread: anonymous_at_discussions.microsoft.com: "Re: Multiple date formats in a Table"
- Reply: anonymous_at_discussions.microsoft.com: "Re: Multiple date formats in a Table"
- Reply: harvestman: "Re: Multiple date formats in a Table"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
