Date Format in Tables

[Guest]

Ok, here's what is probably another very easy question for you guys... it's
been a while since I've really gotten my hands dirty in Access though, and I
can't seem to find a good answer searching websites.

I've got a table that holds information for events, among these is a field
for date of the event. I currently have this field listed as a text field
(for simplicities sake). I'd like to list this as Date Field though, so that
I can then sort by the date, to keep the table in order from nearest event to
farthest away event (timeline-wise).

The issue I have is that some events are one day, and some are multiple. So
I need to be able to enter into this field both Nov. 5, 2006, and Dec.6-8,
2006. Is there a way to place a Mask/Format into a date Data Type that allows
for both types of inputs, and that will still let me sort and sort by the
actual date? As a text field obviously if I do a sort by it simply sorts by
the letters in the string, not the date they represent.

Thanks in advance for any help offered on this.

 

[Guest]

Hi

I look after the largest events database in Britain - so am used to event
dates. I would suggest that you have either a start date and number of days
field or if the event always run on consecutive days you could have start
date and end date fields.

I would suggest the start / end date option as this will allow easier input
(and less likly to mess up by users ??

Oh, you may want to book an event so check out
www.ukoutdoorpursuits.co.uk
No not touting for business (they don't pay commision)

 

[Guest]

How do you handle split events, eg. December 1-2 & December 8-9?

I'm thinking that some events can have one or more start dates and one or
more end dates, and that a "number of days" calculationwouldn't capture
that...

 

[John Vinson]

The issue I have is that some events are one day, and some are multiple. So
I need to be able to enter into this field both Nov. 5, 2006, and Dec.6-8,
2006. Is there a way to place a Mask/Format into a date Data Type that allows
for both types of inputs, and that will still let me sort and sort by the
actual date?

No. A Date/Time value isn't stored as a string; it's stored as a
number, a count of days and fractions of a day since midnight,
December 30, 1899. "Dec 6-8" does not correspond to an exact point in
time; a Date/Time field must do so.

You might want to use two fields - DateStart and DateFinish.

As a text field obviously if I do a sort by it simply sorts by
the letters in the string, not the date they represent.

You can - with some limitations - use the CDate() function to convert
your text string to a date for calculations. The text string "Dec 6-8"
will probably either not convert to a date, or perhaps to December 6,
2008... let's see:

Yep...

?cdate("Dec 6-8")
12/6/2008


John W. Vinson[MVP]

 

[Guest]

I ended up expanding the field from just a date field, to seperate start date
and end date fields. Then for output on the website I found a custom function
on another site that helped me to format it as desired.

http://www.4guysfromrolla.com/webtech/022701-1.shtml#postadlink

Now the only question I have left is... I have an input mask that looks
essentially like this:LLL\-00\-0000;0;_ So the date gets entered as
OCT-04-2006. But the only formatting I could find that come close to this is
"Medium Date" which shows the date as 04-OCT-2006. Is there a way to alter
the Medium Date format, or set up my own format style, so that the date gets
displayed the same way it gets entered?

Thanks again for all the help and suggestions.

 

[Guest]

How do you handle split events, eg. December 1-2 & December 8-9?

Simple - we cheat ha ha ha. The event is NOT set to run between two dates
(no events are - even if they do).

Each event has a code - in our case we use a concencated txt section of the
event name and a DatePart of the 1st start date - even if there is only one.

Basically this means that events have an "EventCode" ID field (you can use
an autonumber but I prefer to have a concencated field). Link this with a
table that stores dates (start and end) and you can have one event running
and stopping as many times as you want.

Like this
[DateID] = autonumber (Don't use a time/date stamp)
[EventCode] = linking field from event table
[Start] = date field
[End] = date field

The only time this would cause a problem (if the access developer didn't
watch out) would be if you had the same "type" of event running on the same
date as then you may end up with a number ofevents having the same code and
start date - BUT you can easily use a BeforeUpdate to add A, B or C, etc to
the code to get round this.

As you can see from these 2 sites (below) - we DO have events with the same
"name" running on the same dates but I have used a BeforeUpdate to alter the
event code so that the event details are not simply added together in
reports, querys etc.

http://www.ukoutdoorpursuits.co.uk ([EventCode] = 3pks607)
http://www.3peakscorporatechallenge.com ([EventCode] = 3pks607A)

both of the events on 21st June have over 1,000 people on them and the
access DB runs all the transport systems, hotels, meals, etc I really do need
to differenciate between them so I can make sure theydon't "bump" into each
other.

 

[Douglas J. Steele]

Try:

mmm\-dd\-yyyy

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Guest]

Hi

You can either use an input mask !>AAA-00-0000;0;_
or simply have the "format" display the date how you want mmm-dd-yyyy

--
Wayne
Manchester, England.
Enjoy whatever it is you do

 

[Guest]

That worked, I wasn't sure if I could set the format in like that
(mmm-dd-yyyy) or not. Thank you both.

 

[Guest]

Hi

The date that you "store" is the same whatever the format. Setting the
format simply "displays" the data in a way that you set.

Good luck with your project.

--
Wayne
Manchester, England.
Enjoy whatever it is you do