DH Query by Form

  • Thread starter Thread starter CS
  • Start date Start date
C

CS

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)))
 
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) said:
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)))


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.
Click to expand...
Click to expand...
 
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
 
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


Using FORMAT makes the returned value a string. AND I
Click to expand...
****THINK****
returning
"" as one of the "answers" to the IIF statement also
Click to expand...
forces everything
returned
to be a string. So, I'm guessing that you might need
Click to expand...
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)))


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.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top