I’ve been beating my head against the wall all day, and I can’t figure this out. Below is a code snippet that should help you understand the issue.
I have a list called ‘StoreSales’, keyed by storeNumber and dept, separated by a ‘~’). Store 70051 has no sales of dept 12, so StoreSales(70051~012) should return 0…but…
ALL of my messageboxes in the below code are returning ‘5.5’, which is actually the value in StoreSales(70051~009), which is directly above 70051~12 in my list. However, when I stick the first messagebox in my snip below, it returns zero, and ALL SUBSEQUENT messageboxes with the exact same text correctly return 0. If I comment out that one message box, all the values fall back to the erroneous ‘5.5’.
It’s completely bizarre…I can’t figure out why a messagebox before this piece of code ‘fixes’ the issue (I can put it anywhere in the code, as long as it’s before the majority of this function call). Any ideas? I could use a second set of eyes on this one…
CODE:
Function LoadAgedInventoryData(result) As String
On Error Goto ErrorHandler
Dim StoreNumber As String
Dim MerchandiseDept As String
Dim WeekEnding As String
Dim listKey As String
’ THIS IS THE ONE MESSAGEBOX THAT SEEMS TO TRIGGER THE ZERO VALUE (CORRECT)
’ Messagebox StoreSales( “70051~012” ).SalesTY
If result.IsResultSetAvailable Then
Do
result.NextRow
MerchandiseDept = Trim(result.GetValue("ww_mdse_dpt"))
' only store the data if it's from a department that we care about, as defined in the department docs by end-users
If Iselement( departmentList( MerchandiseDept ) ) Then
StoreNumber = result.GetValue( "deptid" )
WeekEnding = result.GetValue( "ww_week_end_dt" )
listKey = StoreNumber & "~" & MerchandiseDept
Dim sd As StoreData
If Iselement(StoreSales(listKey)) Then
sd = StoreSales(listKey)
Messagebox listKey & " Test: " & sd.SalesTY
End If
sd.Age3 = result.GetValue( "ww_inv_age_0_3" )
sd.Age6 = result.GetValue( "ww_inv_age_4_6" )
sd.Age9 = result.GetValue( "ww_inv_age_7_9" )
sd.Age12 = result.GetValue( "ww_inv_age_10_12" )
sd.Age13Plus = result.GetValue( "ww_inv_age_gt_12" )
sd.Per03 = result.GetValue( "Per03" )
sd.Per46 = result.GetValue( "Per46" )
sd.Per79 = result.GetValue( "Per79" )
sd.Per1012 = result.GetValue( "Per1012" )
sd.Per13 = result.GetValue( "Per13" )
sd.ageAll = result.GetValue("deptInvTotal")
StoreSales(listKey) = sd
End If
Loop Until result.IsEndOfData
LoadAgedInventoryData = WeekEnding
’ THIS MESSAGE BOX WILL SHOW ‘ZERO’ IF THE FIRST MESSAGEBOX APPEARS, ELSE IT ERRONEOUSELY SHOWS ‘5.5’
Messagebox StoreSales( "70051~012" ).SalesTY
End If
Exit Function
ErrorHandler:
Messagebox "LoadAgedInventoryData " & Err() & ": " & Error() & " line " & Erl()
Exit Function
End Function