Overflow

[Blanelle]

I am working on a database and I running into an overflow error in my query. It was working find with the simple <2.5 which is the less productivity allowed for employees. Now I am suddenly getting this error overflow. I have removed the other 2 filters that are in the query and they do not appear to be the problem since it runs fine without the equation. I tried =<2.5 and =(<2.5). I tried changing the results to a long interger, single and double but that did not work.

 

[Alansidman]

Post your SQL statement so that we can diagnose the issue.

 

[Severin]

Have you tried NZ(YourExpression) or nz the different parts of your expression?~)

 

[Severin]

Good point from Alan!~)

 

[Blanelle]

SELECT Employees.Sites, Employees.[Employee Name], Employees.[Employee Number], Productivity.[Product Date], Productivity.[FD Prep time], Productivity.TWC, Productivity.[TWC Equiv], Productivity.[FD Tracking], Productivity.[FD Tracking Equiv], Productivity.[FD Pend Returns], Productivity.[Pend Returns Equiv], Productivity.[FD Logging], Productivity.[FD Logging Equiv], Productivity.[Interview Equiv], Productivity.Interviews, Productivity.[Interviews & Equivs], Productivity.[Total Proj/Prep], Productivity.[Hrs Wked], Productivity.[Per Hr Rate], Productivity.CaidCareChip, Productivity.[CCChip Equv]
FROM Employees INNER JOIN Productivity ON Employees.[Employee Number] = Productivity.[Employee Number]
WHERE (((Employees.Sites)="Baytown") AND ((Productivity.[Product Date])=Date()-1) AND ((Productivity.[Per Hr Rate])<2.5));

 

[Alansidman]

Have you tried to run a compact and repair on your db. I don't see anything unusual in your query.

 

[pbaldy]

Also, are Employees and Productivity tables or queries? If one or both is a query, does it run without the error?

 

[Blanelle]

I have tried the repair option but it didn't help. Employees and Productivity are both tables. The Productivity table has calculated fields.

 

[MSAccessRookie]

I have tried the repair option but it didn't help. Employees and Productivity are both tables. The Productivity table has calculated fields.

Can any of the selected values be NULL? The Query seems to expect that they would not be, and the results could tend to be unpredictable if it turns out that they are.

 

[pbaldy]

Or could any of the calculated fields divide by zero? I haven't used the calculated fields, so I'm not sure how that would flow through if it was happening.

 

[Blanelle]

Yes,the results could be null. How can I revise it to prevent the overflow. I think that may be the answer! Thanks

 

[pbaldy]

The query works without that criteria? Try

SELECT Employees.Sites, Employees.[Employee Name], Employees.[Employee Number], Productivity.[Product Date], Productivity.[FD Prep time], Productivity.TWC, Productivity.[TWC Equiv], Productivity.[FD Tracking], Productivity.[FD Tracking Equiv], Productivity.[FD Pend Returns], Productivity.[Pend Returns Equiv], Productivity.[FD Logging], Productivity.[FD Logging Equiv], Productivity.[Interview Equiv], Productivity.Interviews, Productivity.[Interviews & Equivs], Productivity.[Total Proj/Prep], Productivity.[Hrs Wked], Productivity.[Per Hr Rate], Productivity.CaidCareChip, Productivity.[CCChip Equv]
FROM Employees INNER JOIN Productivity ON Employees.[Employee Number] = Productivity.[Employee Number]
WHERE Employees.Sites="Baytown" AND Productivity.[Product Date]=Date()-1 AND Nz(Productivity.[Per Hr Rate], 0)<2.5

 

[Blanelle]

Thank you everyone ! It worked like a charm.