Overflow error

[branston]

Hi!

I keep getting an 'Overflow' error. I've had a look in other peoples questions, but all I seem to find is that the field type might not be big enough... which doesn't seem to make sense for me...:

I have a query which runs fine - its a little slow but there is a lot of data and its not an un-usable speed.
However, as soon as I try and add a criteria (is not null) to one of the fields it comes up with this overflow error.

Surely, the fact that the query runs means all the field types are suitable?

Any ideas what might be going wrong?

Thank you!

 

[the_net_2.0]

could it be at the point of being too complex?

does "is not null" work on all the other fields?

 

[branston]

Hum... Surely if it can deal with the whole query, it can deal with a is not null filter??
I wouldn't say it was hugely complex - I have definitley got queries that are more complex that work fine!

I have tried adding the filter to other fields, as you suggested, and with some it works, some it doesn't (same overflow error)

 

[the_net_2.0]

what do the fields have in common?

the ones that work?

the ones that throw the error?


see what I'm doing? trial and error. do it until you get sick of it, and if the problem has no logical explanation, then the next step is to assume that's it's corruption

 

[vbaInet]

I believe you're performing a calculation in the query that is causing the overflow (e.g. you may need to cast to Long or Double) or the criteria you've set could be the cause too.

Let's see the SQL statement of the query.

 

[branston]

Thanks for the suggestions - I'll crack on with the trail & error I guess!

The SQL is:

SELECT QryRatesCheck1.Phase, QryRatesCheck1.StaffNumber, QryRatesCheck1.StaffName, QryRatesCheck1.StaffAgency, IIf([StaffAgency]="Staff",([StaffInvoiceRate]),[AgencyInvoiceRate]) AS RateA, [InvoiceCostRegular]/[SumOfHoursRegular] AS RateB, IIf([StaffAgency]="Staff",([Current]*[StaffMultiplier]),[Current]*[AgencyMultiplier]) AS Current2, Round(([RateA]-[Current2]),5) AS Variance, IIf([Variance]>0.5 Or [variance]<-0.5,"XXXX","") AS ToLookAt, (DLookUp("[QryWeeklyDetailInvoice].[CostRegular]","[QryWeeklyDetailInvoice]","[EmployeeNumber] = '" & [staffNumber] & "'"))/(DLookUp("[QryWeeklyDetailInvoice].[HoursRegular]","[QryWeeklyDetailInvoice]","[EmployeeNumber] = '" & [staffNumber] & "'")) AS [Current], IIf(Round([Current2],2)<Round([RateA],2),"","<----") AS Problems
FROM QryRatesCheck1;

 

[branston]

by the way - I tried casting to a string but that didn't change the outcome.

 

[vbaInet]

by the way - I tried casting to a string but that didn't change the outcome.

...(e.g. you may need to cast to Long or Double) or the criteria you've set could be the cause too.

...

SELECT QryRatesCheck1.Phase, QryRatesCheck1.StaffNumber, QryRatesCheck1.StaffName, QryRatesCheck1.StaffAgency, 
    IIf([StaffAgency]="Staff",([StaffInvoiceRate]),[AgencyInvoiceRate]) AS RateA, cdbl(nz([InvoiceCostRegular]), 0)/clng(iif([SumOfHoursRegular] = 0, 1, nz([SumOfHoursRegular], 1)) AS RateB, 
    IIf([StaffAgency]="Staff",(clng([Current])*clng([StaffMultiplier])),clng([Current])*clng([AgencyMultiplier])) AS Current2, 
    Round(([RateA]-[Current2]),5) AS Variance, IIf([Variance]>0.5 Or [variance]<-0.5,"XXXX","") AS ToLookAt, 
    (DLookUp("[QryWeeklyDetailInvoice].[CostRegular]","[QryWeeklyDetailInvoice]","[EmployeeNumber] = '" & [staffNumber] & "'"))/(DLookUp("[QryWeeklyDetailInvoice].[HoursRegular]","[QryWeeklyDetailInvoice]","[EmployeeNumber] = '" & [staffNumber] & "'")) AS [Current], 
    IIf(Round([Current2],2)<Round([RateA],2),"","<----") AS Problems
FROM QryRatesCheck1;

That DLookup() can be an INNER JOIN (or OUTTER JOIN) between qryRatesCheck1 and QryWeeklyDetailInvoice via StaffNumber -> EmployeeNumber.

 

[branston]

Good point - Thanks for that, makes the whole thing a lot quicker.
Still getting the overflow error though.
I changed it to a string because thats what the field is being set as (XXXX) Surely you can't set that as a type of number?

 

[vbaInet]

Take out all those calculated fields and run the query. See if you get the error.

If you don't, put them in one by one and after each time run it until you get the error so we know which one is the problem.

What did you change to String?

 

[branston]

Right... the 1st one I come accross that does it is 'Current'. I cast that to Double, and then tried to filter it... it looked like it did the filter, then the error came up.

 

[vbaInet]

You're talking about RateA?

 

[gemma-the-husky]

overflow error can really be indicating a divide by zero error

i haven't checked your query, but if there are any divisions in there, i would definitley look closely at them.

 

[branston]

Oh, sorry, I didn't realise you had made changes to the SQL... incorporated those now... Still not working though :-(
Still falling over on current.

Thanks Dave - I'll double check that as I do have some dividing.

 

[branston]

ahHA!!!
Dave, you were spot on... I added a iff statement around my division and it works fine.
VbaINet - I think that might have been in the SQL code you gave me, looking at - think I missed that when I was making the changes!

Thanks everyone!

 

[vbaInet]

VbaINet - I think that might have been in the SQL code you gave me, looking at - think I missed that when I was making the changes!

It sure was, I just didn't highlight the changes as they were quite a few.

Happy to hear that's resolved.

 

[the_net_2.0]

overflow error can really be indicating a divide by zero error

this is precisely why you should appreciate age, the experience, AND the wisdom that comes along with it.

Nice catch, Dave! Obviously, I didn't know it either.