Recording Incomplete Dates

[MrTBC]

Access 2003
I have a simple one table database where one of the fields is a Date. The user has told me that they sometimes have a full date to enter (e.g. 1st May 2005) but sometimes they only have a month (e.g. May 2005). I currently have them entering all of these dates as the 1st of the month, but this is obviously not ideal.
Can anyone suggest the best way of allowing this?

Thanks very much.

 

[THWatson]

MrTBC
Well, it really depends upon how you are wanting to use the date that is entered later on - in reports, etc.

If you don't want to have all absent dates show as the first of the month, another alternative would be to plug in the date upon which the data is being entered. A shortcut for that is <ctrl> + ;

Tom

 

[sdk]

Hi

I tend to think that your solution is the best. Alternatives:

- Have a flag (checkbox) that says the date is indicative only
- change the field type to a string (but this will introduce mayhem)

It does come down to the use of the date. If the attribute is really day-sensitive, then presumably you would already have the accurate data. If the precision is ok to the nearset month, then I would live with how things are.


Cheers

S

 

[MajP]

I would do what you are doing. It is just not worth it to store a date or time as anything but a date. Eventually someone is going to want to treat it as a date. But you could do the heavy lifting for them. You provide an interface that allows the user to supply a complete date or a partial date. If it is a partial date, you fill in the day behind the scenes. Remeber all dates are stored the same regardless of how they are formatted. So if a user enters into a field formated as short time 09:00, or if a user enters into another field 1/1/2006/ 9:00:00 AM. They are still being stored the same way.

All other definitions aside, to VBA, a date is an 8-byte floating-point value that can contain information indicating a specific point in time. In particular, the integer portion of the value contains a number of days since December 30, 1899. The fractional portion of the date value represents the portion of the day stored in the value. For example, if the current date is 5/22/97 at 3:00 p.m., VBA stores the value internally as 35572.625. That is, the current date is 35572 days after 12/30/1899, and 3:00 p.m. is 625/1000 of a full day. In general, you don't have to care about the storage mechanism; VBA handles the conversions gracefully to and from the internal floating-point format and the external date display

So just because you may see a field with
May 2006
There is actually a default Day, and time associated with it, You just do not see it.

 

[MrTBC]

Thanks guys, I think I'll keep the single Date field and add the checkbox as suggested by sdk.
:->