how do I not record date of the weekend or holiday?

[fabiobarreto10]

Friends, I have a subform with some date fields.

How do I not let you record a date weekend date or a holiday?



It has some simple code I can use?

Thanks for helping.

[June7]

What date would you want to record? How are dates entered - textbox, date picker? Don't think can prevent entering the weekend date but can use code to validate it.

Easy to check the entered date to see if falls on weekend. Use WeekDay() function to return the day of the week.

Holidays are trickier. Create a table that has records for holiday dates.

If WeekDay([input date]) = 7 Or WeekDay([input date]) = 1 Or Not IsNull(DLookup("HolDate", "Holidays", "HolDate=#" & [input date] & "#")) Then
'do somethng
End If

[fabiobarreto10]

June7, thanks for help. Only DLookup is always null.

I created a table called "Holidays" and a single field called "HolDate" (date / time). I put a record with
date 07/09/2012.
I
typed in my subform field named "Date1" 07/09/2012.
Then I put vba stop in:

I declared a variable

dim test as date

test = DLookup("HolDate", "Holidays", "HolDate=#" & [Me.Date1] & "#")

But the variable is always null in this case the variable test had to have the value: 07/09/2012.

Where am I wrong?

Thank you.

[June7]

Don't know. I tested this with my db and it works.

[fabiobarreto10]

How DLookup was not working for me, I had to do this:

Private Sub Date1_BeforeUpdate(Cancel As Integer)
Dim lookDate As Date
Dim Counter As Integer
Dim Db As Database, rs As Recordset, sql As String
Set Db = CurrentDb
Set rs = Db.OpenRecordset("SELECT HolDate FROM Holidays")
If rs.BOF Then
Exit Sub
Else
rs.MoveFirst

While Not rs.EOF

lookDate = rs.Fields(0)
If lookDate = Me.Date1 Then
Counter = 1

End If

rs.MoveNext
Wend
End If

If Weekday(Me.Date1) = 7 Or Weekday(Me.Date1) = 1 Or Counter = 1 Then
Cancel = True
Me.Date1.Undo
MsgBox "Please enter a working day!"
Exit Sub
End If
End Sub




It worked perfectly, with only several lines of code.

Thanks for helping.