DH Query by Form

I pulled in the "applet" from Duane Hookoms link on Rogers
Access Library and I'm impressed but I ran into a little
problem with calculated fields in that they are seem to be
seen as text fields and are not evaluated correctly when
numeric criteria is given. It may not be the QBF
application at all, it could be my calculated field.
My original expression was:
B: Format(IIf(IsNull([FWHM]),"",Sqr((([FWHM]/57.3)^2)-
(0.00356)^2)),"#.0000")
Then I removed the formatting and now I get a datatype
mismatch error.
I'm stuck, I appreciate any help, thanks.

Using FORMAT makes the returned value a string. AND I ****THINK**** returning
"" as one of the "answers" to the IIF statement also forces everything returned
to be a string. So, I'm guessing that you might need to change the expression.

Can you use?
IIf(IsNull([FWHM]),Null,Sqr((([FWHM]/57.3)^2)-(0.00356)^2))

or perhaps?

IIf(IsNull([FWHM]),Null,CCur(Sqr((([FWHM]/57.3)^2)-(0.00356)^2)))



CS wrote:

I pulled in the "applet" from Duane Hookoms link on Rogers
Access Library and I'm impressed but I ran into a little
problem with calculated fields in that they are seem to be
seen as text fields and are not evaluated correctly when
numeric criteria is given. It may not be the QBF
application at all, it could be my calculated field.
My original expression was:
B: Format(IIf(IsNull([FWHM]),"",Sqr((([FWHM]/57.3)^2)-
(0.00356)^2)),"#.0000")
Then I removed the formatting and now I get a datatype
mismatch error.
I'm stuck, I appreciate any help, thanks.

I would wrap the expression in Val() to assure the returned value is
numeric.

Glad to hear you like my QBF!

--
Duane Hookom
MS Access MVP


"John Spencer (MVP)" wrote in message
...
Using FORMAT makes the returned value a string. AND I ****THINK****
returning
"" as one of the "answers" to the IIF statement also forces everything
returned
to be a string. So, I'm guessing that you might need to change the
expression.

Can you use?
IIf(IsNull([FWHM]),Null,Sqr((([FWHM]/57.3)^2)-(0.00356)^2))

or perhaps?

IIf(IsNull([FWHM]),Null,CCur(Sqr((([FWHM]/57.3)^2)-(0.00356)^2)))



CS wrote:

I pulled in the "applet" from Duane Hookoms link on Rogers
Access Library and I'm impressed but I ran into a little
problem with calculated fields in that they are seem to be
seen as text fields and are not evaluated correctly when
numeric criteria is given. It may not be the QBF
application at all, it could be my calculated field.
My original expression was:
B: Format(IIf(IsNull([FWHM]),"",Sqr((([FWHM]/57.3)^2)-
(0.00356)^2)),"#.0000")
Then I removed the formatting and now I get a datatype
mismatch error.
I'm stuck, I appreciate any help, thanks.

CS,

You don't indicate which version of Access, but I just tested your code on
Access 97, and it seems to work properly, although when [FWHM] is not null,
but is small enough that [FWHM]/57.3 .00356, then I get an error because
I'm trying to take the square root of a negative number. Is there a chance
that [FWHM] is less than .203988?

I reformatted your equation and took out a couple of () that should not be
needed, to make it easier to read.

Format(IIf(IsNull([FWHM]),"",Sqr(([FWHM]/57.3)^2-0.00356^2)),"#.0000")

HTH
Dale

"CS" wrote in message
...
I pulled in the "applet" from Duane Hookoms link on Rogers
Access Library and I'm impressed but I ran into a little
problem with calculated fields in that they are seem to be
seen as text fields and are not evaluated correctly when
numeric criteria is given. It may not be the QBF
application at all, it could be my calculated field.
My original expression was:
B: Format(IIf(IsNull([FWHM]),"",Sqr((([FWHM]/57.3)^2)-
(0.00356)^2)),"#.0000")
Then I removed the formatting and now I get a datatype
mismatch error.
I'm stuck, I appreciate any help, thanks.

Thanks for all the help!
-----Original Message-----
I would wrap the expression in Val() to assure the
returned value is
numeric.

Glad to hear you like my QBF!

--
Duane Hookom
MS Access MVP


"John Spencer (MVP)" wrote in
message
...
Using FORMAT makes the returned value a string. AND I
****THINK****
returning
"" as one of the "answers" to the IIF statement also
forces everything
returned
to be a string. So, I'm guessing that you might need
to change the
expression.

Can you use?
IIf(IsNull([FWHM]),Null,Sqr((([FWHM]/57.3)^2)-(0.00356)
^2))

or perhaps?

IIf(IsNull([FWHM]),Null,CCur(Sqr((([FWHM]/57.3)^2)-
(0.00356)^2)))



CS wrote:

I pulled in the "applet" from Duane Hookoms link on
Rogers
Access Library and I'm impressed but I ran into a
little
problem with calculated fields in that they are seem
to be
seen as text fields and are not evaluated correctly
when
numeric criteria is given. It may not be the QBF
application at all, it could be my calculated field.
My original expression was:
B: Format(IIf(IsNull([FWHM]),"",Sqr((([FWHM]/57.3)^2)-
(0.00356)^2)),"#.0000")
Then I removed the formatting and now I get a datatype
mismatch error.
I'm stuck, I appreciate any help, thanks.


.