DCount problems (A2k)

Topic

New Reply

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

Reply | Quote

Replies

The where-condition is not quite correct. Try

=DCount(“[BusPros]”,”[tblMain]”,”[BusPros] = 1″)

or

=DCount(“*”,”[tblMain]”,”[BusPros] = 1″)

Reply | Quote

First one worked, thanks

Reply | Quote

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”)

Reply | Quote

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″)

Reply | Quote

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 + “)+”

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

In that case I don’t quite understand whyyou would want to change the control source in code, but this should do it:

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

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

=DCount("BusPros","qryBLSD","BusPros=1")

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

Reply | Quote

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

Reply | Quote

If BusPros had been a variable containing the name of a field, you would have used

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

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.

Reply | Quote

If BusPros had been a variable containing the name of a field, you would have used

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

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.

Reply | Quote

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

Reply | Quote

In that case I don’t quite understand whyyou would want to change the control source in code, but this should do it:

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

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

=DCount("BusPros","qryBLSD","BusPros=1")

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

Reply | Quote

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?

Reply | Quote

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 + “)+”

Reply | Quote

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″)

Reply | Quote

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”)

Reply | Quote

First one worked, thanks

Reply | Quote

The where-condition is not quite correct. Try

=DCount(“[BusPros]”,”[tblMain]”,”[BusPros] = 1″)

or

=DCount(“*”,”[tblMain]”,”[BusPros] = 1″)

Reply | Quote