Limiting date data

[Russ]

In my db I want to keep track of a certain date and I want it limited
to just the day and the month and ignoring the year. When I enter the
date, I only enter the month and day, and it displays ok, but when I
examine the field Access 2000 puts the current year in there as well.
This is not a problem until I run the report which I want sorted on
month and day. With the year in there Access sorts according to year
first, then month, then day and I end up with records out of order.

I've got the field formatted to mmmm dd in the table and in the form,
however that will not prevent Access from inserting the year.

Anyone know a way around this?

Russ

 

[Brendan Reynolds]

A Date/Time field always stores a complete date and time. If you don't
specify the time, it defaults to midnight.

If you really want to store only a day and a month, you'll need to use
something other than a Date/Time field, perhaps two integers, one for the
day and one for the month. Of course, then you'll have to write your own
validation code to ensure that users don't enter dates such as 31 September
or 30 February.

Alternatively, you can use functions such as Day() and Month() to extract
from a date/time value only the part or parts of the date that interest you,
or Format$() to create a string from the date. For example, instead of
sorting your report by your Date/Time field, sort it by
Month([NameOfFieldHere]), Day([NameOfFieldHere]), or by
Format$([NameOfFieldHere], "mmdd").

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Russ]

Thanks Brendan. What has happened is, the field has used the current
year each time a date field is filled. Therefore, it sorts by the
wrong date. I'll try your suggestion with the function Day() and
Month() and see how it goes.

Russ