searching a range of dates

[Guest]

I have a data entry form that records empl_no, gross_amt and startdate and
end date. I am trying to alert my users if they have already entered a record
for a giving date. If the employee only worked 1 day they only have a start
date otherwise they have a start and an end date. I am using simple dlookup
to check each record against a history file. My lookup works when the start
date matches or when the start and end dates match exactly but not if the
date falls in the range between the start and end dates.

Example - Employee 12345 has a record in the history file with start date
11/1/04 and an end date 11/5/04. If I enter a new record with 11/1/04 as
start date - I get my message box. If I enter a new record with 11/1/04 as
start and 11/5/04 as end - I get my message box. But if I enter a date like
11/3/04 - nothing.

How can I make it check if my new date or range of dates falls within the
history range of dates?
TIA

 

[Marshall Barton]

I have a data entry form that records empl_no, gross_amt and startdate and
end date. I am trying to alert my users if they have already entered a record
for a giving date. If the employee only worked 1 day they only have a start
date otherwise they have a start and an end date. I am using simple dlookup
to check each record against a history file. My lookup works when the start
date matches or when the start and end dates match exactly but not if the
date falls in the range between the start and end dates.

Example - Employee 12345 has a record in the history file with start date
11/1/04 and an end date 11/5/04. If I enter a new record with 11/1/04 as
start date - I get my message box. If I enter a new record with 11/1/04 as
start and 11/5/04 as end - I get my message box. But if I enter a date like
11/3/04 - nothing.

How can I make it check if my new date or range of dates falls within the
history range of dates?

Did you try the expression I suggested last week?

If not, why not? If you did, what went wrong?

 

[Guest]

Did you try the expression I suggested last week?

If not, why not? If you did, what went wrong?

Hi,

Thanks so much for your responses. Honestly I got lost in all the NZs and
formats.
I ended up cobbling this together - please don't laugh at my code -

Dim varkey As Variant
' If no enddate --> look for start date in history
If IsNull(ENDDATE) Then
varkey = (DLookup("[tblhistory]![remote batch number]", "[tblHistory]",
"([tblHistory]![StartDate] = Forms![SBPREARN]![startdate] And
[tblHistory]![EMPL NUMBER] = Forms![Sbprearn]![EMPL NUMBER]) "))
' If found in history --> msgbox
If Not IsNull(varkey) Then
MsgBox "This employee already has a payment for this work date.
Check batch number " & varkey
Cancel = True
STARTDATE.SetFocus
End If
Else
' If start and end date --> look for dates in history
varkey = (DLookup("[tblhistory]![remote batch number]","[tblHistory]",
"(([tblHistory]![StartDate] >= Forms![SBPREARN]![STARTDATE] and
[tblHistory]![StartDate] <= Forms![SBPREARN]![ENDDATE]) And
[tblHistory]![EMPL NUMBER] = Forms![Sbprearn]![EMPL NUMBER]) "))
' If found in history --> msgbox
If Not IsNull(varkey) Then
MsgBox "This employee already has a payment for this work date.
Check batch number " & varkey
Cancel = True
STARTDATE.SetFocus
End If
End If


This works when the dates are an exact match but I have a feeling I'm not
even close with the date range part. Any comments. Dumb it down for me - I'm
new to this.

Thanks

 

[Marshall Barton]

Thanks so much for your responses. Honestly I got lost in all the NZs and
formats.
I ended up cobbling this together - please don't laugh at my code -

Dim varkey As Variant
' If no enddate --> look for start date in history
If IsNull(ENDDATE) Then
varkey = (DLookup("[tblhistory]![remote batch number]", "[tblHistory]",
"([tblHistory]![StartDate] = Forms![SBPREARN]![startdate] And
[tblHistory]![EMPL NUMBER] = Forms![Sbprearn]![EMPL NUMBER]) "))
' If found in history --> msgbox
If Not IsNull(varkey) Then
MsgBox "This employee already has a payment for this work date.
Check batch number " & varkey
Cancel = True
STARTDATE.SetFocus
End If
Else
' If start and end date --> look for dates in history
varkey = (DLookup("[tblhistory]![remote batch number]","[tblHistory]",
"(([tblHistory]![StartDate] >= Forms![SBPREARN]![STARTDATE] and
[tblHistory]![StartDate] <= Forms![SBPREARN]![ENDDATE]) And
[tblHistory]![EMPL NUMBER] = Forms![Sbprearn]![EMPL NUMBER]) "))
' If found in history --> msgbox
If Not IsNull(varkey) Then
MsgBox "This employee already has a payment for this work date.
Check batch number " & varkey
Cancel = True
STARTDATE.SetFocus
End If
End If


This works when the dates are an exact match but I have a feeling I'm not
even close with the date range part. Any comments. Dumb it down for me - I'm
new to this.

I suggest that you could have tried it, and, if it worked,
we could have worked on understanding how it worked.

Regardless, if you want to avoid some of the NZs and break
it up into separate cases, we can work on straightening out
your code.

The first if block should check if the startdate is in the
range of an existing record. Since an existing record might
or might not have an enddate, we will use the Nz function to
guard against a missing enddate (it will use startdate if
enddate is Null):

varkey = DLookup("[remote batch number]", "tblHistory", _
"(#" & Me!startdate & "# " & _
"Between StartDate And Nz(End Date, StartDate)) " _
& "And ([EMPL NUMBER] =" & Me![EMPL NUMBER]))

The Else block you have is close, but you used startdate
instead of enddate in the second comparison. You should use
Nz here too:

varkey = DLookup("[remote batch number]", "[tblHistory]", _
"(#" & Me.StartDate & "# <= Nz(ENDDATE, STARTDATE)) " & _
"And (#" & Me.EndDate & "# >= STARTDATE) " & _
"And ([EMPL NUMBER] =" & Me![EMPL NUMBER]))

Barring typos, that should work. However, you should be
aware that the second DLookup will do the job alone (without
the need for the If ... Else) if you would just use
Nz(Me.EndDate, Me.StartDate) in place of Me.EndDate.

Note that the construct "#" & Me.startdate & "#" will only
work for the USA Windows regional settings. I previously
used the Format function to allow it to work for any
international setting.