partial date format

[chriske911]

how can I let users fill in a partial date format and still let access
recognize it as a date?
the format of the field would be yyyy/mm --> year and month, no day is
required
right now I am having problems with the ordering of the values since it
is entered as text for the moment

thnx

 

[Douglas J. Steele]

The Date data type in Access must be a complete date: no exceptions. That's
because under the covers, it's an 8 byte floating point number, where the
integer portion represents the date as the number of days relative to 30
Dec, 1899, and the decimal portion represents the time as a fraction of a
day.

One option is to store their input as text. Another, slightly better, one is
to store it as two numeric fields: one for year, and one for month. (You
could use combo boxes to let them choose). A third option would be to decide
on a default day (i.e.: you'd always assume the first of the month). You'd
have the text box into which they type unbound, then put code in its
AfterUpdate event to convert their input to a proper date that you'd store
in the actual field.

 

[Arvin Meyer [MVP]]

You cannot recognize part of a date as a date. What you can do is set the
format property to only display the year and month: yyyy/mm

You will stull need to enter a correct date.

 

[chriske911]

After serious thinking Arvin Meyer [MVP] wrote :

You cannot recognize part of a date as a date. What you can do is set the
format property to only display the year and month: yyyy/mm

You will stull need to enter a correct date.

thnx to both
exactly what I thought
will have to catch this in code for sake of intuitive usage

grtz

 

[Jamie Collins]

You cannot recognize part of a date as a date. What you can do is set the
format property to only display the year and month: yyyy/mm

Another view is that yyyy/mm is duration e.g. 2006/11 can be
represented in Jet SQL (noting one second granularity) as using a pair
of columns (i.e. same row):

start_date = #2006-11-01 00:00:00#
end_date = #2006-11-30 23:59:59#

(in the SQL literature this is called the 'closed-closed'
representation).

Modelling temporal data in this way aids querying, especially of the
kind, "Which dates coincide with 2006/11?" because you can use

BETWEEN start_date AND end_date

in the search (or join) condition.

Jamie.

--