I am making an application to track methanol usage in an oil and gas field throughout the winter months in Canada.
I have a query set up to do several simple but time consuming calculations. There query determines the volume in the tank at the start of the fall based on a measurement, the volume in the tank at the end of spring based on another measurement, and I would like it to determine the methanol usage over the time period. Since methanol may be added during the winter if the storage tanks get low I sometimes need to add a fixed number(s) to the difference of the two fall and spring volumes.
The problem I am having is that not all the tanks get refilled during the winter months. I am using a DSUM function to calculate the total amount of methanol added between the spring and fall dates. If the tanks don’t get refilled, the DSUM function appears to return a null or an empty entry, then when I add the three fields FallVolume, Spring Volume, and AddedMethanol, I get a #Error in the calculated field MethanolUsage. I have tried using IIf(Iserror([AddedMethanol], as well as both IIf(IsNull([AddedMethanol and IsEmpty(). These don’t seem to work help either. I have also tried using the Nz function when calculating [Addedmethanol] and forcing it to return “0.00” if it results in a null value. I have searched the forum and the web and have come up empty so far. At this point I am stumped why it is not working. Any help would be much appreciated.
Thanks, Mike
MethanolUsage for the first record should be 1085.05
FallVolume: FormatNumber(DLookUp("[Volume]","[tblTankGaugeCharts]","[DipDepth]=" & [FallDipReading] & "And [TankID]=" & [MethanolTankID]),2,0)
SpringVolume: FormatNumber(DLookUp("[Volume]","[tblTankGaugeCharts]","[DipDepth]=" & [SpringDipReading] & "And [TankID]=" & [MethanolTankID]),2,0)
I have a query set up to do several simple but time consuming calculations. There query determines the volume in the tank at the start of the fall based on a measurement, the volume in the tank at the end of spring based on another measurement, and I would like it to determine the methanol usage over the time period. Since methanol may be added during the winter if the storage tanks get low I sometimes need to add a fixed number(s) to the difference of the two fall and spring volumes.
The problem I am having is that not all the tanks get refilled during the winter months. I am using a DSUM function to calculate the total amount of methanol added between the spring and fall dates. If the tanks don’t get refilled, the DSUM function appears to return a null or an empty entry, then when I add the three fields FallVolume, Spring Volume, and AddedMethanol, I get a #Error in the calculated field MethanolUsage. I have tried using IIf(Iserror([AddedMethanol], as well as both IIf(IsNull([AddedMethanol and IsEmpty(). These don’t seem to work help either. I have also tried using the Nz function when calculating [Addedmethanol] and forcing it to return “0.00” if it results in a null value. I have searched the forum and the web and have come up empty so far. At this point I am stumped why it is not working. Any help would be much appreciated.
Thanks, Mike
MethanolUsage for the first record should be 1085.05
FallVolume: FormatNumber(DLookUp("[Volume]","[tblTankGaugeCharts]","[DipDepth]=" & [FallDipReading] & "And [TankID]=" & [MethanolTankID]),2,0)
SpringVolume: FormatNumber(DLookUp("[Volume]","[tblTankGaugeCharts]","[DipDepth]=" & [SpringDipReading] & "And [TankID]=" & [MethanolTankID]),2,0)
Attachments
-
qryAnalysis.JPG
