Overflow error

[dv8123456789]

Hi - I have a system I have inherited, which after about of year of no problems has a query that is now resulting in an overflow error that I cannot get to the bottom of. The problem seems to stem from an expression field...

% Load: Round(IIf(Sum(IIf([Data Type]="Capacity",[DataValue],0))>0,(Sum(IIf([Data Type]="Demand",[DataValue],0))/(Sum(IIf([Data Type]="Capacity",[DataValue],0))-Sum(IIf([Data Type]="Holiday",[DataValue],0))))),4)

I have tried to do a little research into why this might be and from what I have found it could be something to do with divide by zero, but interestingly the query runs and I don't get divide by zero error. Its only when I sort the field or try and use the result set in another query that I get the error. It looks as if my predecessor has tried to accommodate for zeros by using IIF's?? Any help would be much appreciated. Thanks

 

[arnelgp]

this is an impossible expression.

when [data type]="Capacity", then there is
a guarantee result since the divisor is also
the value of the "Capacity".

when [data type] is "Demand", the divisor will
be 0. (division by zero).

[data type]="Holiday" is only used on the divisor
and not used as dividend.

 

[dv8123456789]

Hi Arnelgp - Thanks for your reply. demand is whats being divided and only if capacity is greater than zero so unless there is a situation whereby there is the same amount of holiday as there is capacity, that's the only way I'm seeing it could be divided by zero.

 

[arnelgp]

What are the Fields being Grouped
on your Query? Maybe we can use
a function instead of nested IIFs
to do the calculation.

 

[gemma-the-husky]

overflow is a number too big to fit the size of the variable. Although it can be an integer problem, it is most likely to be caused by a divide by zero, but not necessarily.

if the divisor is nearly zero, then you may get an overflow result.

I find the sort of expression you have used very hard to follow. Is there another way of deriving the result? Can you verify the individual elements of the expression to check the values

 

[dv8123456789]

the fields we are grouping on are Month and Person

 

[dv8123456789]

Hi Dave - thanks for your reply. I couldn't agree more - it is hard to follow. What we are trying to calculate is per person per month a percentage load of how much someone is utilised so if they have capacity they will be under 1 e.g. 0.70, if they are over it will be 1.something

 

[arnelgp]

May i know what is the table name, ill try to build a function similar to what you have in a query so i can add validation.

Can u provide me with the straight formula without the iifs.

 

[dv8123456789]

The table name it comes from is called Demand and capacity which contains a number of columns but the key ones here are as follows

Person ReferenceData TypeMeasureProject ReferenceMonthDataValueA N OtherCapacityDays Oct22A N OtherCapacityDays Nov22A N OtherCapacityDays Dec19A N OtherHolidayDays Nov2A N OtherDemandDaysProject 1Oct8A N OtherDemandDaysProject 2Oct4

so taking November, "A N Other" has a capacity of 22, 12 days demand across all projects and 2 holidays days. therefore current workload = Demand/(Capacity-Holiday) = 0.6

 

[arnelgp]

I cant guess which one belongs to which column. Can you give an excel file sample of the table and the fields concern.

 

[dv8123456789]

Sorry that table didn't come out right. lets see if this works...

Person Ref Data Type Measure Project Ref Month Data Value
A N Other Capacity Days Oct 22
A N Other Capacity Days Nov 22
A N Other Capacity Days Dec 19
A N Other Holiday Days Nov 2
A N Other Capacity Days Oct 22
A N Other Demand Days P1 Nov 8
A N Other Demand Days P2 Nov 4

 

[dv8123456789]

Didn't realise you could attach, sorry

 

[arnelgp]

here, replace your LoadPercent on your Query
with:

%LoadPct: LoadPct([Person], [Month])


but first copy and paste the code in a
Standard Module:

Public Function LoadPct(Person As Variant, Mnth As Variant) As Double
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' Purpose:      Replace multiple IIFs in query
'               of dv8123456789
' Return:       Double value
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim CapacityValue As Long
    Dim DemandValue As Long
    Dim HolidayValue As Long
    Dim rstDAO As DAO.Recordset
    
    ' convert all to string since I don't know
    ' what are the datatype
    Person = Person & ""
    Mnth = Mnth & ""
    ' first get the total Capacity of this Person on this Month
    Set rstDAO = DBEngine(0)(0).OpenRecordset( _
            "SELECT SUM([DataValue]) From [Demand] " & _
            "WHERE [Person] & """"=" & Chr(34) & Person & Chr(34) & " And " & _
            "[Month] & """"=" & Chr(34) & Mnth & Chr(34) & _
            " And [Data Type]=" & Chr(34) & "Capacity" & Chr(34))
            
    If Not (rstDAO.BOF And rstDAO.EOF) Then
        rstDAO.MoveFirst
        CapacityValue = Nz(rstDAO(0), 0)
    End If
    
    ' next get the total Demand of this Person on this Month
    Set rstDAO = DBEngine(0)(0).OpenRecordset( _
            "SELECT SUM([DataValue]) From [Demand] " & _
            "WHERE [Person] & """"=" & Chr(34) & Person & Chr(34) & " And " & _
            "[Month] & """"=" & Chr(34) & Mnth & Chr(34) & _
            " And [Data Type]=" & Chr(34) & "Demand" & Chr(34))
            
    If Not (rstDAO.BOF And rstDAO.EOF) Then
        rstDAO.MoveFirst
        DemandValue = Nz(rstDAO(0), 0)
    End If
    
    ' next get the total Holidays of this Person on this Month
    Set rstDAO = DBEngine(0)(0).OpenRecordset( _
            "SELECT SUM([DataValue]) From [Demand] " & _
            "WHERE [Person] & """"=" & Chr(34) & Person & Chr(34) & " And " & _
            "[Month] & """"=" & Chr(34) & Mnth & Chr(34) & _
            " And [Data Type]=" & Chr(34) & "Holiday" & Chr(34))
            
    If Not (rstDAO.BOF And rstDAO.EOF) Then
        rstDAO.MoveFirst
        HolidayValue = Nz(rstDAO(0), 0)
    End If
    
    rstDAO.Close
    Set rstDAO = Nothing
    
    If (CapacityValue - HolidayValue) > 0 Then _
        LoadPct = Round(DemandValue / (CapacityValue - HolidayValue), 4)
    
End Function

 

[dv8123456789]

wow, this well beyond me!! Thanks, hopefully I can get this to work. I have made a couple of changes I think I need to make (changed person and mnth to strings from variant as they are texts - think that's what you meant and also changed the code from person to person reference because that is the name of the column, which might not have been obvious in the table I typed. Also the table name is "#tbl demand and capacity" but its falling over on the text highlighted in bold with an error of syntax error

Public Function LoadPct(person As String, Mnth As String) As Double
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' Purpose: Replace multiple IIFs in query
' of dv8123456789
' Return: Double value
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim CapacityValue As Long
Dim DataValue As Long
Dim HolidayValue As Long
Dim rstDAO As DAO.Recordset



' convert all to string since I don't know
' what are the datatype
person = person & ""
Mnth = Mnth & ""
' first get the total Capacity of this Person on this Month



Set rstDAO = DBEngine(0)(0).OpenRecordset( _
"SELECT SUM([DataValue]) From [#tbl Demand and Capacity] " &
"WHERE [Person Reference] & """"=" & Chr(34) & Person & Chr(34) & " And " & _
"[Month] & """"=" & Chr(34) & Mnth & Chr(34) & _
" And [Data Type]=" & Chr(34) & "Capacity" & Chr(34))

If Not (rstDAO.BOF And rstDAO.EOF) Then
rstDAO.MoveFirst
CapacityValue = Nz(rstDAO(0), 0)
End If

' next get the total Demand of this Person on this Month
Set rstDAO = DBEngine(0)(0).OpenRecordset( _
"SELECT SUM([DataValue]) From [#tbl Demand and Capacity] " & _
"WHERE [Person] & """"=" & Chr(34) & person & Chr(34) & " And " & _
"[Month] & """"=" & Chr(34) & Mnth & Chr(34) & _
" And [Data Type]=" & Chr(34) & "Demand" & Chr(34))

If Not (rstDAO.BOF And rstDAO.EOF) Then
rstDAO.MoveFirst
DemandValue = Nz(rstDAO(0), 0)
End If

' next get the total Holidays of this Person on this Month
Set rstDAO = DBEngine(0)(0).OpenRecordset( _
"SELECT SUM([DataValue]) From [Demand] " & _
"WHERE [Person Reference] & """"=" & Chr(34) & person & Chr(34) & " And " & _
"[Month] & """"=" & Chr(34) & Mnth & Chr(34) & _
" And [Data Type]=" & Chr(34) & "Holiday" & Chr(34))

If Not (rstDAO.BOF And rstDAO.EOF) Then
rstDAO.MoveFirst
HolidayValue = Nz(rstDAO(0), 0)
End If

rstDAO.Close
Set rstDAO = Nothing

If (CapacityValue - HolidayValue) > 0 Then _
LoadPct = Round(DemandValue / (CapacityValue - HolidayValue), 4)

End Function

 

[arnelgp]

You are missing an underscore at rhe end of line. Ampersand space underscore. Also check all rhe code and spot and replace the fields and table name.