Calculated Field Error Msg

[RobUCSD]

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

 

[Damian S]

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]

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]

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.

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]

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]

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

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.

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]

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.

:

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

 

[Damian S]

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.

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

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.

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]


:

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.

:

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

 

[RobUCSD]

Sorry for being such a pest. The fields are correct but I still get the
error msg. Could you send me the sql of the query you tested. I'll compare it
with mine. Thanks, Rob

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.

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

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.


:

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]


:

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.

:

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

 

[RobUCSD]

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)

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.

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

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.


:

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]


:

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.

:

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

 

[Damian S]

Hi Rob,

No worries... Here's what I'm using:

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

Damian

Sorry for being such a pest. The fields are correct but I still get the
error msg. Could you send me the sql of the query you tested. I'll compare it
with mine. Thanks, Rob

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.

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

:

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.


:

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]


:

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.

:

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

 

[Damian S]

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.

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)

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.

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

:

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.


:

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]


:

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.

:

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