Results 1 to 7 of 7
  1. #1
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151

    Question DCount with AND criteria returns incorrect value

    Hi all

    I have been scouring this forum and many others for a solution to this issue, and while they've helped enormously I still can't resolve my problem. I'm sort of loathed to ask as I think it's a simple problem.

    I am using this code to count the number of records that meet a couple of criteria in a table. The variables are gathered from a form. All variable are string variable in the tables and on the form.

    Code:
    ConfigCount = DCount("[MachineNo]", "[tblMachineConfigurations]", "[MachineNo] = '" & Me.cmbMachineNo & "' And [CustomerID] = " & "'Me.txtCurrentOwnerNo'")
    
    Debug.Print ConfigCount

    The problem is that the statement is returning 0, when I know that (in testing) there are records that meet the criteria, so should be returning a 1.

    If I hard code the variables like this, I get the correct result;



    Code:
    ConfigCount = DCount("[MachineNo]", "tblMachineConfigurations", "[MachineNo] = '1215-1490' And [CustomerID] = '3361'")
    
    Debug.Print ConfigCount
    I've been going around in circles with this and would appreciate any insights in to what I am doing wrong.

    As an aside I've been reading that the function may not work if there are no records that meet the criteria because DCount doesn't count Null values. Is that correct?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The single quotes around customer need to be outside the quotes like the first value.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sorry, I meant inside. Try

    DCount("*", "[tblMachineConfigurations]", "[MachineNo] = '" & Me.cmbMachineNo & "' And [CustomerID] = '" & Me.txtCurrentOwnerNo & "'")

    Note the * in place of the field name too.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Hi pabldy

    Thanks for the quick reply. Your solution works perfectly, but I don't quite understand why.

    Does the use of the * (search all records) make the difference?

    I also note that when you use the *, the DCount is written differently and never really understood why.


    Quote Originally Posted by pbaldy View Post
    Sorry, I meant inside. Try

    DCount("*", "[tblMachineConfigurations]", "[MachineNo] = '" & Me.cmbMachineNo & "' And [CustomerID] = '" & Me.txtCurrentOwnerNo & "'")

    Note the * in place of the field name too.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    From help:

    The DCount function doesn't count records that contain Null values in the field referenced by expr unless expr is the asterisk (*) wildcard character. If you use an asterisk, the DCount function calculates the total number of records, including those that contain Null fields.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Quote Originally Posted by pbaldy View Post
    From help:

    The DCount function doesn't count records that contain Null values in the field referenced by expr unless expr is the asterisk (*) wildcard character. If you use an asterisk, the DCount function calculates the total number of records, including those that contain Null fields.
    OK. I think I understand. Always learning.

    Thank you again for your help.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. dcount with 2 criteria
    By Nobby2193 in forum Access
    Replies: 2
    Last Post: 02-04-2019, 03:46 AM
  2. Replies: 21
    Last Post: 09-02-2018, 11:49 AM
  3. Replies: 9
    Last Post: 07-27-2015, 01:19 PM
  4. Replies: 9
    Last Post: 06-19-2014, 10:38 AM
  5. Dcount() only returns 0 (zero)
    By pploum in forum Access
    Replies: 6
    Last Post: 02-20-2014, 02:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums