Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Sep 2002
    Location
    Camp Lejeune, North Carolina
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DCount problems (A2k)

    I'm trying to get a count of an item off a table, table name is "tblMain" and the field name is "BusPros", its an option count and a number comes into the record. This is what I've got:
    =DCount("[BusPros]","[tblMain]","[BusPros]"="1")

    And yes, there's records with each number in it, but the box keeps showing up as 0

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DCount problems (A2k)

    The where-condition is not quite correct. Try

    =DCount("[BusPros]","[tblMain]","[BusPros] = 1")

    or

    =DCount("*","[tblMain]","[BusPros] = 1")

  3. #3
    Lounger
    Join Date
    Sep 2002
    Location
    Camp Lejeune, North Carolina
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount problems (A2k)

    First one worked, thanks

  4. #4
    Lounger
    Join Date
    Sep 2002
    Location
    Camp Lejeune, North Carolina
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount problems (A2k)

    Got another question on DCounts....I'm picking out all of the fields where the data is 1, 3 and 5...but its returning all of the records? This is what i've got:
    =DCount("[DataManager]","[tblMain]","[DataManager]=1" And "[DataManager]=3" And "[DataManager]=5")

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DCount problems (A2k)

    In the first place, just as in your original question, the where-condition should be one string.

    In the second place, you need to be aware of the difference between And and Or. "A And B" means that A and B must both be true, at the same time. "A Or B" means that either A must be true, or B must be true, or perhaps both.

    [DataManager]=1 And [DataManager]=3 And [DataManager]=5 would mean that DataManager is equal to 1, 3 and 5 at the same time. This is clearly impossible. You need Or here. So:

    =DCount("[DataManager]","[tblMain]","[DataManager]=1 Or [DataManager]=3 Or [DataManager]=5")

    or

    =DCount("*","[tblMain]","[DataManager]=1 Or [DataManager]=3 Or [DataManager]=5")

  6. #6
    Lounger
    Join Date
    Sep 2002
    Location
    Camp Lejeune, North Carolina
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount problems (A2k)

    Sorry to be such a pain in the butt on this stuff, but once again I'm stuck. I'm trying to change a text box's Control Source when a button is clicked. I know I'm missing a + or " here and there, can someone help? Here's what I've got:

    Text5.ControlSource = "=DCount(" + [BusPros] + "," + [qryBLSD] + "," + [BusPros] = 1 + ")+"

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DCount problems (A2k)

    Although + has specialized uses, in general it's better to use & to concatenate strings.

    What are BusPros and qryBLSD? Are they the name of a field and of a query, or are they the name of text controls that *contain* the name of a field and of a query?

  8. #8
    Lounger
    Join Date
    Sep 2002
    Location
    Camp Lejeune, North Carolina
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount problems (A2k)

    BusPros is the name of the field
    qryBLSD is the name of the query

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DCount problems (A2k)

    In that case I don't quite understand whyyou would want to change the control source in code, but this should do it:
    <pre>Text5.ControlSource = "=DCount(""BusPros"",""qryBLSD"",""BusPros = 1"")"
    </pre>

    Note the use of double double quotes inside the string; they will be evaluated as single double quotes in runtime, so that the control source is set to
    <pre>=DCount("BusPros","qryBLSD","BusPros=1")
    </pre>

    You need the double double quotes because otherwise VBA would think that they end the string.

  10. #10
    Lounger
    Join Date
    Sep 2002
    Location
    Camp Lejeune, North Carolina
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount problems (A2k)

    Thanks for all your help Hans...for future reference what would I use if it were a variable instead of a field?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DCount problems (A2k)

    If BusPros had been a variable containing the name of a field, you would have used
    <pre>Text5.ControlSource = _
    "=DCount(""[" & BusPros & "]"",""qryBLSD"",""[" & BusPros & "] = 1"")"
    </pre>

    This instruction still uses qryBLSD as the name of the query, not as a variable. The square brackets are used here in case the value of the variable BusPros contains a space or unusual character.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •