Can date entries be restricted to mm/yy format? 1

[migv1]

I have forms for users to enter dates of projected events, payments, etc. For our purposes, we only need the month and year for these entries.
I have formatted the date fields as mm/yy and have tried several different input masks, finally settling on
00/"1"/00;0;_
(since every month would have a first day). The problem is that users still see __/1/__ when entering dates, and this can be confusing, even if they have been explicitly instructed to enter dates in mm/yy format. An input mask of 00/00;0;_ shows the desired __/__ during data entry, but the data saved consists of the 4 digits as the month and day of the current year.
Is there any way to have users enter mm/yy data without exposing them to a confusing input mask?

 

[BobJacksonNCI]

If you're sure the day will never be needed, all you need to do is define the field in the table as Date/Time, set the Format field to mmyy and the Input Mask as 00/00;0;_

After the table definition has been set close the table and make sure Access doesn't report any problems with existing data.

Then delete the control on your form and add it back. It will use the properties from the table and you should be set.



HTH,
Bob

 

[migv1]

Thanks for the tip, Bob. I tried that in both the forms and entering values directly into the table. Unfortunately, the same problem arises that I mentioned earlier: the 4 digits entered make up the month and day in the current year, e.g., an entry of 0104 (for Jan '04)comes up as 1/4/04, and an entry of 0105 (for Jan. '05) is saved as 1/5/04.

 

[BobJacksonNCI]

Sorry you're having trouble.

I created a database in Access 2000, followed my instructions and ... didn't like the result.

So, I set the Format to mm/yy and the Input Mask to 00/00;;_
and that seems to work.

But, maybe I'm overlooking something: You ARE aware that dates are always stored in tables the same way, aren't you? And how it is displayed is governed by the Format, the way data entry looks is governed by Input Mask.

A review of

http://support.microsoft.com/default.aspx?scid=kb;en-us;210276&Product=acc

might help, too.

HTH,
Bob

 

[migv1]

Thanks again, but the same thing happens: 0104 is stored as 1/4/04, while 0105 is stored as 1/5/04. I realize that dates are always stored as #m/#d/yy in tables - I was hoping I could just have users enter 2-digit month and year (and have these saved as mm/1/yy) without them having to see the confusing input mask.
Maybe some solutions to consider:
1. Splitting the field (as text), separating month and year
2. VBA hijinks

 

[BobJacksonNCI]

Sure enough, you ARE correct...

I've read before, but now confirm that if a field is to contain date information, it MUST be a complete, valid date.

Best I can come up with is this:

Table: Field defined as 4 text characters, Format property is @@\/@@

Form: Input Mask is 00/00;;_, Format if the field is newly added will come from table - if modifying existing form set same as table.

For validation of first two characters between 01 - 12, add a Before Update event containing:

Dim mm As String
Dim yy As String

mm = Left(dtProj, 2)
yy = Right(dtProj, 2)

If Nz(mm, 0) < "01" Or mm > "12" Then
MsgBox "First two digits must be between 01 and 12", vbOKOnly, "INVALID ENTRY"
Me.Undo
DoCmd.CancelEvent
End If

Note that entry of less than 4 digits gets a funky message about entry not matching Input Mask.

Caveat: This is going to make queries that select by month and year a bit more complicated. Following is an example if a date range on a form is used to select data. More jockeying to allow Between to work:

WHERE Mid(MMYY,3,2) & Left(MMYY,2) Between Mid(Forms!MyForm!Fromdate,3,2) & Left(Forms!MyForm!Fromdate,2) AND Mid(Forms!myForm!ToDate,3,2) & Left(Forms!MyForm!Todate,2)



HTH,
Bob