RE: Calculated Field Error Msg

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

The error you have with this one is that when fldQT or fldRR are null, you
are replacing them with an empty string then attempting to divide a string by
1000.

As a test, try just having nz(fldQT, 0) and nz(fldRR, 0) as fields in your
query and see how that goes. Then, try putting them back together again.

Damian.

"RobUCSD" wrote:

This is what I'm using and I don't get the syntax error but I still get the
ERROR# in the calculated field cell. I don't get it. Rob

Calc QTc: CLng((Nz([fldQT],"")/1000)/Sqr(Nz([fldRR],"")/1000)*1000)

"Damian S" wrote:

Hi Rob,

I have tested it here (using fields named as you have them) and it works
fine. You must ensure that the name inside the square brackets is actually
the name of the field from your tables (if you are using it inside the
query), not the name of the control on your form.

Damian.

"RobUCSD" wrote:

Damian, thanks again. I tried the expression as it appears below and I get a
syntax error. I've tried monkeying with it to no avail. Any ideas.
Thanks, Rob

"Damian S" wrote:

Hi Rob,

You could do it in your query (assuming the fields are available).

Like this:

CLng((nz([fldQT], 0)/1000)/Sqr(nz([fldRR], 0)/1000)*1000) AS [Calc QTc]

Just be careful using nz in the divisor (you can't divide by zero as you
know) - although this isn't an issue above.

Damian.


"RobUCSD" wrote:

Damian, Thanks for your help.

Do I need to do this thru code in the form, or can I do this in the query?
If in the query, how would I do this? Here is the calculation.

CLng(([fldQT]/1000)/Sqr([fldRR]/1000)*1000) AS [Calc QTc]


"Damian S" wrote:

Hi Rob,

You need to check for null in your fields, either use the nz function or
iif(isnull(FIELD), "", FIELD_CALCULATION)

Hope this helps.

Damian.

"RobUCSD" wrote:

I have a calculated field in my form. The two fields the calculation is based
on aren't always filled out, and this is ok. The problem is that when the
fields aren't filled out, the calculated field displays "ERROR#".

Is there a way to suppress this message?

Thanks, Rob
.

Relevant Pages

  • Re: CreateQueryDef
    ... If you are concerned with creating a unique name for the temporary query, ... The QueryDef is used as a parameter in a second QueryDef. ... use an empty string for the name of the query? ...
    (microsoft.public.access.modulesdaovba)
  • RE: Form Wont Allow Additions
    ... "RobUCSD" wrote: ... The second query does allow me to udate and add new. ... FROM tblOPmedsLU INNER JOIN tblOPmeds ON tblOPmedsLU.fldOPMedsLUno = ... a cbo in the head that is used to narrow down the selections cbo in the ...
    (microsoft.public.access.formscoding)
  • Re: help with correct syntax
    ... Since you've told the query to return an empty string, ... Or try wrapping the entire IIF in CVDate (But you still have to get rid of the ... > to do the calculation in my query. ...
    (microsoft.public.access.queries)
  • Re: query results in Access
    ... Your field is probably an empty string, a string with no character in it, which is also considered a NULL in some database, but not in Jet, neither in MS SQL Server. ... The format of the query is as follows: ... not produce a meaningful comparison to a non-NULL field; ...
    (microsoft.public.access.queries)
  • RE: Calculated Field Error Msg
    ... "RobUCSD" wrote: ... error msg. ... Could you send me the sql of the query you tested. ... "Damian S" wrote: ...
    (microsoft.public.access.formscoding)