Need Help on DCount

  • Thread starter Thread starter Ned
  • Start date Start date
N

Ned

Hi all, Can someone help me why I am getting an error on the select
statement below

SELECT TBL_Name.Invoice, TBL_Name.DeptCode, TBL_Name.ProductCode,
TBL_Name.Amount, DCount('*','TBL_Name','Invoice=' & [Invoice] & " and
DeptCode<'" & [DeptCode] & "") AS SNo
FROM TBL_Name
ORDER BY TBL_Name.Invoice, TBL_Name.DeptCode;

Regards, Ed
 
Hi all, Can someone help me why I am getting an error on the select
statement below

SELECT TBL_Name.Invoice, TBL_Name.DeptCode, TBL_Name.ProductCode,
TBL_Name.Amount, DCount('*','TBL_Name','Invoice=' & [Invoice] & " and
DeptCode<'" & [DeptCode] & "") AS SNo FROM TBL_Name
ORDER BY TBL_Name.Invoice, TBL_Name.DeptCode;

Regards, Ed
Click to expand...

Looks like you're missing a closing single quote for the second criteria.
 
SELECT TBL_Name.Invoice, TBL_Name.DeptCode
, TBL_Name.ProductCode,
TBL_Name.Amount
, DCount("*","TBL_Name","Invoice='" & [Invoice] & "' and
DeptCode<'" & [DeptCode] & "") AS SNo
FROM TBL_Name
ORDER BY TBL_Name.Invoice
, TBL_Name.DeptCode;

Is Invoice a number field or a text field? Is DeptCode a number field
or text field?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Try:For the final field, try:
DCount("*","TBL_Name", "([Invoice] = " & Nz([Invoice],0) &
") AND ([DeptCode] < """ & [DeptCode] & """)") AS SNo

This assumes that Invocie is a Number field, and DeptCode is a Text field.
If the invoice number was null, it uses Nz() to stick a zero into the
string. It uses the double-quotes which DCount() expects.

Note that the sorting of the text field DeptCode may not be as you expect:
since text fields sort character-by-character, 19 sorts as less than 2 for
example.
 
Try:For the final field, try:
    DCount("*","TBL_Name", "([Invoice] = " & Nz([Invoice],0) &
        ") AND ([DeptCode] < """ & [DeptCode] & """)") AS SNo

This assumes that Invocie is a Number field, and DeptCode is a Text field..
If the invoice number was null, it uses Nz() to stick a zero into the
string. It uses the double-quotes which DCount() expects.

Note that the sorting of the text field DeptCode may not be as you expect:
since text fields sort character-by-character, 19 sorts as less than 2 for
example.
--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.




Hi all, Can someone help me why I am getting an error on the select
statement below
Click to expand...
SELECT TBL_Name.Invoice, TBL_Name.DeptCode, TBL_Name.ProductCode,
TBL_Name.Amount, DCount('*','TBL_Name','Invoice=' & [Invoice] & " and
DeptCode<'" & [DeptCode] & "") AS SNo
FROM TBL_Name
ORDER BY TBL_Name.Invoice, TBL_Name.DeptCode;
Click to expand...
Regards, Ed- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Hi all. InvoiceNo is a Numeric Field and DeptCode a Text. Ed
 
Back
Top