Dsums working intermitently

[Sketchin]

Hey All,

I have some Dsum's that calculate some metrics values and output them to a textbox on my form. The procedure that runs is behind a "refresh" command button and a select case statement that determines if I want metrics for the entire country, or just a specific province by looking at a specified combo box on my form. The code that runs for the country case fails, but works for the provincial case (by "fails" I mean that zero records are returned). This doesn't really make any sense as the Dsum in both case statements is looking at the same queries, but just adding an additional criteria.

Here is the code that fails:

'******************************************************************************************
'Calculate and populate textboxes displaying Fiscal year equipment days for all of Canada
'******************************************************************************************
FiscalEquipDaysSME = Nz(DSum("WorkingDays", "UniQryFiscalUsageToDateDashboardNew", "CategoryID = 1")) + _
           Nz(DSum("UsageDays", "qryFiscalEquipmentDaysRapidProto", "CategoryID = 1"))
            
FiscalEquipDaysStartup = Nz(DSum("WorkingDays", "UniQryFiscalUsageToDateDashboardNew", "CategoryID = 3")) + _
            Nz(DSum("UsageDays", "qryFiscalEquipmentDaysRapidProto", "CategoryID = 3"))
            
FiscalEquipDaysUNI = Nz(DSum("WorkingDays", "UniQryFiscalUsageToDateDashboardNew", "CategoryID = 2")) + _
            Nz(DSum("UsageDays", "qryFiscalEquipmentDaysRapidProto", "CategoryID = 2"))

'*********************************************************' 'Set values for 3 textboxes containing fiscal year equipment days
'*********************************************************
Me.TxtTotalUsageToDateFiscalSME.Value = FiscalEquipDaysSME
Me.txtTotalFiscalUsageToDateUNI.Value = FiscalEquipDaysUNI
Me.txtTotalFiscalUsagetoDateStartup.Value = FiscalEquipDaysStartup

This is the code that works:

'******************************************************************************************
'Calculate and populate textboxes displaying Fiscal year equipment days for selected province
'******************************************************************************************

FiscalEquipDaysSME = Nz(DSum("WorkingDays", "UniQryFiscalUsageToDateDashboardNew", "CategoryID = 1 AND [State_Province codes]= '" & ProvinceFilter & "'")) + _
            Nz(DSum("UsageDays", "qryFiscalEquipmentDaysRapidProto", "CategoryID = 1 AND [State_Province codes]= '" & ProvinceFilter & "'"))
            
FiscalEquipDaysStartup = Nz(DSum("WorkingDays", "UniQryFiscalUsageToDateDashboardNew", "CategoryID = 3 AND [State_Province codes]= '" & ProvinceFilter & "'")) + _
            Nz(DSum("UsageDays", "qryFiscalEquipmentDaysRapidProto", "CategoryID = 3 AND [State_Province codes]= '" & ProvinceFilter & "'"))
            
FiscalEquipDaysUNI = Nz(DSum("WorkingDays", "UniQryFiscalUsageToDateDashboardNew", "CategoryID = 2 AND [State_Province codes]= '" & ProvinceFilter & "'")) + _
            Nz(DSum("UsageDays", "qryFiscalEquipmentDaysRapidProto", "CategoryID = 2 AND [State_Province codes]= '" & ProvinceFilter & "'"))

'*********************************************************'' Set values for 3 textboxes containing fiscal year equipment days
'*********************************************************
Me.TxtTotalUsageToDateFiscalSME.Value = FiscalEquipDaysSME
Me.txtTotalFiscalUsageToDateUNI.Value = FiscalEquipDaysUNI
Me.txtTotalFiscalUsagetoDateStartup.Value = FiscalEquipDaysStartup

If I run either of those queries on their own, they return records. For some reason, the Dsum just doesn't want to sum the appropriate field.

This is where it gets crazy....if I copy the front and back end onto a different network location and re-link the tables to the copied back end, it all works fine.

I should also mention that there are 20-30 dsums running in the same procedure and they all work fine no matter what. :banghead::banghead::banghead:

 

[The_Doc_Man]

The problem with domain aggregates is that they sometimes fall flat when nulls are involved. DSum is one of the ones that doesn't behave correctly around nulls.

I see you have the general construct "Nz(Dsum(yadda,yadda,...),something)"

I might have expected better results from DSum( Nz(yadda), 0), yadda, yadda )

The first case can fail if the DSum trips on a null. The second case cannot trip on a null because you always intercept the null first.

 

[Sketchin]

I figured out how to fix it. I moved the back end to ANY other directory on our network, or on my local drive. If it is at R:\Database Tables, it fails, if it's at R:\Database Tables\New folder, or any other directory, it works fine.

I have also moved the front end all over the place, and as long as the back end is in R:\Database Tables, it fails.

Keep in mind that nothing on that drive has changed, and it has been working fine for the last 2 years.

Very strange.