Wrong dates entered

[LMB]

Hi Everyone,

Using Access 2000. Currently use the properties dialog to set my form
preferences because I am not yet ready for code but have pasted it in
before. I have a form with a date field. The input mask is set to
99/99/9999;0;_

I have found several date errors. Here are some of them..1/10/205,
1/10/305, 1/11/605, 7/17/1958. The dates entered so far should only be from
3/1/05 thru 11/30/05. How can I set things up so the user can't enter "bad"
dates?

Any suggestions regarding dates in general will be appreciated.

Thanks,
Linda

 

[tina]

well, you have two issues: first, your input mask is allowing "spaces" to
be skipped, i think. second, even a valid date can be outside the time frame
you mention, and an input mask can't control that - but a validation rule
can.

suggest you change the input mask to

00/00/0000;0;_

and add a validation rule either on this field at the table level, or on
this control in the form. example: if you don't want any dates prior to
3/1/05, then use

2/28/05

if the date may be left blank, then use

2/28/05 Or Is Null

if you don't want to allow any dates in the future of the current date, then
use

Between 3/1/05 And Date() Or Is Null

obviously, the specific validation rule you use depends on exactly how you
want to limit the date range that will be accepted during data entry.

hth

 

[David C. Holley]

Confirm that the field type for the underlying table is set to
Date/Time. Also, double check that the field mask value of 9 requires a
value to be entered. If I'm not mistaken 0 is the value to use, but I'm
not 100% certain.

 

[David C. Holley]

What she said. But, if you're dealing with a range of values, I would
use code in the controls AfterUpdate event to do the validation as
opposed to table-level validation. Basically

Sub myField_AfterUpdate

If Me.myField > #10/1/2005# and Me.myField < #10/2/2005# then
MsgBox
Me.myField.Setfocus
end if

End Sub

 

[LMB]

Thanks Dave and Tina. I'm going in to work so I'll try these things and
report back if I need to.

Linda

 

[Rick Brandt]

What she said. But, if you're dealing with a range of values, I would
use code in the controls AfterUpdate event to do the validation as
opposed to table-level validation. Basically

Sub myField_AfterUpdate

If Me.myField > #10/1/2005# and Me.myField < #10/2/2005# then
MsgBox
Me.myField.Setfocus
end if

End Sub

Actually validation like that is better done in BeforeUpdate. With BeforeUpdate
you simply set the Cancel argument of the event to True with no need to set
focus back to the control because it will never lose focus to begin with.

 

[tina]

you're welcome, and good luck

Thanks Dave and Tina. I'm going in to work so I'll try these things and
report back if I need to.

Linda