Calculated number

  • Thread starter Thread starter SM
  • Start date Start date
S

SM

I have a calculated field in my query based on a field and
another calculated field and when I try to give it
criteria such as <29, I get all the Null values as well.
The calculations are B: Format(IIf(IsNull([bMEAS]),"",Sqr
((([bMEAS]/57.3)^2)-(0.00356)^2)),"#.0000")and
Particle_Size: Format(IIf(IsNull([bMEAS]),"",(0.9*1.54056)/
(*Cos([XRD2theta]/57.3))),"#.0000")
Can someone explain what's happening and how can I fix it?
I have noticed that the query surrounds my criteria with
quotes so does that mean its being trated as text and not
a number.Thanks in advance.
 
SM,

Yes, your expression results in a string, as the use of the Format()
function will always do. I would suggest to just get the query to deal
with the numerical values, and apply any formatting later in any place
where it makes any difference, e.g. form or report. Also, your
expression is returning a zero-length string "" instead of Null which it
sounds like you really want. Try it like this...
B: IIf(IsNull([bMEAS]),Null,Sqr((([bMEAS]/57.3)^2)-(0.00356)^2))
 
I took out the formatting and even the IIF portion but now
if I try to run the query with criteria I get a "data type
mismatch in criteria expression" error. The SQL code is:
SELECT tblTesting.SampleID, tblTesting.FWHM, Sqr
((([bMEAS]/57.3)^2)-(0.00356)^2) AS B, (0.9*1.54056)/(
*Cos([CDiam]/57.3/2)) AS Particle_Size,tblTesting.CDiam
FROM tblTesting
WHERE (((Sqr((([FWHM]/57.3)^2)-(0.00356)^2))<2));
What's going on?
-----Original Message-----
SM,

Yes, your expression results in a string, as the use of the Format()
function will always do. I would suggest to just get the query to deal
with the numerical values, and apply any formatting later in any place
where it makes any difference, e.g. form or report. Also, your
expression is returning a zero-length string "" instead of Null which it
sounds like you really want. Try it like this...
B: IIf(IsNull([bMEAS]),Null,Sqr((([bMEAS]/57.3)^2)- (0.00356)^2))

--
Steve Schapel, Microsoft Access MVP

I have a calculated field in my query based on a field and
another calculated field and when I try to give it
criteria such as <29, I get all the Null values as well.
The calculations are B: Format(IIf(IsNull ([bMEAS]),"",Sqr
((([bMEAS]/57.3)^2)-(0.00356)^2)),"#.0000")and
Particle_Size: Format(IIf(IsNull([bMEAS]),"", (0.9*1.54056)/
(*Cos([XRD2theta]/57.3))),"#.0000")
Can someone explain what's happening and how can I fix it?
I have noticed that the query surrounds my criteria with
quotes so does that mean its being trated as text and not
a number.Thanks in advance.
Click to expand...

.
Click to expand...
 
SM,

Do you have any records where there is no entry for FWHM? Try putting
Is Not Null in the criteria of the FWHM column in the query, and see if
you still get the error.
 
Yes, I do have records where FWHM is Null but adding the
IS Not Null in that column does not help.
other suggestions?
-----Original Message-----
SM,

Do you have any records where there is no entry for FWHM? Try putting
Is Not Null in the criteria of the FWHM column in the query, and see if
you still get the error.

--
Steve Schapel, Microsoft Access MVP

[email protected] said:
I took out the formatting and even the IIF portion but now
if I try to run the query with criteria I get a "data type
mismatch in criteria expression" error. The SQL code is:
SELECT tblTesting.SampleID, tblTesting.FWHM, Sqr
((([bMEAS]/57.3)^2)-(0.00356)^2) AS B, (0.9*1.54056)/ (
*Cos([CDiam]/57.3/2)) AS Particle_Size,tblTesting.CDiam
FROM tblTesting
WHERE (((Sqr((([FWHM]/57.3)^2)-(0.00356)^2))<2));
What's going on?
Click to expand...

.
Click to expand...
 
SM,

Well, I think the Nulls in the FWHM field will cause a problem. Whether
that is the only problem will only be apparent when you handle the
Nulls. I showed you before how to do this, but you decided to not
include the IIf() function in your query, so who knows? Anyway, you
will need to experiment and test until you have handled the FWHM nulls,
and then see how the query goes.
 
is the second B in your Select statement an attempt to refer to the calculated
field you have aliased as B? I'm assuming so and if so, then you need to
replace the second B for Particle_size with the formula for B. AS Far as I can
recall, Access won't allow you to use an alias like you are attempting.

I took out the formatting and even the IIF portion but now
if I try to run the query with criteria I get a "data type
mismatch in criteria expression" error. The SQL code is:
SELECT tblTesting.SampleID, tblTesting.FWHM, Sqr
((([bMEAS]/57.3)^2)-(0.00356)^2) AS B, (0.9*1.54056)/(
*Cos([CDiam]/57.3/2)) AS Particle_Size,tblTesting.CDiam
FROM tblTesting
WHERE (((Sqr((([FWHM]/57.3)^2)-(0.00356)^2))<2));
What's going on?
-----Original Message-----
SM,

Yes, your expression results in a string, as the use of the Format()
function will always do. I would suggest to just get the query to deal
with the numerical values, and apply any formatting later in any place
where it makes any difference, e.g. form or report. Also, your
expression is returning a zero-length string "" instead of Null which it
sounds like you really want. Try it like this...
B: IIf(IsNull([bMEAS]),Null,Sqr((([bMEAS]/57.3)^2)- (0.00356)^2))

--
Steve Schapel, Microsoft Access MVP

I have a calculated field in my query based on a field and
another calculated field and when I try to give it
criteria such as <29, I get all the Null values as well.
The calculations are B: Format(IIf(IsNull ([bMEAS]),"",Sqr
((([bMEAS]/57.3)^2)-(0.00356)^2)),"#.0000")and
Particle_Size: Format(IIf(IsNull([bMEAS]),"", (0.9*1.54056)/
(*Cos([XRD2theta]/57.3))),"#.0000")
Can someone explain what's happening and how can I fix it?
I have noticed that the query surrounds my criteria with
quotes so does that mean its being trated as text and not
a number.Thanks in advance.
Click to expand...

.
Click to expand...
Click to expand...
 
Sorry to contrdict, John. I think you may have been right WRT Access 2,
but since then this type of usage in a query has been supported.

--
Steve Schapel, Microsoft Access MVP

is the second B in your Select statement an attempt to refer to the calculated
field you have aliased as B? I'm assuming so and if so, then you need to
replace the second B for Particle_size with the formula for B. AS Far as I can
recall, Access won't allow you to use an alias like you are attempting.

I took out the formatting and even the IIF portion but now
if I try to run the query with criteria I get a "data type
mismatch in criteria expression" error. The SQL code is:
SELECT tblTesting.SampleID, tblTesting.FWHM, Sqr
((([bMEAS]/57.3)^2)-(0.00356)^2) AS B, (0.9*1.54056)/(
*Cos([CDiam]/57.3/2)) AS Particle_Size,tblTesting.CDiam
FROM tblTesting
WHERE (((Sqr((([FWHM]/57.3)^2)-(0.00356)^2))<2));
What's going on?

-----Original Message-----
SM,

Yes, your expression results in a string, as the use of
Click to expand...

the Format()
function will always do. I would suggest to just get the
Click to expand...

query to deal
with the numerical values, and apply any formatting later
Click to expand...

in any place
where it makes any difference, e.g. form or report.
Click to expand...

Also, your
expression is returning a zero-length string "" instead
Click to expand...

of Null which it
sounds like you really want. Try it like this...
B: IIf(IsNull([bMEAS]),Null,Sqr((([bMEAS]/57.3)^2)-
(0.00356)^2))

--
Steve Schapel, Microsoft Access MVP


SM wrote:

I have a calculated field in my query based on a field
and

another calculated field and when I try to give it
criteria such as <29, I get all the Null values as
well.

The calculations are B: Format(IIf(IsNull
([bMEAS]),"",Sqr

((([bMEAS]/57.3)^2)-(0.00356)^2)),"#.0000")and
Particle_Size: Format(IIf(IsNull([bMEAS]),"",
(0.9*1.54056)/

(*Cos([XRD2theta]/57.3))),"#.0000")
Can someone explain what's happening and how can I fix
it?

I have noticed that the query surrounds my criteria
with

quotes so does that mean its being trated as text and
not

a number.Thanks in advance.

.
Click to expand...
Click to expand...
Click to expand...
 
Whoops, you are right. I guess I was thinking of using the alias in the order
by or where clause which does seem to fail in at least Access 97. Can't test in
higher versions on this old machine.

And no apology needed for the correction - that is one of the strengths of these newsgroups.

Steve said:
Sorry to contrdict, John. I think you may have been right WRT Access 2,
but since then this type of usage in a query has been supported.

--
Steve Schapel, Microsoft Access MVP
is the second B in your Select statement an attempt to refer to the calculated
field you have aliased as B? I'm assuming so and if so, then you need to
replace the second B for Particle_size with the formula for B. AS Far as I can
recall, Access won't allow you to use an alias like you are attempting.

I took out the formatting and even the IIF portion but now
if I try to run the query with criteria I get a "data type
mismatch in criteria expression" error. The SQL code is:
SELECT tblTesting.SampleID, tblTesting.FWHM, Sqr
((([bMEAS]/57.3)^2)-(0.00356)^2) AS B, (0.9*1.54056)/(
*Cos([CDiam]/57.3/2)) AS Particle_Size,tblTesting.CDiam
FROM tblTesting
WHERE (((Sqr((([FWHM]/57.3)^2)-(0.00356)^2))<2));
What's going on?


-----Original Message-----
SM,

Yes, your expression results in a string, as the use of

the Format()

function will always do. I would suggest to just get the

query to deal

with the numerical values, and apply any formatting later

in any place

where it makes any difference, e.g. form or report.

Also, your

expression is returning a zero-length string "" instead

of Null which it

sounds like you really want. Try it like this...
B: IIf(IsNull([bMEAS]),Null,Sqr((([bMEAS]/57.3)^2)-

(0.00356)^2))

--
Steve Schapel, Microsoft Access MVP


SM wrote:

I have a calculated field in my query based on a field

and

another calculated field and when I try to give it
criteria such as <29, I get all the Null values as

well.

The calculations are B: Format(IIf(IsNull

([bMEAS]),"",Sqr

((([bMEAS]/57.3)^2)-(0.00356)^2)),"#.0000")and
Particle_Size: Format(IIf(IsNull([bMEAS]),"",

(0.9*1.54056)/

(*Cos([XRD2theta]/57.3))),"#.0000")
Can someone explain what's happening and how can I fix

it?

I have noticed that the query surrounds my criteria

with

quotes so does that mean its being trated as text and

not

a number.Thanks in advance.

.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top