CODEWITHSUNDEEP
vbams-accessms-access-2010
tryer
tryer

Reputation: 81

Back to basics with DCOUNT

Trying to work out where am messing up here:

Dim Count AS string
Count = DCount("F", "tbl_1", "C = '" & .Acc & "'")

The problem is with the criteria part of the count, Ive tested without the criteria and it works fine.

Anything jump out to anyone?

The error received:

Data Type mismatch in criteria expression

** UPDATE ** I've since discovered this works fine, but id still like to know why my original method didn't work

count = DCount("F", "tbl_1", "C =" & .Acc)

Upvotes: 1

Views: 62

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

I'd still like to know why my original method didn't work

In your original code, you were using:

DCount("F", "tbl_1", "C = '" & .Acc & "'")

You stated that the field C is of integer data type, and therefore I would assume that .Acc also returns an integer value.

Therefore, if .Acc were to return 1, the criteria expression would be evaluated as :

DCount("F", "tbl_1", "C = '1'")

Which is the equivalent to the following SQL statement:

select count(F) from tbl_1 where C = '1'

Since the field C is of integer data type, and the where clause is being supplied with the string '1', this will result in the error:

Data Type mismatch in criteria expression.

In the revised code, the DCount expression would be evaluated as:

DCount("F", "tbl_1", "C = 1")

Which is the equivalent to a SQL statement such as:

select count(F) from tbl_1 where C = 1

Correctly supplying the field C within the where clause with an integer value.

Upvotes: 3

Related Questions