Month and Date only field

[bdhtexas]

Is there a way to create a field (Table or Query) that contains the Month and Date only and not the year?

 

[Mile-O]

Keep a proper date field in the table but, in a query, you can use the Format function to create a calculated field.

i.e.

NewField: Format([DateField], "ddmm")

 

[ScottGem]

Is there a way to create a field (Table or Query) that contains the Month and Date only and not the year?

No. Access stores dates as a serial number that counts the number of days since 12/30/1899. Therefore, its impossible for a date/time datatype to not store a full date. How the date is displayed is controlled by you. You can display dates in a variety of ways. Look up formatting Dates in Access help for examples.

I suspect the reason you ask this question is to produce an anniversary date. Where you want the current year but the same month and day. That's also easy to do. An example of this is a birthday. You store the date of birth but to show the birthday for the current year you use:

=DateSerial(Year(Date()), Month([DOB]), Day([DOB]))

 

[Noelie2000]

Sorting Problem

I have used the following, as discussed in this thread:

Birthday: DateSerial(Year(Date()),Month([DOB]),Day([DOB]))

Unfortunately when I try to sort it returns a too complex expression.

Any ideas?

 

[Liil]

Keep a proper date field in the table but, in a query, you can use the Format function to create a calculated field.

i.e.
NewField: Format([DateField], "ddmm")

Thank you for this bit of code. I have a database at work where we show birthdays of employees, their date of birth is stored in one field. In a new query I have added the original table info plus a field called "Month" using:

Month: Format([Date Of Birth],"mm")

This then shows the month of each person's birthday which works perfectly for the reports i wanted to print off.

 

[Brianwarnock]

Firstly let me say thanks to LiiL for searching, too many don't bother.

Secondly I can sort on a field created with Dateserial no bother, whether things have moved on since Noelie2000 's post I don't know, I wonder why he got no response.

Brian