help with date range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm trying to create a data entry form that will spot duplicate pay dates.
Each record will have an employee number, amount, start date and end date.
The record can be for a single day(start date) or multiple days(range between
start and end dates) so not every record will have an end date. What I want
to do is have a msg box pop up if a particular employee already has an entry
in a history file matching the start date or any date within the range. I'm
having a brain freeze here so any ideas will be appreciated.
 
Lorien2733 said:
I'm trying to create a data entry form that will spot duplicate pay dates.
Each record will have an employee number, amount, start date and end date.
The record can be for a single day(start date) or multiple days(range between
start and end dates) so not every record will have an end date. What I want
to do is have a msg box pop up if a particular employee already has an entry
in a history file matching the start date or any date within the range. I'm
having a brain freeze here so any ideas will be appreciated.
Click to expand...


If I understand what you're trying to do, then try using
something similar to this air code in the txtStartDate text
box's BeforeUpdate event procedure:

Dim varKey As Variant
If IsNull(txtStartDate) Then Exit Sub
varKey = DLookup("keyfield", "table", "StartDate <= " _
& Format(Nz(txtEndDate, txtStartDate), "\#m\/d\/yyyy\#") _
& " AND Nz(EndDate, StartDate) >= " _
& Format(StartDate, "\#m\/d\/yyyy\#" _
& " AND EmpID = " & txtEmpID)
If Not IsNull(varKey ) Then
MsgBox "Invalid start date, check record ID " & varKey
Cancel = True
End If

A slight variation of that should also be used in the
txtEndDate text box's BeforeUpdate event.
 
Lorien,
I only want to add a comment here. Marshall's code is what you need to
solve your problem, given the data you have, but I think you should
seriously reconsider one point.
No one should have only a StartDate. A StartDate of 1/1/05 does not
properly indicate that someone has worked one day. In other words, if
someone has a StartDate of 1/1/05 and an End Date of 1/6/05, you can use
DateDiff to quickly calculate the days worked. For people who only have a
StartDate, that method fails, and you need to create a seperate calculation.
Consider this...
If someone works 1 day, they should have a Start date of 1/1/05 and an
EndDate of 1/2/05. That way, everybody's DaysWorked can be calculated using
DateDiff, and you can avoid all the IsNull and NZ coding your doing now, and
in the future. Those Null EndDates are going to be a pain in the neck on
down the road.

hth
Al Camp
 
Back
Top