Overflow ??

[ragtopcaddy via AccessMonster.com]

I have a query that divides profit by sales and returns a decimal between .
3013 and -.2301 for 19 records.
When I put the criteria, <=0.15 in the field, I get "Overflow". When I click
on it, I get "Overflow" again. then the query shows 1 record with "#Name?" in
all fields.

Any suggestions?

Thanks,

Bill

 

[ragtopcaddy via AccessMonster.com]

I had it working for a second by removing the "=" sign from the <=0.15
criteria. Then I got the same Overflow msgs again. Here's the SQL:

SELECT [Store #]
FROM qryStoreMaster
WHERE [Market#]=35620 AND [Brand]="XYZ" AND [Sales]*0.000001>=3 AND [Profits]
/[Sales]<0.15

I also got "Query1.Expr1001 not a valid field" or something at one point.

Thanks,

Bill

 

[ragtopcaddy via AccessMonster.com]

It is only the last bit, "[Profits]/[Sales]<0.15" that causes a problem.
Without that field the query runs fine. I don't get it.

 

[Duane Hookom]

I expect this is a divide by zero error:

SELECT [Store #]
FROM qryStoreMaster
WHERE [Market#]=35620 AND [Brand]="XYZ" AND [Sales]*0.000001>=3
AND IIf(Sales =0,0,[Profits]/[Sales])<0.15

 

[ragtopcaddy via AccessMonster.com]

Thanks for the reply, Duane.

The fact is, all the calculations work fine. I see no zero values in [Sales].
I only get the Overflow when I put the criteria in.

Bill

I expect this is a divide by zero error:

SELECT [Store #]
FROM qryStoreMaster
WHERE [Market#]=35620 AND [Brand]="XYZ" AND [Sales]*0.000001>=3
AND IIf(Sales =0,0,[Profits]/[Sales])<0.15

It is only the last bit, "[Profits]/[Sales]<0.15" that causes a problem.
Without that field the query runs fine. I don't get it.

 

[Duane Hookom]

When you put the criteria in, all rows get evaluated. Try this query

SELECT *
FROM qryStoreMaster
WHERE Sales = 0

--
Duane Hookom
Microsoft Access MVP

Thanks for the reply, Duane.

The fact is, all the calculations work fine. I see no zero values in [Sales].
I only get the Overflow when I put the criteria in.

Bill

I expect this is a divide by zero error:

SELECT [Store #]
FROM qryStoreMaster
WHERE [Market#]=35620 AND [Brand]="XYZ" AND [Sales]*0.000001>=3
AND IIf(Sales =0,0,[Profits]/[Sales])<0.15

It is only the last bit, "[Profits]/[Sales]<0.15" that causes a problem.
Without that field the query runs fine. I don't get it.

 

[ragtopcaddy via AccessMonster.com]

Duane,

I think the problem had to do with querying a complex query. It looks like
Access got a case of brain freeze.

When I redesigned the query from scratch, on the constituent tables, and
simplified it, the calculation and criteria worked just fine.

Thanks for your help,

Bill

When you put the criteria in, all rows get evaluated. Try this query

SELECT *
FROM qryStoreMaster
WHERE Sales = 0

Thanks for the reply, Duane.

[quoted text clipped - 12 lines]

It is only the last bit, "[Profits]/[Sales]<0.15" that causes a problem.
Without that field the query runs fine. I don't get it.