DSum Not Working

  • Thread starter Thread starter iamnu
  • Start date Start date
I

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
 
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?
 
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

iamnu said:
The following is producing a zero value for the DSum function.  Can
someone explain what I've done wrong?
Click to expand...
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;
Click to expand...
Thanks,
Bernie
Click to expand...
Click to expand...

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


iamnu said:
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

iamnu said:
The following is producing a zero value for the DSum function. Can
someone explain what I've done wrong?
Click to expand...
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;
Click to expand...
Thanks,
Bernie
Click to expand...
Click to expand...

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
Click to expand...
 
Try replace the 2nd AcctID with a known AcctID value like:
CCur(Nz(DSum("[BillPayAmt]", "[tblPayDetail]", "[AcctID] = " & 123),0))
--
Duane Hookom
Microsoft Access MVP

iamnu said:
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
Click to expand...
Click to expand...
I added the 0 in the expression, and still get zero for the value of
AcctTotal.
Click to expand...
Yes, AcctID is numeric.  It comes from table zAcctNum which has a
Primary Key AcctID, and AcctNum as Text.
Click to expand...
Bernie
Click to expand...
Click to expand...

Yes, that works for the specific AccID. Strange...
 
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


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

iamnu said:
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
Click to expand...
Is AcctID numeric?
Click to expand...
:
The following is producing a zero value for the DSum function. Can
someone explain what I've done wrong?
Click to expand...
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
Click to expand...

I added the 0 in the expression, and still get zero for the value of
AcctTotal.
Click to expand...
Yes, AcctID is numeric. It comes from table zAcctNum which has a
Primary Key AcctID, and AcctNum as Text.
Click to expand...
Bernie
Click to expand...
Click to expand...

Yes, that works for the specific AccID. Strange...
Click to expand...
 
Back
Top