input mask for date/time field

[Gerilee]

I have a date/time field in my table called haziddate. I define the format in the table as dd/mm/yyyy which is how I want it to display to the user. I was having trouble when I defined an input mask so I removed it but I would like to use one to make it more user-friendly. I want the user to enter the date in the dd/mm/yy format but don't want them to have to enter 02 for day 2 -- just to enter 2. I have experimented with all kinds of input masks but can't seem to get this to work. Also, when I enter 20/02/06 it displays and stores it as 02/06/2020. I have spent most of the day researching this and am now totally confused. Can anyone help please?

 

[formerTexan]

Hi Gerilee,

The Windows regional settings define how dates are stored, not only in Windows, but Access as well. In much of the world the order is year/month/day, in the US month/day/year and I think there are some other flavors as well. So I think you have three options:

1. reset Windows regional settings on everyone's computer (not really a good option);
2. go with the flow, create your input controls accordingly and train the users;
3. my suggestion - store dates in three seperate fields (year, month, day). Then you can tailor input and output to any fashion you want. And use a convenient dropdown for the months as well as validate day/year entries.

Cheers,
Bill

 

[Gerilee]

Bill,

Thanks for replying. I'm sorry but I put some wrong info in my initial post. I do have the format set up as mm/dd/yyyy NOT dd/mm/yyyy as I originally stated. So it does match the Windows regional settings. I was originally using an input mask of 99/99/00;_ which allowed the user to see __/__/__ for input. For February 9, 2006, I wanted the user to be able to enter 2/9/06. I wanted them to enter 2 in the month field and then have the cursor move to the day part of the date. Instead, you must enter 02 or you get an Access error. In an effort to correct that, I removed the input mask and used only the format of mm/dd/yyyy but the problem is still the same. I found a sample database where this works so I know that it's possible. That database uses Short Date and an input mask of 99/99/00;0 in both the table design and the form properties. I changed my database to match and it worked as long as I typed over an existing record. I have an ADD command button on my form which clears the form for new entries. When I click that, I get the mask __/__/__ even though I changed it and then it requires me to enter 02 instead of 2. So it obviously has something to do with my Add command button. I have code for 2 events with it. For the On Click event, I have --

Private Sub cmdadd_Click()
On Error GoTo Err_cmdadd_Click
Me.cmdbuttonsave.Enabled = True
Me.cmdcancel.Enabled = True
Me.cmdnextrecord.Enabled = False
Me.cmdprevious.Enabled = False
Me.cmddelete.Enabled = False
DoCmd.GoToRecord , , A_NEWREC
DoCmd.GoToControl "cmdcc"

Exit_cmdadd_Click:
Exit Sub

Err_cmdadd_Click:
MsgBox Err.Description
Resume Exit_cmdadd_Click

End Sub

and for the On Mouse Move event, I have -

Private Sub cmdadd_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me![OptMsg].Value = "Click to add a record"
End Sub

I would also like the cursor to go to the first position in the field when the user goes to that field. Hope this clarifies things and sorry for the confusion. I spent all day yesterday working on this so if anyone can help, I'd appreciate it.

 

[formerTexan]

Gerilee,

The input mask uses zero's as placeholder. Try using 1 zero for the day instead of two.

And I think the user will be better served by putting "Add New" on a command button rather raising messages on the mouse movement.

Have a look at selLength and selstart. Note that you will have to set focus to the control before using these methods.

Cheers,
Bill

 

[Gerilee]

Bill,

Using 1 zero only allowed me to enter a one digit day so I couldn't enter anything greater than 9. I do have ADD on the command button, the mouse move message is just a little extra help. I will take a look at selLength and selStart.

Thanks.

 

[formerTexan]

Gerilee,

Here is an ad hoc idea that will work in the absence of a better solution.

Leave off the mask on the text box and place the following code on the textbox's After UpDate event. Rename the referenced control to whatever your textbox is.

Dim str As String
Dim strdte As String
str = DatePart("d", Me.txtDate)

If str > 0 And str < 10 Then
str = 0 & str
strdte = DatePart("yyyy", Me.txtDate) & "/" & DatePart("m", Me.txtDate) & "/" & str
Me.txtDate = strdte
End If

Cheers,
Bill

 

[Gerilee]

Bill,

Thanks for your persistence and patience. This works great!

Gerilee