Division by zero problem

[D Stretton]

I am running a query using 2 tables. One field in the query works out BMI
using the formula Abs([Weight]/([Hgt]*[Hgt])*10000), both the weight and
height come from one table. If I don't put in any criteria for this it is
happy and returns BMI values. As soon as I introduce criteria for BMI e.g.

=17.5 And <=30.5 then I get a division by zero message and the query will

not run. I have checked and here are no zero values in my data. Yesterday I
created an identical query from scratch and it ran fine using BMI and
criteria. Today I have created a different query using BMI and criteria, it
runs the query and I can see correct values in the fields but after ~2
seconds the message 'division by zero' appears and the fields change and all
return the value #Name?. Does anyone have any ideas what the problem is?

Thanks in advance.

 

[Wayne Morgan]

Please post the SQL view of the query.

Have you done a Compact and Repair? This will redo the indexes on the tables
in case they are corrupted and messing up the query. Are you opening the
query itself or viewing its results in a form or report?

Just to try and simplify things to eliminate possible problems, I don't
understand the need for Abs(). Unless Weight can be negative, there is no
way to get a negative value from the equation (Hgt^2 will always be a
positive result and 10000 is positive).

--
Wayne Morgan
MS Access MVP

I am running a query using 2 tables. One field in the query works out BMI
using the formula Abs([Weight]/([Hgt]*[Hgt])*10000), both the weight and
height come from one table. If I don't put in any criteria for this it is
happy and returns BMI values. As soon as I introduce criteria for BMI
e.g.

=17.5 And <=30.5 then I get a division by zero message and the query will

not run. I have checked and here are no zero values in my data.
Yesterday I
created an identical query from scratch and it ran fine using BMI and
criteria. Today I have created a different query using BMI and criteria,
it
runs the query and I can see correct values in the fields but after ~2
seconds the message 'division by zero' appears and the fields change and
all
return the value #Name?. Does anyone have any ideas what the problem
is?

Thanks in advance.

 

[D Stretton]

Hi Wayne

Thanks for the reply. This is the SQL:
SELECT [Single Radiographs].Gycm2, PatientDetails.Weight,
([Weight]/([Hgt]*[Hgt])*10000) AS BMI
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo;

I regularly run compact and repair. Am opening the query itself. You're
right - using ABS was unnecessary - have removed this but still get same
problem.

Should have said that I've run loads of queries over past 2/3 years using
BMI and criteria and never used to have a problem. This has only happened
fairly recently.
--
Don

Please post the SQL view of the query.

Have you done a Compact and Repair? This will redo the indexes on the tables
in case they are corrupted and messing up the query. Are you opening the
query itself or viewing its results in a form or report?

Just to try and simplify things to eliminate possible problems, I don't
understand the need for Abs(). Unless Weight can be negative, there is no
way to get a negative value from the equation (Hgt^2 will always be a
positive result and 10000 is positive).

--
Wayne Morgan
MS Access MVP

I am running a query using 2 tables. One field in the query works out BMI
using the formula Abs([Weight]/([Hgt]*[Hgt])*10000), both the weight and
height come from one table. If I don't put in any criteria for this it is
happy and returns BMI values. As soon as I introduce criteria for BMI
e.g.

=17.5 And <=30.5 then I get a division by zero message and the query will

not run. I have checked and here are no zero values in my data.
Yesterday I
created an identical query from scratch and it ran fine using BMI and
criteria. Today I have created a different query using BMI and criteria,
it
runs the query and I can see correct values in the fields but after ~2
seconds the message 'division by zero' appears and the fields change and
all
return the value #Name?. Does anyone have any ideas what the problem
is?

Thanks in advance.

 

[D Stretton]

Sorry, the SQL previously posted was without criteria. Here it is with
criteria for weight and BMI included:
-- SELECT [Single Radiographs].Gycm2, PatientDetails.Weight,
([Weight]/([Hgt]*[Hgt])*10000) AS BMI
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE (((PatientDetails.Weight)>=82 And (PatientDetails.Weight)<=90) AND
((([Weight]/([Hgt]*[Hgt])*10000))>=20 And
(([Weight]/([Hgt]*[Hgt])*10000))<=33));

Don

Hi Wayne

Thanks for the reply. This is the SQL:
SELECT [Single Radiographs].Gycm2, PatientDetails.Weight,
([Weight]/([Hgt]*[Hgt])*10000) AS BMI
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo;

I regularly run compact and repair. Am opening the query itself. You're
right - using ABS was unnecessary - have removed this but still get same
problem.

Should have said that I've run loads of queries over past 2/3 years using
BMI and criteria and never used to have a problem. This has only happened
fairly recently.
--
Don

Please post the SQL view of the query.

Have you done a Compact and Repair? This will redo the indexes on the tables
in case they are corrupted and messing up the query. Are you opening the
query itself or viewing its results in a form or report?

Just to try and simplify things to eliminate possible problems, I don't
understand the need for Abs(). Unless Weight can be negative, there is no
way to get a negative value from the equation (Hgt^2 will always be a
positive result and 10000 is positive).

--
Wayne Morgan
MS Access MVP

I am running a query using 2 tables. One field in the query works out BMI
using the formula Abs([Weight]/([Hgt]*[Hgt])*10000), both the weight and
height come from one table. If I don't put in any criteria for this it is
happy and returns BMI values. As soon as I introduce criteria for BMI
e.g.
=17.5 And <=30.5 then I get a division by zero message and the query will
not run. I have checked and here are no zero values in my data.
Yesterday I
created an identical query from scratch and it ran fine using BMI and
criteria. Today I have created a different query using BMI and criteria,
it
runs the query and I can see correct values in the fields but after ~2
seconds the message 'division by zero' appears and the fields change and
all
return the value #Name?. Does anyone have any ideas what the problem
is?

Thanks in advance.

 

[Wayne Morgan]

You have 2 tables in the query but you're not specifying the table in the
equation, just the fields. I would expect an error about the ambiguity, but
is their a Hgt field in [Single Readiographs] also?

Off-hand, especially since this is intermittent, I would suspect some type
of corruption. Have you tried importing everything into a new, blank
database? Start by importing just the 2 tables and query in question and see
what happens.

For more information on corruption, see this site:
http://allenbrowne.com/ser-47.html

Also, the SQL you posted doesn't have the criteria on the BMI field. I
wonder if calling Hgt twice is causing a problem (it shouldn't)? Have you
tried

([Weight]/[Hgt]^2*10000) AS BMI

 

[Wayne Morgan]

Thanks, the SQL looks proper.

 

[Pieter Wijnen]

seems like you have a record with a Null or Zero Value for Hgt

Try Substituting Hgt*Hgt With IIF(Nz(hgt,0)=0,1,Hgt*Hgt)

Pieter

You have 2 tables in the query but you're not specifying the table in the
equation, just the fields. I would expect an error about the ambiguity,
but is their a Hgt field in [Single Readiographs] also?

Off-hand, especially since this is intermittent, I would suspect some type
of corruption. Have you tried importing everything into a new, blank
database? Start by importing just the 2 tables and query in question and
see what happens.

For more information on corruption, see this site:
http://allenbrowne.com/ser-47.html

Also, the SQL you posted doesn't have the criteria on the BMI field. I
wonder if calling Hgt twice is causing a problem (it shouldn't)? Have you
tried

([Weight]/[Hgt]^2*10000) AS BMI

--
Wayne Morgan
MS Access MVP

Hi Wayne

Thanks for the reply. This is the SQL:
SELECT [Single Radiographs].Gycm2, PatientDetails.Weight,
([Weight]/([Hgt]*[Hgt])*10000) AS BMI
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo;

I regularly run compact and repair. Am opening the query itself. You're
right - using ABS was unnecessary - have removed this but still get same
problem.

Should have said that I've run loads of queries over past 2/3 years using
BMI and criteria and never used to have a problem. This has only
happened
fairly recently.

 

[Jerry Whittle]

Is Weight in pounds and Hgt (height I'm assuming) in inches? If so I'm
getting some strange numbers from your calculation of
([Weight]/([Hgt]*[Hgt])*10000). The number is expotentially small to almost
zero. Maybe that could be the cause.

Here's my vitals
For WEIGHT: 215 Lbs. (97.5 Kg); HEIGHT: 6 Ft., 1 In. (185.4 CM)
Body Mass Index (BMI) = 28.37 - Overweight.
(In my defense, I've ridden my bicycle over a thousand miles so far this
year including 145 miles this past weeked. I need to back off from the table
I guess.)

Anyway I digress. Let's try simplifying your query as you said that it
started going bad when you entered criteria. Try this first:

SELECT [Single Radiographs].Gycm2, PatientDetails.Weight
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE (((PatientDetails.Weight)>=82
and (PatientDetails.Weight)<=90)
and ((([Weight]/([Hgt]*[Hgt])*10000))>=20
and (([Weight]/([Hgt]*[Hgt])*10000))<=33));

Any problems? If not try this:
SELECT [Single Radiographs].Gycm2, PatientDetails.Weight
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE (PatientDetails.Weight)>=82
and (PatientDetails.Weight)<=90) ;

Then this:

SELECT [Single Radiographs].Gycm2, PatientDetails.Weight
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE ([Weight]/([Hgt]*[Hgt])*10000)>=20
and ([Weight]/([Hgt]*[Hgt])*10000)<=33 ;

Here's using Between statements:
SELECT [Single Radiographs].Gycm2,
PatientDetails.Weight,
([Weight]/([Hgt]*[Hgt])*10000) AS BMI
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE (PatientDetails.Weight) BETWEEN 82 and 90
AND ([Weight]/([Hgt]*[Hgt])*10000) BETWEEN 20 and 33;


--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Sorry, the SQL previously posted was without criteria. Here it is with
criteria for weight and BMI included:
-- SELECT [Single Radiographs].Gycm2, PatientDetails.Weight,
([Weight]/([Hgt]*[Hgt])*10000) AS BMI
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE (((PatientDetails.Weight)>=82 And (PatientDetails.Weight)<=90) AND
((([Weight]/([Hgt]*[Hgt])*10000))>=20 And
(([Weight]/([Hgt]*[Hgt])*10000))<=33));

Don

Hi Wayne

Thanks for the reply. This is the SQL:
SELECT [Single Radiographs].Gycm2, PatientDetails.Weight,
([Weight]/([Hgt]*[Hgt])*10000) AS BMI
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo;

I regularly run compact and repair. Am opening the query itself. You're
right - using ABS was unnecessary - have removed this but still get same
problem.

Should have said that I've run loads of queries over past 2/3 years using
BMI and criteria and never used to have a problem. This has only happened
fairly recently.
--
Don

Please post the SQL view of the query.

Have you done a Compact and Repair? This will redo the indexes on the tables
in case they are corrupted and messing up the query. Are you opening the
query itself or viewing its results in a form or report?

Just to try and simplify things to eliminate possible problems, I don't
understand the need for Abs(). Unless Weight can be negative, there is no
way to get a negative value from the equation (Hgt^2 will always be a
positive result and 10000 is positive).

--
Wayne Morgan
MS Access MVP


I am running a query using 2 tables. One field in the query works out BMI
using the formula Abs([Weight]/([Hgt]*[Hgt])*10000), both the weight and
height come from one table. If I don't put in any criteria for this it is
happy and returns BMI values. As soon as I introduce criteria for BMI
e.g.
=17.5 And <=30.5 then I get a division by zero message and the query will
not run. I have checked and here are no zero values in my data.
Yesterday I
created an identical query from scratch and it ran fine using BMI and
criteria. Today I have created a different query using BMI and criteria,
it
runs the query and I can see correct values in the fields but after ~2
seconds the message 'division by zero' appears and the fields change and
all
return the value #Name?. Does anyone have any ideas what the problem
is?

Thanks in advance.

 

[D Stretton]

Have put in table in equation, still same problem. No, there is not a field
called Hgt in [Single Radiographs]. Have tried importing 2 tables and query
and also created new identical query, still same problem. Have also tried
[Hgt]^2 - same problem. Suspected corruption as it works with some queries.

Will look on website you suggested. Thank you for the time spent on this.
--
Don

You have 2 tables in the query but you're not specifying the table in the
equation, just the fields. I would expect an error about the ambiguity, but
is their a Hgt field in [Single Readiographs] also?

Off-hand, especially since this is intermittent, I would suspect some type
of corruption. Have you tried importing everything into a new, blank
database? Start by importing just the 2 tables and query in question and see
what happens.

For more information on corruption, see this site:
http://allenbrowne.com/ser-47.html

Also, the SQL you posted doesn't have the criteria on the BMI field. I
wonder if calling Hgt twice is causing a problem (it shouldn't)? Have you
tried

([Weight]/[Hgt]^2*10000) AS BMI

--
Wayne Morgan
MS Access MVP

Hi Wayne

Thanks for the reply. This is the SQL:
SELECT [Single Radiographs].Gycm2, PatientDetails.Weight,
([Weight]/([Hgt]*[Hgt])*10000) AS BMI
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo;

I regularly run compact and repair. Am opening the query itself. You're
right - using ABS was unnecessary - have removed this but still get same
problem.

Should have said that I've run loads of queries over past 2/3 years using
BMI and criteria and never used to have a problem. This has only happened
fairly recently.

 

[D Stretton]

Hi Jerry

No, I'm using metric (England not USA) - much easier than the old imperial
measurements and doesn't lead to mix ups! Keep on the bike!

Have tried your first SQL and still get same problem. Next bit works OK,
third bit I get an 'Overflow' message. 4th bit - same problem (runs and I
can see data for a few seconds then I get the 'Division by zero' message up
and data goes).

Thanks for your help with this. Any more ideas?
--
Don

Is Weight in pounds and Hgt (height I'm assuming) in inches? If so I'm
getting some strange numbers from your calculation of
([Weight]/([Hgt]*[Hgt])*10000). The number is expotentially small to almost
zero. Maybe that could be the cause.

Here's my vitals
For WEIGHT: 215 Lbs. (97.5 Kg); HEIGHT: 6 Ft., 1 In. (185.4 CM)
Body Mass Index (BMI) = 28.37 - Overweight.
(In my defense, I've ridden my bicycle over a thousand miles so far this
year including 145 miles this past weeked. I need to back off from the table
I guess.)

Anyway I digress. Let's try simplifying your query as you said that it
started going bad when you entered criteria. Try this first:

SELECT [Single Radiographs].Gycm2, PatientDetails.Weight
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE (((PatientDetails.Weight)>=82
and (PatientDetails.Weight)<=90)
and ((([Weight]/([Hgt]*[Hgt])*10000))>=20
and (([Weight]/([Hgt]*[Hgt])*10000))<=33));

Any problems? If not try this:
SELECT [Single Radiographs].Gycm2, PatientDetails.Weight
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE (PatientDetails.Weight)>=82
and (PatientDetails.Weight)<=90) ;

Then this:

SELECT [Single Radiographs].Gycm2, PatientDetails.Weight
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE ([Weight]/([Hgt]*[Hgt])*10000)>=20
and ([Weight]/([Hgt]*[Hgt])*10000)<=33 ;

Here's using Between statements:
SELECT [Single Radiographs].Gycm2,
PatientDetails.Weight,
([Weight]/([Hgt]*[Hgt])*10000) AS BMI
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE (PatientDetails.Weight) BETWEEN 82 and 90
AND ([Weight]/([Hgt]*[Hgt])*10000) BETWEEN 20 and 33;


--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Sorry, the SQL previously posted was without criteria. Here it is with
criteria for weight and BMI included:
-- SELECT [Single Radiographs].Gycm2, PatientDetails.Weight,
([Weight]/([Hgt]*[Hgt])*10000) AS BMI
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE (((PatientDetails.Weight)>=82 And (PatientDetails.Weight)<=90) AND
((([Weight]/([Hgt]*[Hgt])*10000))>=20 And
(([Weight]/([Hgt]*[Hgt])*10000))<=33));

Don

Hi Wayne

Thanks for the reply. This is the SQL:
SELECT [Single Radiographs].Gycm2, PatientDetails.Weight,
([Weight]/([Hgt]*[Hgt])*10000) AS BMI
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo;

I regularly run compact and repair. Am opening the query itself. You're
right - using ABS was unnecessary - have removed this but still get same
problem.

Should have said that I've run loads of queries over past 2/3 years using
BMI and criteria and never used to have a problem. This has only happened
fairly recently.
--
Don


:

Please post the SQL view of the query.

Have you done a Compact and Repair? This will redo the indexes on the tables
in case they are corrupted and messing up the query. Are you opening the
query itself or viewing its results in a form or report?

Just to try and simplify things to eliminate possible problems, I don't
understand the need for Abs(). Unless Weight can be negative, there is no
way to get a negative value from the equation (Hgt^2 will always be a
positive result and 10000 is positive).

--
Wayne Morgan
MS Access MVP


I am running a query using 2 tables. One field in the query works out BMI
using the formula Abs([Weight]/([Hgt]*[Hgt])*10000), both the weight and
height come from one table. If I don't put in any criteria for this it is
happy and returns BMI values. As soon as I introduce criteria for BMI
e.g.
=17.5 And <=30.5 then I get a division by zero message and the query will
not run. I have checked and here are no zero values in my data.
Yesterday I
created an identical query from scratch and it ran fine using BMI and
criteria. Today I have created a different query using BMI and criteria,
it
runs the query and I can see correct values in the fields but after ~2
seconds the message 'division by zero' appears and the fields change and
all
return the value #Name?. Does anyone have any ideas what the problem
is?

Thanks in advance.

 

[Wayne Morgan]

I would agree. If you are 100% sure there is no zero value for Hgt, then
something is corrupted. A Null value should return Null, not "divide by
zero" since you're not using Nz(). So a Null value shouldn't be a problem.
If you set a Select query to return records where Hgt = 0 do you get any
records returned?

SELECT Hgt FROM PatientDetails WHERE Hgt = 0;

You may also look for entries where Hgt < 1 in case you have a misplaced
decimal in one of the entries accidentally (see next comment).

John Whittle brought up an interesting point; however, even if the result of
the division is close to zero, that shouldn't cause a "divide by zero"
error. The denominator is still going to be sufficiently large so as not to
be interpreted as zero, especially since you're squaring it first and (I
assume) Hgt is >=1, so you're not squaring a fraction only.

For possible corruption fixes, see this link:
http://allenbrowne.com/ser-47.html

--
Wayne Morgan
MS Access MVP

Have put in table in equation, still same problem. No, there is not a
field
called Hgt in [Single Radiographs]. Have tried importing 2 tables and
query
and also created new identical query, still same problem. Have also tried
[Hgt]^2 - same problem. Suspected corruption as it works with some
queries.

Will look on website you suggested. Thank you for the time spent on this.
--
Don

You have 2 tables in the query but you're not specifying the table in the
equation, just the fields. I would expect an error about the ambiguity,
but
is their a Hgt field in [Single Readiographs] also?

Off-hand, especially since this is intermittent, I would suspect some
type
of corruption. Have you tried importing everything into a new, blank
database? Start by importing just the 2 tables and query in question and
see
what happens.

For more information on corruption, see this site:
http://allenbrowne.com/ser-47.html

Also, the SQL you posted doesn't have the criteria on the BMI field. I
wonder if calling Hgt twice is causing a problem (it shouldn't)? Have you
tried

([Weight]/[Hgt]^2*10000) AS BMI

--
Wayne Morgan
MS Access MVP

Hi Wayne

Thanks for the reply. This is the SQL:
SELECT [Single Radiographs].Gycm2, PatientDetails.Weight,
([Weight]/([Hgt]*[Hgt])*10000) AS BMI
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo;

I regularly run compact and repair. Am opening the query itself.
You're
right - using ABS was unnecessary - have removed this but still get
same
problem.

Should have said that I've run loads of queries over past 2/3 years
using
BMI and criteria and never used to have a problem. This has only
happened
fairly recently.

 

[Jerry Whittle]

Not measuring weight in stones? I did a lot of riding when I lived near
Banbury and Bicester for 4 years in your fine country.

Then it must be in the (([Weight]/([Hgt]*[Hgt])*10000)) area. Let's see if
you have any people who are really, really short like completely flat or
nonexistant:

SELECT [Hgt]
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE [Hgt] = 0
OR [Hgt] IS NULL;

Any returns?

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi Jerry

No, I'm using metric (England not USA) - much easier than the old imperial
measurements and doesn't lead to mix ups! Keep on the bike!

Have tried your first SQL and still get same problem. Next bit works OK,
third bit I get an 'Overflow' message. 4th bit - same problem (runs and I
can see data for a few seconds then I get the 'Division by zero' message up
and data goes).

Thanks for your help with this. Any more ideas?
--
Don

Is Weight in pounds and Hgt (height I'm assuming) in inches? If so I'm
getting some strange numbers from your calculation of
([Weight]/([Hgt]*[Hgt])*10000). The number is expotentially small to almost
zero. Maybe that could be the cause.

Here's my vitals
For WEIGHT: 215 Lbs. (97.5 Kg); HEIGHT: 6 Ft., 1 In. (185.4 CM)
Body Mass Index (BMI) = 28.37 - Overweight.
(In my defense, I've ridden my bicycle over a thousand miles so far this
year including 145 miles this past weeked. I need to back off from the table
I guess.)

Anyway I digress. Let's try simplifying your query as you said that it
started going bad when you entered criteria. Try this first:

SELECT [Single Radiographs].Gycm2, PatientDetails.Weight
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE (((PatientDetails.Weight)>=82
and (PatientDetails.Weight)<=90)
and ((([Weight]/([Hgt]*[Hgt])*10000))>=20
and (([Weight]/([Hgt]*[Hgt])*10000))<=33));

Any problems? If not try this:
SELECT [Single Radiographs].Gycm2, PatientDetails.Weight
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE (PatientDetails.Weight)>=82
and (PatientDetails.Weight)<=90) ;

Then this:

SELECT [Single Radiographs].Gycm2, PatientDetails.Weight
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE ([Weight]/([Hgt]*[Hgt])*10000)>=20
and ([Weight]/([Hgt]*[Hgt])*10000)<=33 ;

Here's using Between statements:
SELECT [Single Radiographs].Gycm2,
PatientDetails.Weight,
([Weight]/([Hgt]*[Hgt])*10000) AS BMI
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE (PatientDetails.Weight) BETWEEN 82 and 90
AND ([Weight]/([Hgt]*[Hgt])*10000) BETWEEN 20 and 33;


--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Sorry, the SQL previously posted was without criteria. Here it is with
criteria for weight and BMI included:
-- SELECT [Single Radiographs].Gycm2, PatientDetails.Weight,
([Weight]/([Hgt]*[Hgt])*10000) AS BMI
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE (((PatientDetails.Weight)>=82 And (PatientDetails.Weight)<=90) AND
((([Weight]/([Hgt]*[Hgt])*10000))>=20 And
(([Weight]/([Hgt]*[Hgt])*10000))<=33));

Don


:

Hi Wayne

Thanks for the reply. This is the SQL:
SELECT [Single Radiographs].Gycm2, PatientDetails.Weight,
([Weight]/([Hgt]*[Hgt])*10000) AS BMI
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo;

I regularly run compact and repair. Am opening the query itself. You're
right - using ABS was unnecessary - have removed this but still get same
problem.

Should have said that I've run loads of queries over past 2/3 years using
BMI and criteria and never used to have a problem. This has only happened
fairly recently.
--
Don


:

Please post the SQL view of the query.

Have you done a Compact and Repair? This will redo the indexes on the tables
in case they are corrupted and messing up the query. Are you opening the
query itself or viewing its results in a form or report?

Just to try and simplify things to eliminate possible problems, I don't
understand the need for Abs(). Unless Weight can be negative, there is no
way to get a negative value from the equation (Hgt^2 will always be a
positive result and 10000 is positive).

--
Wayne Morgan
MS Access MVP


I am running a query using 2 tables. One field in the query works out BMI
using the formula Abs([Weight]/([Hgt]*[Hgt])*10000), both the weight and
height come from one table. If I don't put in any criteria for this it is
happy and returns BMI values. As soon as I introduce criteria for BMI
e.g.
=17.5 And <=30.5 then I get a division by zero message and the query will
not run. I have checked and here are no zero values in my data.
Yesterday I
created an identical query from scratch and it ran fine using BMI and
criteria. Today I have created a different query using BMI and criteria,
it
runs the query and I can see correct values in the fields but after ~2
seconds the message 'division by zero' appears and the fields change and
all
return the value #Name?. Does anyone have any ideas what the problem
is?

Thanks in advance.

 

[D Stretton]

Hi Jerry and Wayne

Certainly not measuring in stones! Drives me mad when some people kick and
scream about going metric. Not sure I'd be happy if the pint went though,
asking for 1/2litre or 500ml doesn't have quite the same ring.

Well, I seem to have a solution, thanks to you both. My query is definitely
not returning any records where height is a zero - I have run a query where
there are only 13 records so easy to see. Introduced height field into the
query and criteria >0 and the query runs fine with BMI criteria. I don't
understand why I need to do this. Is this usual? If there were some records
in the table (and there are a few thousand records) with null height but due
to other criteria it was not being included in the query should this matter?
I know it has not done so in the past and I can't see why introducing BMI
criteria messes it up - if I don't have the criteria on for BMI it happily
returns values for all records in my query (no nulls).

I had a new PC last year some time with Windows XP on it. Have had problems
with various things crashing so don't know if this is relevant in some way
and there is also a difference in the way that graphs in Access were
displayed (crooked, dashed lines - not usable). Can't remember whether my
BMI problems came about same time as got XP.

Anyway, I seem to be able to do what I want now. Thank you both for your
time, it is much appreciated.

--
Don

Not measuring weight in stones? I did a lot of riding when I lived near
Banbury and Bicester for 4 years in your fine country.

Then it must be in the (([Weight]/([Hgt]*[Hgt])*10000)) area. Let's see if
you have any people who are really, really short like completely flat or
nonexistant:

SELECT [Hgt]
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE [Hgt] = 0
OR [Hgt] IS NULL;

Any returns?

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi Jerry

No, I'm using metric (England not USA) - much easier than the old imperial
measurements and doesn't lead to mix ups! Keep on the bike!

Have tried your first SQL and still get same problem. Next bit works OK,
third bit I get an 'Overflow' message. 4th bit - same problem (runs and I
can see data for a few seconds then I get the 'Division by zero' message up
and data goes).

Thanks for your help with this. Any more ideas?
--
Don

Is Weight in pounds and Hgt (height I'm assuming) in inches? If so I'm
getting some strange numbers from your calculation of
([Weight]/([Hgt]*[Hgt])*10000). The number is expotentially small to almost
zero. Maybe that could be the cause.

Here's my vitals
For WEIGHT: 215 Lbs. (97.5 Kg); HEIGHT: 6 Ft., 1 In. (185.4 CM)
Body Mass Index (BMI) = 28.37 - Overweight.
(In my defense, I've ridden my bicycle over a thousand miles so far this
year including 145 miles this past weeked. I need to back off from the table
I guess.)

Anyway I digress. Let's try simplifying your query as you said that it
started going bad when you entered criteria. Try this first:

SELECT [Single Radiographs].Gycm2, PatientDetails.Weight
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE (((PatientDetails.Weight)>=82
and (PatientDetails.Weight)<=90)
and ((([Weight]/([Hgt]*[Hgt])*10000))>=20
and (([Weight]/([Hgt]*[Hgt])*10000))<=33));

Any problems? If not try this:
SELECT [Single Radiographs].Gycm2, PatientDetails.Weight
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE (PatientDetails.Weight)>=82
and (PatientDetails.Weight)<=90) ;

Then this:

SELECT [Single Radiographs].Gycm2, PatientDetails.Weight
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE ([Weight]/([Hgt]*[Hgt])*10000)>=20
and ([Weight]/([Hgt]*[Hgt])*10000)<=33 ;

Here's using Between statements:
SELECT [Single Radiographs].Gycm2,
PatientDetails.Weight,
([Weight]/([Hgt]*[Hgt])*10000) AS BMI
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE (PatientDetails.Weight) BETWEEN 82 and 90
AND ([Weight]/([Hgt]*[Hgt])*10000) BETWEEN 20 and 33;


--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Sorry, the SQL previously posted was without criteria. Here it is with
criteria for weight and BMI included:
-- SELECT [Single Radiographs].Gycm2, PatientDetails.Weight,
([Weight]/([Hgt]*[Hgt])*10000) AS BMI
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo
WHERE (((PatientDetails.Weight)>=82 And (PatientDetails.Weight)<=90) AND
((([Weight]/([Hgt]*[Hgt])*10000))>=20 And
(([Weight]/([Hgt]*[Hgt])*10000))<=33));

Don


:

Hi Wayne

Thanks for the reply. This is the SQL:
SELECT [Single Radiographs].Gycm2, PatientDetails.Weight,
([Weight]/([Hgt]*[Hgt])*10000) AS BMI
FROM [Single Radiographs] INNER JOIN PatientDetails ON [Single
Radiographs].UniqueNo = PatientDetails.UniqueNo;

I regularly run compact and repair. Am opening the query itself. You're
right - using ABS was unnecessary - have removed this but still get same
problem.

Should have said that I've run loads of queries over past 2/3 years using
BMI and criteria and never used to have a problem. This has only happened
fairly recently.
--
Don


:

Please post the SQL view of the query.

Have you done a Compact and Repair? This will redo the indexes on the tables
in case they are corrupted and messing up the query. Are you opening the
query itself or viewing its results in a form or report?

Just to try and simplify things to eliminate possible problems, I don't
understand the need for Abs(). Unless Weight can be negative, there is no
way to get a negative value from the equation (Hgt^2 will always be a
positive result and 10000 is positive).

--
Wayne Morgan
MS Access MVP


I am running a query using 2 tables. One field in the query works out BMI
using the formula Abs([Weight]/([Hgt]*[Hgt])*10000), both the weight and
height come from one table. If I don't put in any criteria for this it is
happy and returns BMI values. As soon as I introduce criteria for BMI
e.g.
=17.5 And <=30.5 then I get a division by zero message and the query will
not run. I have checked and here are no zero values in my data.
Yesterday I
created an identical query from scratch and it ran fine using BMI and
criteria. Today I have created a different query using BMI and criteria,
it
runs the query and I can see correct values in the fields but after ~2
seconds the message 'division by zero' appears and the fields change and
all
return the value #Name?. Does anyone have any ideas what the problem
is?

Thanks in advance.

 

[Wayne Morgan]

Don,

Nulls shouldn't cause the error. A Null anywhere in a mathematical
calculation will give an answer of Null. You can try this at the Immediate
window.

?3/Null
Null

Since adding the criteria removes the problem, there is something that
Access thinks is zero, even if it doesn't look that way to you. This tells
me that there is a corrupted record somewhere in the table. To try and find
it, use the query you have with the >0 criteria and see how may records it
returns and compare that to the number of records in the table. Is there a
difference? If there is, run the Unmatched Query wizard using this query and
the table as the sources for the wizard. This will let you find just the
records that are in the table that aren't in the query with the >0 criteria.

As a precaution, make sure you have the latest Office and Jet service packs
installed.