Run-Time Error 6 - Overflow (1 Viewer)

[andy_dyer]

Ok this feels quite weird...

I've played around with a calculated field on a subform and now I 'sometimes' get this error message...

I changed my calculated field so that it is now

=IIf([currencyid] Is Null,0,FormatNumber(ExchangeRate([Start Date],[CurrencyID]),10))

As previously if there was no currency set it showed #Type which looked weird...

If I now go into my master form for a record that hasn't got a currency set in the subform it displays the runtime error (I can click ok and all ok but want to get rid of the error) BUT

If I go into my master form for a record that HAS got a currency set in the subform and then navigate with record selectors to the same record that hasn't got a currency it DOESN'T display the runtime error...

Why, why, why???

Any ideas??

 

[JANR]

Its because IIF() evaluates both True and False so your function ExchangeRate() gets called regardless of the value of CurrencyID.

You might as well just call the function, but trap the potential overflow error inside your function.

JR

 

[andy_dyer]

Hi - thanks for trying to help me!

I'm definitely a VBA newbie...

If I understand correctly you are suggesting me take the IIf statement back out and just call the function? Would that work?

And then somehow trap the overflow in the function...

I have to admit to not understanding how to do that at all... my function code is currently this;

Public Function ExchangeRate(DateValue As Date, CurrencyID As Integer) As Double

'Returns Exchange Rate for values supplied
'-1 returned if not found

    Dim dblExchangeRate As Double
    Dim qd As QueryDef
    Dim rst As Recordset
    
    On Error Resume Next ' We have to go through with this come what may!
    
    Set qd = CurrentDb.QueryDefs("qselExchangeRateForDateAndCurrency")
    
    With qd
    
        .Parameters(0).Value = CurrencyID
        .Parameters(1).Value = DateValue
        Set rst = .OpenRecordset
        
        With rst
        
            If .BOF And .EOF Then
                dblExchangeRate = -1
            Else
                dblExchangeRate = .Fields(0).Value
            End If
        
        End With
        
    End With
    
    ExchangeRate = dblExchangeRate
    
End Function

 

[JANR]

Change CurrencyID in you function from Integer to Long, if you pass on a value outside the range -32,768 to 32,767 you get an overflow error.

Public Function ExchangeRate(DateValue As Date, CurrencyID As [COLOR=red]Long[/COLOR]) As Double

As for trapping NULL you can use Nz()

=FormatNumber(ExchangeRate([Start Date],[COLOR=red]Nz([CurrencyID],0[/COLOR]),10))

And test for 0 in your function, if found then Exit Function

JR

 

[andy_dyer]

As for trapping NULL you can use Nz()

=FormatNumber(ExchangeRate([Start Date],[COLOR=red]Nz([CurrencyID],0[/COLOR]),10))

And test for 0 in your function, if found then Exit Function

JR

This tells me that I have the wrong number of arguments... ???

And any idea what code I would need to use to test for 0 and then exit?

Sorry for being a pain...

 

[JANR]

Oops forgot to remove the perence belonging to you original IIF(), just remove the last perence.

=FormatNumber(ExchangeRate([Start Date],Nz([CurrencyID],0),10)

Public Function ExchangeRate(DateValue As Date, CurrencyID As Long) As Double

'Returns Exchange Rate for values supplied
'-1 returned if not found

[COLOR="Red"]If CurrencyID = 0 Then
    ExchangeRate = 0  ' Or whatever you want the function to return
    Exit Function
End If
[/COLOR]
    Dim dblExchangeRate As Double
    Dim qd As QueryDef
    Dim rst As Recordset
    
    On Error Resume Next ' We have to go through with this come what may!
    
    Set qd = CurrentDb.QueryDefs("qselExchangeRateForDateAndCurrency")
    
    With qd
    
        .Parameters(0).Value = CurrencyID
        .Parameters(1).Value = DateValue
        Set rst = .OpenRecordset
        
        With rst
        
            If .BOF And .EOF Then
                dblExchangeRate = -1
            Else
                dblExchangeRate = .Fields(0).Value
            End If
        
        End With
        
    End With
    
    ExchangeRate = dblExchangeRate
    
End Function

JR

 

[andy_dyer]

Great thanks for your help!