DSum Not Working

[iamnu]

The following is producing a zero value for the DSum function. Can
someone explain what I've done wrong?

SELECT tblPayDetail.ProviderID, tblProvider.AcctID,
tblProvider.PayeeID, tblProvider.CoNameID, tblPayDetail.DatePaid,
tblPayDetail.BillPayAmt, CCur(Nz(DSum
("[BillPayAmt]","[tblPayDetail]","[AcctID] = " & [AcctID]))) AS
AcctTotal
FROM tblProvider INNER JOIN tblPayDetail ON tblProvider.ProviderID =
tblPayDetail.ProviderID;

Thanks,
Bernie

 

[Duane Hookom]

You have an Nz() with no second argument. Minimally try add the ,0 to the
expression.
CCur(Nz(DSum("[BillPayAmt]", "[tblPayDetail]", "[AcctID] = " & [AcctID]),0))
AS
AcctTotal

Is AcctID numeric?

 

[iamnu]

You have an Nz() with no second argument. Minimally try add the ,0 to the
expression.
CCur(Nz(DSum("[BillPayAmt]", "[tblPayDetail]", "[AcctID] = " & [AcctID]),0))
AS
AcctTotal

Is AcctID numeric?

--
Duane Hookom
Microsoft Access MVP

The following is producing a zero value for the DSum function.  Can
someone explain what I've done wrong?

SELECT tblPayDetail.ProviderID, tblProvider.AcctID,
tblProvider.PayeeID, tblProvider.CoNameID, tblPayDetail.DatePaid,
tblPayDetail.BillPayAmt, CCur(Nz(DSum
("[BillPayAmt]","[tblPayDetail]","[AcctID] = " & [AcctID]))) AS
AcctTotal
FROM tblProvider INNER JOIN tblPayDetail ON tblProvider.ProviderID =
tblPayDetail.ProviderID;

Thanks,
Bernie

I added the 0 in the expression, and still get zero for the value of
AcctTotal.

Yes, AcctID is numeric. It comes from table zAcctNum which has a
Primary Key AcctID, and AcctNum as Text.

Bernie

 

[Duane Hookom]

Try replace the 2nd AcctID with a known AcctID value like:
CCur(Nz(DSum("[BillPayAmt]", "[tblPayDetail]", "[AcctID] = " & 123),0))
--
Duane Hookom
Microsoft Access MVP

You have an Nz() with no second argument. Minimally try add the ,0 to the
expression.
CCur(Nz(DSum("[BillPayAmt]", "[tblPayDetail]", "[AcctID] = " & [AcctID]),0))
AS
AcctTotal

Is AcctID numeric?

--
Duane Hookom
Microsoft Access MVP

The following is producing a zero value for the DSum function. Can
someone explain what I've done wrong?

SELECT tblPayDetail.ProviderID, tblProvider.AcctID,
tblProvider.PayeeID, tblProvider.CoNameID, tblPayDetail.DatePaid,
tblPayDetail.BillPayAmt, CCur(Nz(DSum
("[BillPayAmt]","[tblPayDetail]","[AcctID] = " & [AcctID]))) AS
AcctTotal
FROM tblProvider INNER JOIN tblPayDetail ON tblProvider.ProviderID =
tblPayDetail.ProviderID;

Thanks,
Bernie

I added the 0 in the expression, and still get zero for the value of
AcctTotal.

Yes, AcctID is numeric. It comes from table zAcctNum which has a
Primary Key AcctID, and AcctNum as Text.

Bernie

 

[iamnu]

Try replace the 2nd AcctID with a known AcctID value like:
CCur(Nz(DSum("[BillPayAmt]", "[tblPayDetail]", "[AcctID] = " & 123),0))
--
Duane Hookom
Microsoft Access MVP

You have an Nz() with no second argument. Minimally try add the ,0 tothe
expression.
CCur(Nz(DSum("[BillPayAmt]", "[tblPayDetail]", "[AcctID] = " & [AcctID]),0))
AS
AcctTotal
Is AcctID numeric?
--
Duane Hookom
Microsoft Access MVP
:
The following is producing a zero value for the DSum function.  Can
someone explain what I've done wrong?
SELECT tblPayDetail.ProviderID, tblProvider.AcctID,
tblProvider.PayeeID, tblProvider.CoNameID, tblPayDetail.DatePaid,
tblPayDetail.BillPayAmt, CCur(Nz(DSum
("[BillPayAmt]","[tblPayDetail]","[AcctID] = " & [AcctID]))) AS
AcctTotal
FROM tblProvider INNER JOIN tblPayDetail ON tblProvider.ProviderID =
tblPayDetail.ProviderID;
Thanks,
Bernie

I added the 0 in the expression, and still get zero for the value of
AcctTotal.

Yes, AcctID is numeric.  It comes from table zAcctNum which has a
Primary Key AcctID, and AcctNum as Text.

Bernie

Yes, that works for the specific AccID. Strange...

 

[Duane Hookom]

I think the issue is that both tblProvider and tblPayDetail both contain
AcctID and you aren't telling DSum() which one to use.

--
Duane Hookom
Microsoft Access MVP

Try replace the 2nd AcctID with a known AcctID value like:
CCur(Nz(DSum("[BillPayAmt]", "[tblPayDetail]", "[AcctID] = " & 123),0))
--
Duane Hookom
Microsoft Access MVP

On Jan 4, 9:55 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
You have an Nz() with no second argument. Minimally try add the ,0 to the
expression.
CCur(Nz(DSum("[BillPayAmt]", "[tblPayDetail]", "[AcctID] = " & [AcctID]),0))
AS
AcctTotal

Is AcctID numeric?

:
The following is producing a zero value for the DSum function. Can
someone explain what I've done wrong?

SELECT tblPayDetail.ProviderID, tblProvider.AcctID,
tblProvider.PayeeID, tblProvider.CoNameID, tblPayDetail.DatePaid,
tblPayDetail.BillPayAmt, CCur(Nz(DSum
("[BillPayAmt]","[tblPayDetail]","[AcctID] = " & [AcctID]))) AS
AcctTotal
FROM tblProvider INNER JOIN tblPayDetail ON tblProvider.ProviderID =
tblPayDetail.ProviderID;

Thanks,
Bernie

I added the 0 in the expression, and still get zero for the value of
AcctTotal.

Yes, AcctID is numeric. It comes from table zAcctNum which has a
Primary Key AcctID, and AcctNum as Text.

Bernie

Yes, that works for the specific AccID. Strange...