need validation rule for monthly period (mm/yy) on textbox control

[alanpike]

MS Access 2000... I have a textbox control on a form that requires a validation rule that will check for valid monthly periods to be entered. Example... allowable values for March 2006 would be 03/06 or 3/06 or 03/2006 or 3/2006. I have thought of using the left, right, & instr functions but am not having much luck. The control must remain a textbox. The form name is "AlsForm" and the textbox controls name is "AlsText1". Any ideas out there?

[dima69]

MS Access 2000... I have a textbox control on a form that requires a validation rule that will check for valid monthly periods to be entered. Example... allowable values for March 2006 would be 03/06 or 3/06 or 03/2006 or 3/2006. I have thought of using the left, right, & instr functions but am not having much luck. The control must remain a textbox. The form name is "AlsForm" and the textbox controls name is "AlsText1". Any ideas out there?

Use this for control's validation rule:

IsDate("20/" & [AlsText1])=True Or Is Null

[nico5038]

Best to place the code behind a Save button.

Try:

IF val(left(me.AlsText1,INSTR(me.AlsText1,"/"))) < 0 or val(left(me.AlsText1,INSTR(me.AlsText1,"/"))) > 12 then
' action invalid month
endif

if len(mid(me.AlsText1,INSTR(me.AlsText1,"/")+1)) <> 2 and
len(mid(me.AlsText1,INSTR(me.AlsText1,"/")+1)) <> 4 then
' invalid year length action
endif

Idea ?

Nic;o)

[NeoPa]

MS Access 2000... I have a textbox control on a form that requires a validation rule that will check for valid monthly periods to be entered. Example... allowable values for March 2006 would be 03/06 or 3/06 or 03/2006 or 3/2006. I have thought of using the left, right, & instr functions but am not having much luck. The control must remain a textbox. The form name is "AlsForm" and the textbox controls name is "AlsText1". Any ideas out there?

In your position, rather than simply controlling the input, I'd let them put in whatever they like then, in the AfterUpdate event I'd check it and convert it to your preferred format.

Private Sub txtDate_AfterUpdate()
    If IsDate(txtDate) Then
        txtDate = Format(CDate(txtDate), "mm/yyyy")
    Else
        txtDate = ""
        MsgBox 'Whatever
    End If
End Sub

[alanpike]

Thank you for you help. This worked great. Sorry it took so long to get back to you. Thanks again.

[NeoPa]

No problem Alan.
It's good to hear that it helped :)