C
Can Of Worms
I designed a query which calls the the companion function below, although it
is running REALLY slow, likely from the multiple uses of DLookup in the
function. I have tried to think of a better way to do this, but I am coming
up dry.
2 Source tables:
- 1 table with event information tied to a week ending date, going back to
2007, updated weekly
- 1 table with inventory dates from each location, going back to 2006,
updated weekly
The desired result:
Return the correct inventory year associated with each event week ending
date. For example, if a location has had an inventory on 4/4/08 and 2/3/09,
then I want to add '2009' to a field for all events that are dated 4/5/08
through 2/3/09. Events before would be '2008', events after would be '2010'.
Basically, tag each event with the inventory year it is affecting.
The trouble I have is that not every location has an inventory every year,
and when they do have an inventory, it could be at any point during the year.
If this was static information, then I could use a crosstab as the base and
do the calculations off of that, but it fluctuates enough that this could
easily cause field reference problems.
So I need to account for if a location doesn't exist at all on the inventory
table, or exists for only part of the time. (Lots of {nulls} are possible)
Any suggestions? I feel I am missing something obvious that is query based
instead.
Function:
Function fInventoryYear(StoreN As Integer, WeekEndDate As Date) As Integer
Dim InvDate_Current As Date
Dim InvDate_Next As Date
Dim InvDate_Last As Date
Dim InvDate_Prior As Date
Dim Year_Current As Integer
Dim Year_Next As Integer
Dim Year_Last As Integer
Dim Year_Prior As Integer
fInventoryYear = 0
Year_Current = Year(Now())
Year_Next = Year(Now()) + 1
Year_Last = Year(Now()) - 1
Year_Prior = Year(Now()) - 2
If IsNull(StoreN) = True Or StoreN = 0 Then
Exit Function
End If
If IsNull(DLookup("[StoreN]", "tbl_YearLastInventory", "[StoreN] = " &
StoreN)) = True Then
Exit Function
End If
InvDate_Current = Nz(DLookup("[ID_Date]", "tbl_YearLastInventory",
"[StoreN] = " & StoreN & " and [ID_Year] = " & Year_Current), #1/1/2090#)
InvDate_Next = Nz(DLookup("[ID_Date]", "tbl_YearLastInventory", "[StoreN]
= " & StoreN & " and [ID_Year] = " & Year_Next), #1/1/2090#)
InvDate_Last = Nz(DLookup("[ID_Date]", "tbl_YearLastInventory", "[StoreN]
= " & StoreN & " and [ID_Year] = " & Year_Last), #1/1/2090#)
InvDate_Prior = Nz(DLookup("[ID_Date]", "tbl_YearLastInventory",
"[StoreN] = " & StoreN & " and [ID_Year] = " & Year_Prior), #1/1/2090#)
If IsNull(InvDate_Current) = False Then
If WeekEndDate > InvDate_Current Then
fInventoryYear = Year_Current + 1
Exit Function
End If
End If
If IsNull(InvDate_Last) = False Then
If WeekEndDate > InvDate_Last Then
fInventoryYear = Year_Last + 1
Exit Function
End If
End If
If IsNull(InvDate_Prior) = False Then
If WeekEndDate > InvDate_Prior Then
fInventoryYear = Year_Prior + 1
Exit Function
ElseIf WeekEndDate < InvDate_Prior And Year(InvDate_Prior) =
Year_Prior Then
fInventoryYear = Year_Prior
Exit Function
End If
End If
End Function
is running REALLY slow, likely from the multiple uses of DLookup in the
function. I have tried to think of a better way to do this, but I am coming
up dry.
2 Source tables:
- 1 table with event information tied to a week ending date, going back to
2007, updated weekly
- 1 table with inventory dates from each location, going back to 2006,
updated weekly
The desired result:
Return the correct inventory year associated with each event week ending
date. For example, if a location has had an inventory on 4/4/08 and 2/3/09,
then I want to add '2009' to a field for all events that are dated 4/5/08
through 2/3/09. Events before would be '2008', events after would be '2010'.
Basically, tag each event with the inventory year it is affecting.
The trouble I have is that not every location has an inventory every year,
and when they do have an inventory, it could be at any point during the year.
If this was static information, then I could use a crosstab as the base and
do the calculations off of that, but it fluctuates enough that this could
easily cause field reference problems.
So I need to account for if a location doesn't exist at all on the inventory
table, or exists for only part of the time. (Lots of {nulls} are possible)
Any suggestions? I feel I am missing something obvious that is query based
instead.
Function:
Function fInventoryYear(StoreN As Integer, WeekEndDate As Date) As Integer
Dim InvDate_Current As Date
Dim InvDate_Next As Date
Dim InvDate_Last As Date
Dim InvDate_Prior As Date
Dim Year_Current As Integer
Dim Year_Next As Integer
Dim Year_Last As Integer
Dim Year_Prior As Integer
fInventoryYear = 0
Year_Current = Year(Now())
Year_Next = Year(Now()) + 1
Year_Last = Year(Now()) - 1
Year_Prior = Year(Now()) - 2
If IsNull(StoreN) = True Or StoreN = 0 Then
Exit Function
End If
If IsNull(DLookup("[StoreN]", "tbl_YearLastInventory", "[StoreN] = " &
StoreN)) = True Then
Exit Function
End If
InvDate_Current = Nz(DLookup("[ID_Date]", "tbl_YearLastInventory",
"[StoreN] = " & StoreN & " and [ID_Year] = " & Year_Current), #1/1/2090#)
InvDate_Next = Nz(DLookup("[ID_Date]", "tbl_YearLastInventory", "[StoreN]
= " & StoreN & " and [ID_Year] = " & Year_Next), #1/1/2090#)
InvDate_Last = Nz(DLookup("[ID_Date]", "tbl_YearLastInventory", "[StoreN]
= " & StoreN & " and [ID_Year] = " & Year_Last), #1/1/2090#)
InvDate_Prior = Nz(DLookup("[ID_Date]", "tbl_YearLastInventory",
"[StoreN] = " & StoreN & " and [ID_Year] = " & Year_Prior), #1/1/2090#)
If IsNull(InvDate_Current) = False Then
If WeekEndDate > InvDate_Current Then
fInventoryYear = Year_Current + 1
Exit Function
End If
End If
If IsNull(InvDate_Last) = False Then
If WeekEndDate > InvDate_Last Then
fInventoryYear = Year_Last + 1
Exit Function
End If
End If
If IsNull(InvDate_Prior) = False Then
If WeekEndDate > InvDate_Prior Then
fInventoryYear = Year_Prior + 1
Exit Function
ElseIf WeekEndDate < InvDate_Prior And Year(InvDate_Prior) =
Year_Prior Then
fInventoryYear = Year_Prior
Exit Function
End If
End If
End Function
