Need wider DLookUp

[Andrew Thorpe]

I enter workshifts for staff and need to check to make sure that a person is not already working somewhere else on that date. I am using the DLookUp function to see if the proposed date matches any existing bookings. This works fine as long as there is only one date standing in the LookUp list. But if there are more, then my check misses the warning if it's not top of the list. Can anyone suggest a plan whereby the LookUp checks all the values in the list - not just the first one it comes to. Would much appreciate any help. Thanks. Andrew

 

[pbaldy]

What is your existing formula? It doesn't sounds like you've included a criteria:

http://www.mvps.org/access/general/gen0018.htm

 

[Andrew Thorpe]

Paul. Many thanks for your reply. At the moment, I am using:

If (StartDate Or FinishDate) = DLookup("StartDate", "qryStaffConfliction") Then
DisplayMessage ("Please check for Staff confliction").

So at the moment, I am not using a criteria in the final section of my LookUp. In the StaffConfliction query shown below, the bit about the Shift not being equal is to prevent other shifts within the same invoice from triggering the message box.

SELECT SHIFT.StartDate
FROM SHIFT
WHERE (((SHIFT.StartDate)>=Date()) AND ((SHIFT.NurseFK)=[Forms]![frmInvoice]![sfrmShift]![NurseFK]) AND ((SHIFT.InvFK)<>[Forms]![frmInvoice]![InvoiceID]));

I was assuming that the LookUp will only recognize the first or only conflicting date whilst a nurse could have several shifts booked and thus my LookUp might not see them.

 

[DCrake]

I think you need to reverse your logic

On your form first perform a dlookup

X = Nz(DLookup("StartDate","QueryOrTable","StartDate>Date() And NurseFK = " Me.NurseFK & " And InvFK = '" & Me.InvoiceID ),0)

If X <> 0 Then [COLOR="SeaGreen"]'Found something[/COLOR]
   If X = StartDate Or X = FinishDate Then [COLOR="seagreen"]'Does it match either the start or end date[/COLOR]
        [COLOR="Blue"]Do something here....[/COLOR]
   End If
End If

 

[Andrew Thorpe]

David
Many thanks. I had been wondering if I should "start from the other end". I'll try your idea. Thank you. Andrew

 

[Andrew Thorpe]

Paul. I have put in a criteria at the end of the expression to see if the value = the one I'm looking for - and it works!! Many thanks for your suggestion. What a great forum! Thank you. Andrew

What is your existing formula? It doesn't sounds like you've included a criteria:

http://www.mvps.org/access/general/gen0018.htm

 

[pbaldy]

Happy to help Andrew.