How can I get a field to display month and day only? 1

[MacroScope]

I have an Access 2007, SQL Server 2012 system and I have a field that, like a birthday or anniversary, only requires day and month but not year. The data type for the table I chose is datetime, and even if I format the field Format([HOExp],"m,d') After Update, the year still returns automatically because of the data type. I thought about changing the data type to text, but that doesn't make sure someone doesn't put in an invalid entry, like 7/32, as an example.

Any suggestions on how to get the field to display only month and day will be appreciated.

 

[SkipVought]

Why? When I was born, there was a year! 1942!

 

[Andrzejek]

SO keep in your table entire date (with teh year) and just display what you want to see.

For example, today:

[tt]MsgBox Now[/tt]

gives you: [tt]8/20/2014 1:48:29[/tt]
but

[tt]MsgBox Format(Now, "M/D")[/tt]

Gives you: [tt]8/20[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MacroScope]

Because this field is not birthdate, but birthday. Actually, it's the expiration date of a homeowner's insurance policy. It will expire the same day every year. If I ask you what's your birthday, it's just a month and day, and that's what I want to record.

 

[MacroScope]

Andrzejek, I know how to do that for message box, text field, etc. But in a datetime formatted text box, the year wants to return automatically even if I format it as you suggested on the AfterUpdate event. That's what I'm trying to work around. I want to store the data so that every time the record displays it says 8/20 instead of 8/20/2014.

 

[Andrzejek]

In my (simple) mind, homeowner's insurance policy has a StartDate. Also, it has the date it was last paid in full. I would also assume people pay for one year ahead, and not for 10 years into the future.

Wouldn’t your expiration date of a homeowner's insurance policy be a calculated field, i.e. a Date?


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MacroScope]

No, because this is not a policy that we have sold. We sell primarily vehicle insurance, and we want to cross sell them homeowner policies as well. So we ask, when does your policy expire? They give a date, Nov. 15, and we follow up within six weeks of that date trying to switch them. So they decide to stay with their existing company for another year, and the expiration date will still remain the same, even if the year has changed. We'll follow up within 6 weeks of Nov. 15 in 2015, 2016, etc. trying to get them to work with us.

In policies we have sold for vehicle insurance, for example, the ExpDate field is of course a full field with year, but this particular field is only to record month and day for later follow-up.

Think of it like a birthday, the same day every year. I just want to display the birthday, not the birth date in the field. I don't care if a year is recorded in the table. It's the display I want to limit.

 

[MajP]

Private Sub Form_Load()
  Me.datefld.Format = "MM/DD"
End Sub

 

[dhookom]

Store your values in two fields: ExpMonth and ExpDay

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

Simple, but works like a charm!

I knew what it needed, but didn't know the right place to put it. Thanks so much for your help.

 

[Andrzejek]

How is it different from my very first suggestion to Format it this way (20 Aug 14 14:49)?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MajP]

How is it different from my very first suggestion to Format it this way (20 Aug 14 14:49)?

Not different but the OP did not know how to apply the format

Format([HOExp],"m,d') After Update

Cannot due it that way by using the format function. You have to set the format property and through code. This has to be done in code, because you cannot choose this as an option in the field property.

 

[MacroScope]

Andy, the difference is that each time I return to the field I see only day and date. The year is in the table but is ignored in the display. I'll create a report that will display all of the upcoming expiration dates, so that isn't an issue. I just wanted to only see something like 12/25 on the form.

 

[MajP]

Sorry my bust. This can be done simply without any code. I thought you were limited to the format choices in the pull down, but not the case. No code is needed to do this, just type in "MM/DD" in the format property field (no quotes). I think that was what Andy was simply trying to say.

When you create a custom format, you enter various characters in the Format property of a table field. The characters consist of placeholders (such as 0 and #), separators (such as periods and commas), literal characters, and colors. Custom formats for the Date/Time fields can contain two sections — one for the date and another for time — and you separate the sections with a semicolon.

When you apply a custom format to the Date/Time field, you can combine different formats by having two sections, one for the date and another for the time. In such an instance, you would separate the sections with a semicolon. For example, you can combine the General Date and Long Time formats as follows: m/dd/yyyy;h:mm:ss. To apply a custom date or time format, complete the following step

 

[MacroScope]

Thanks. I tried the format property as well, but with quotation marks and got a field populated as "m/d". I too was assuming that the list was limited to pull-down choices.

Sorry if I misunderstood, Andy. It's a cool solution, and I went back and removed the code. Simpler is always better, and if it's not needed I thought it best to eliminate it.

 

[Andrzejek]

No harm done. We all learned something, and that’s what counts.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.