Just a little nudge with expression building...

[Guest]

Hi all,

On a form, I have a control that is populated by the expression:

DSum("[priceper]*[partquantity]","Parts","serviceID = " &
[Forms]![servicecall]!SCallID)

Which is great, unless I encounter a null value for SCallID. When that
happens when I open the form, I get #error displayed on the control, as well
I should. My difficulty is in circumventing that error.

I tried using Nz(DSum("[priceper]*[partquantity]","Parts","serviceID = " &
[Forms]![servicecall]!SCallID))
but it doesn't appear D functions like the Nz() function, unless I applied
it wrong.

I also tried:
=iif(isnull(DSum("[priceper]*[partquantity]","Parts","serviceID = " &
[Forms]![servicecall]!SCallID)),0,(DSum("[priceper]*[partquantity]","Parts","serviceID = " & [Forms]![servicecall]!SCallID)))

But, that didn't give me the 0 that i wanted if the DSum() returned a null
like I wanted.

Can anybody tell me what I did wrong?

Thanks in advance
R

 

[Dirk Goldgar]

Hi all,

On a form, I have a control that is populated by the expression:

DSum("[priceper]*[partquantity]","Parts","serviceID = " &
[Forms]![servicecall]!SCallID)

Which is great, unless I encounter a null value for SCallID. When
that
happens when I open the form, I get #error displayed on the control,
as well
I should. My difficulty is in circumventing that error.

I tried using Nz(DSum("[priceper]*[partquantity]","Parts","serviceID
= " & [Forms]![servicecall]!SCallID))
but it doesn't appear D functions like the Nz() function, unless I
applied
it wrong.

I also tried:
=iif(isnull(DSum("[priceper]*[partquantity]","Parts","serviceID = " &
[Forms]![servicecall]!SCallID)),0,(DSum("[priceper]*[partquantity]","Par
ts","serviceID
= " & [Forms]![servicecall]!SCallID)))

But, that didn't give me the 0 that i wanted if the DSum() returned a
null
like I wanted.

Can anybody tell me what I did wrong?

Thanks in advance
R

You could do something like this, I think:

=IIf(IsNull([Forms]![servicecall]![SCallID], 0,
DSum("[priceper]*[partquantity]","Parts","serviceID = "
& [Forms]![servicecall]![SCallID]))

(all entered on one line, of course). Or you can actually get away with
this:

=Nz(DSum("[priceper]*[partquantity]","Parts",
"serviceID = [Forms]![servicecall]![SCallID]"), 0)

 

[SteveS]

Hi all,

On a form, I have a control that is populated by the expression:

DSum("[priceper]*[partquantity]","Parts","serviceID = " &
[Forms]![servicecall]!SCallID)

Which is great, unless I encounter a null value for SCallID. When
that
happens when I open the form, I get #error displayed on the control,
as well
I should. My difficulty is in circumventing that error.

I tried using Nz(DSum("[priceper]*[partquantity]","Parts","serviceID
= " & [Forms]![servicecall]!SCallID))
but it doesn't appear D functions like the Nz() function, unless I
applied
it wrong.

I also tried:
=iif(isnull(DSum("[priceper]*[partquantity]","Parts","serviceID = " &
[Forms]![servicecall]!SCallID)),0,(DSum("[priceper]*[partquantity]","Par
ts","serviceID

= " & [Forms]![servicecall]!SCallID)))

But, that didn't give me the 0 that i wanted if the DSum() returned a
null
like I wanted.

Can anybody tell me what I did wrong?

Thanks in advance
R

You could do something like this, I think:

=IIf(IsNull([Forms]![servicecall]![SCallID], 0,
DSum("[priceper]*[partquantity]","Parts","serviceID = "
& [Forms]![servicecall]![SCallID]))

(all entered on one line, of course). Or you can actually get away with
this:

=Nz(DSum("[priceper]*[partquantity]","Parts",
"serviceID = [Forms]![servicecall]![SCallID]"), 0)

=DSum("nz([priceper],0) * nz([partquantity],0)","Parts","serviceID = " &
nz([Forms]![servicecall]!SCallID,0))


will also work.

 

[Dirk Goldgar]

=DSum("nz([priceper],0) * nz([partquantity],0)","Parts","serviceID =
" & nz([Forms]![servicecall]!SCallID,0))


will also work.

Except that transforming a Null value for SCallID to 0 assumes that
there is no real SCallID with that value.

 

[SteveS]

=DSum("nz([priceper],0) * nz([partquantity],0)","Parts","serviceID =
" & nz([Forms]![servicecall]!SCallID,0))


will also work.

Except that transforming a Null value for SCallID to 0 assumes that
there is no real SCallID with that value.

Yes, <sigh> you're right...(again). I hate it when that happens!! <big grin>

I was on the wrong end of a shovel in the hot sun all afternoon unloading
topsoil from my PU.

I was going to suggest using a negative number instead of zero, but after
engaging my half baked brain, I finally realized that if SCallID is an
AutoNumber type, SCallID could be *any* (long int) number (positive or negative).

So unless there was a dummy SCallID record, my suggestion cannot be relied on
to give the correct answer **in all cases**.


Can't tell when it's time to go to bed - it's light 24/7 right now..
But the clock says 10:55, so it's time to shut down the confuser.....

(Thanks Dirk, I learned a new lesson....or did I learn it Again?)

 

[Guest]

Try

=IIf(IsError(DSum("[priceper]*[partquantity]","Parts","serviceID = " &
[Forms]![servicecall]!SCallID)),0,DSum("[priceper]*[partquantity]","Parts","serviceID = " &
[Forms]![servicecall]!SCallID))

 

[Guest]

You put the Nz in the wrong place. You need to make the Null a 0 before you
calculate on it. You are checking the result of the calculation. Here is
where it should be:

DSum("[priceper]*[partquantity]","Parts","serviceID = " &
Nz([Forms]![servicecall]!SCallID,0))