i need a complex validation rule dealing w/ date and day need help here! (1 Viewer)

[gino]

i need a complex validation rule dealing w/ date and day. this date field i have is based on first day of the week, but..i am looking for a validation that the date must be on a monday.

This is a field must be on a monday......

thanks to all in advance....

 

[R. Hicks]

Gino, instead of using the Validation property, use the Before Update event of the txtbox in question. Here is the code:

Private Sub Yourtxtbox_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim strTitle As String
strMsg = "This Entry Date must fall on a Monday." & vbNewLine & _
"Please Check Entry and try again."
strTitle = " Entry Error"

If DatePart("d", Me.ActiveControl) <> vbMonday Then
MsgBox strMsg, vbOKOnly, strTitle
Cancel = True
End If
End Sub

Adjust the message and the title to read what you want.

HTH
RDH

 

[gino]

for some reason...it works but it also takes 5/12/00 which falls on a friday......here is what i got from your code....

Private Sub WEEK_OF_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim strTitle As String
strMsg = "This Entry Date must fall on a Monday." & vbNewLine & _
"Please Check Entry and try again."
strTitle = " Entry Error"

If DatePart("d", Me.WEEK_OF) <> vbMonday Then
MsgBox strMsg, vbOKOnly, strTitle
Cancel = True
End If

End Sub

any clue what's wrong..R. Hicks? thanks.....

 

[R. Hicks]

Sorry Gino, try this:

Private Sub WEEK_OF_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim strTitle As String
strMsg = "This Entry Date must fall on a Monday." & vbNewLine & _
"Please Check Entry and try again."
strTitle = " Entry Error"
Me![Test] = DatePart("w", Me.ActiveControl, vbSunday)
If DatePart("w", Me.ActiveControl) <> vbMonday Then
MsgBox strMsg, vbOKOnly, strTitle
Cancel = True
End If
End Sub

HTH
RDH

 

[gino]

This time it doesnt work at all. but hey i think i screwed up or something.. please take a look at my code. troubleshoot my error please!

Private Sub WEEK_OF_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim strTitle As String
strMsg = "This Entry Date must fall on a Monday." & vbNewLine & _
"Please Check Entry and try again."
strTitle = " Entry Error"
Me![WEEK OF] = DatePart("w", Me.WEEK_OF, vbSunday)
If DatePart("w", Me.WEEK_OF) <> vbMonday Then
MsgBox strMsg, vbOKOnly, strTitle
Cancel = True
End If
End Sub

[This message has been edited by gino (edited 05-11-2000).]

 

[R. Hicks]

Gino, it's been a bad day. You didn't mess it up, I did. I left a line in the code where I was testing a value.

You should be able to copy this into your code window. You can leave the line that say Me.ActiveControl, this means "the value of the currently active control".

Private Sub WEEK_OF_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim strTitle As String
strMsg = "This Entry Date must fall on a Monday." & vbNewLine & _
"Please Check Entry and try again."
strTitle = " Entry Error"
If DatePart("w", Me.ActiveControl) <> vbMonday Then
MsgBox strMsg, vbOKOnly, strTitle
Cancel = True
End If
End Sub

I'll now go stand in the corner,
RDH

 

[gino]

thanks....wow...i learned something too. you are good! thanks...r.hicks...you can stop facing the corner now......j/k...thanks man!