Date/Time custom format

[Pat Voght]

I need to retrieve records based on a selected General
Ledger Accounting period of month and year. Can I store
the date in the format of mm/yy in a table field? If so,
how do you create that format? If I cannot store data in
that format, can I retrieve records based on the month
and year of a date? I have attempted to do so using the
datePart function in a query on a date/time field but
with no success. I appreciate any help you can give me.
Pat

 

[Douglas J. Steele]

The Date data type in Access is intended to store complete dates: it's an 8
byte floating point number, where the integer part represents the date as
the number of days relative to 30 Dec, 1899, and the decimal part represents
the time as a fraction of a day. In other words, it's not possible to store
only mm/yy in a date field.

If all you want is month and year, consider storing the month and the year
as separate integer fields (unless you want to store a text version of the
month name)

If you've got complete dates in the field, though, you can easily retrieve
by month and year using something like the following:

WHERE Format([DateEntry], "yyyymm") = 200402

 

[Bas Cost Budde]

Can I store the date in the format of mm/yy in a table field?

Maybe; but there is no need. You can use a normal date (allowing date
calculations by using standard functions) and if you don't need the day,
set it to 1.

can I retrieve records based on the month
and year of a date?

Most certainly. Create an expression in the query like

format(yourdatefield, "mm/yy")

and put appropriate criteria below.

 

[Pat Voght]

Thanks,
I now have a better understanding of how Access handles dates. I tried format() and it solves my problem
Thanks again
Pat

 

[Bas Cost Budde]

The Date data type in Access is intended to store complete dates: it's an 8
byte floating point number, where the integer part represents the date as
the number of days relative to 30 Dec, 1899,

Luckily there is no Macintosh version of Access, as there is Excel--Mac
has a different date starting point.

If all you want is month and year, consider storing the month and the year
as separate integer fields (unless you want to store a text version of the
month name)

I don't really believe that is a solution, unless you never need math on
the dates. Even deriving the next value will be a pain (exaggerating
slightly here, bounds checking for 12 is all really) and finding the
interval requires help. Let alone that you have to have extra validation.

 

[Guest]

If you don't care about the day, just save it as the first
of the month (e.g., 3/1/2004). Then use the Month and Year
functions to retrieve the parts you want. e.g.

MyDate = Month(Date) & "/" & Year(Date)

Results in MyDate = 3/2004

 

[John Vinson]

If you don't care about the day, just save it as the first
of the month (e.g., 3/1/2004). Then use the Month and Year
functions to retrieve the parts you want. e.g.

MyDate = Month(Date) & "/" & Year(Date)

Results in MyDate = 3/2004

or even simply set the Format property of the field to

m/yyyy