I wanted to a field to store and display in the format of mmmm yyyy. In both the tables and forms, it displays as mmmm yyyy but when I click on the field it reverts to dd/mm/yyyy format. I then ran this:


MsgBox Me.Operative_Date.Value 'prints value as dd/mm/yyyy
MsgBox Me.Operative_Date.Text 'prints value as mmmm yyyy


I haven't created any reports yet but I suspect want the value to mmmm yyyy as well. I tried the input mask >L<?????????\ 0000 but that wasn't that much better. Any suggestions?

Thanks,

scratch

A date field can only contain a valid date value. You can format it to display however you want. The difference between .text and .value is that .text (only available when the field has focus by the way) shows the value in the control and .value shows the saved value of the field.

You could use a text field to only store the month and year but that will be a whole passel of trouble when it comes to doing anything but looking at the field. You would need to edit the input data yourself - Access automatically ensures that date fields contain valid values. You would also have problems comparing and sorting the text fields. It is far better to stickwith a normal date/time field and use 1 as the day and format it however you want for display.

It is far better to stickwith a normal date/time field and use 1 as the day and format it however you want for display.

So you're saying stick with what I have?

Yes, stay with a real date field. There is no input mask that will be useful. If you want to have better control over this field, you might consider working with an unbound field for data entry. the user would enter month and year and you would translate that to month/day/year and populate the bound column. The problem is that although Access tries to be smart about dates, its algorithm isn't going to help you since it assumes the year is missing when you only enter two of the three parts of a date. You want to enter month and year and have day assumed to be 1.

Don't forget that when working with unbound controls like this, you'll need code in the form's current event to populate the unbound control and you'll need code in the unbound control's BeforeUpdate event to populate the hidden, bound control.