Report not pulling all that I am looking for

[jtfalk]

I have an Access DB that I am having an issue getting. i am trying to have it
look up the last inventory entered. Currently it is looking up the
information and is finding the last inventory input nad printing that number
out - which is good. The issue is that on a day when 1st and 2nd shift put in
it is only pulling and printing the 2nd shift data because it is looking at
the last entered date but not verifying against the shift. So since on 1-22
1st and 2nd both put in but 2nd did it last when I print the inventory for
that day only the second shift numbers come up for every shift that day, adn
the same for every day.

Here is what I have so far:
-----------------------------------------------------------
dtShiftDate = Forms![Division Inventory qry frm]!tbDate
iShift = Forms![Division Inventory qry frm]!Shift
-----------------------------------------------------------

Function LookupValue_OHQTY(iMasterID As Integer, sPartNo As String) As String

On Error GoTo LookupValue_OHQTY_Error

If (iMasterID > 0) Then
LookupValue_OHQTY = DLookup("[OHQty]", "[Inventory_Shift Input tbl]",
"[MasterID]=" & iMasterID & " AND [PartNo]='" & sPartNo & "'")
Exit Function
End If

LookupValue_OHQTY_Error:
LookupValue_OHQTY = -11
End Function
------------------------------------------------------------
Function LookupMasterID(dtShiftDate As Date, iShift As Integer, sDept As
String) As Integer

On Error GoTo LookupMasterID_Error

LookupMasterID = DLookup("[ID]", "[Master tbl]", "[ShiftDate]=" &
SQLDate(dtShiftDate) & " AND [Shift]=" & iShift & " AND [Dept]='" & sDept &
"'")
Exit Function

LookupMasterID_Error:
LookupMasterID = 10
End Function
------------------------------------------------------------
' START Department 74-2 set up to search for the last inventory put in the
computer
Dim dtLast0742Date As Date
Dim iLast0742Shift As Integer
Dim stQuery0742 As String
Dim stDept0742 As String

stDept0742 = "0742"
stQuery0742 = "SELECT ShiftDate, Shift from [Master tbl] where [Master
tbl].Dept='" & stDept0742 & "' AND ShiftDate<=" & SQLDate(dtShiftDate) & "
ORDER by ShiftDate DESC, Shift DESC;"

Set rs = CurrentDb.OpenRecordset(stQuery0742)

Dim iRecordCount0742 As Integer
iRecordCount0742 = rs.RecordCount
If (iRecordCount0742 > 0) Then
dtLast0742Date = rs.Fields(0)
iLast0742Shift = rs.Fields(1)
End If
' END Department 74-2 set up to search for the last inventory put in the
computer
------------------------------------------------------------
iMasterID0742 = LookupMasterID(dtLast0742Date, iLast0742Shift, "0742")
------------------------------------------------------------
srptWk!lblWk1GearSets.Caption = LookupValue_OHQTY(iMasterID0742, "WKF 3.07
Purple")
------------------------------------------------------------

from table master tbl
ID ShiftDate Dept SupervisorID Shift RunHours RunHoursVarianceExplanation
Notes
18335 1/21/2009 0742 68 1 0.00
18352 1/21/2009 0742 121 2 0.00
------------------------------------------------------------

from table inventory_shift inventory tbl
ID MasterID PartNo Model PartDescr OHQty NumPal_Racks Time Reprocess
BayLocation
698350 18352 WKF 3.07 Purple 864.00 0.00 20:55 0
ID MasterID PartNo Model PartDescr OHQty NumPal_Racks Time Reprocess
BayLocation
697710 18335 WKF 3.07 Purple 684.00 0.00 13:47 0

------------------------------------------------------------
So in this case it always prints out 864 if I put in the date of 1-21 no
matter what shift I ask for since it is the LAST input. How can I have look
at the date and find the shift that I am looking for but if the date has no
information it looks at the last date entered and the last info there - so it
would always find 2nd shift since it sorts it in order which is fine since
that is the last shift. meaning that if the last info was put in on 1-21-09
on 2nd shift if I ran the report for 1-24-09 1st shift it would pull up
values from 1-21-09 but if I put in 1-12-09 1st shift it will look up and
find 1-12-09 1st shift and NOT 1-12-09 2nd shift because 2nd had the last
entry that day.