If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
DH Query by Form
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. |
#3
|
|||
|
|||
DH Query by Form
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. |
#4
|
|||
|
|||
DH Query by Form
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. |
#5
|
|||
|
|||
DH Query by Form
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. . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Display a query result in form | Le Tran | New Users | 0 | June 15th, 2004 09:08 PM |
how to combine fields on a form in a query as criteria? | Henro | New Users | 3 | June 8th, 2004 03:50 PM |
running a query in a form | juliec | Running & Setting Up Queries | 1 | June 8th, 2004 02:39 AM |
Query Form: Print Report | Dennis | Running & Setting Up Queries | 1 | June 6th, 2004 01:08 PM |
Query By Form | Craig | Running & Setting Up Queries | 2 | June 1st, 2004 01:10 AM |