DCount and 2 Criterias

[Telesphore]

In a control box I have this formula:
=DCount("*";"tbMembers";"[1stCriteria]='1'")

Now I would like:
=DCount("*";"tbMembers";"[1stCriteria]='1'" And [2ndCriteria]='2'")

But it doesn't work.

Thank you.

 

[Paul Overway]

Try this instead:

=DCount("*";"tbMembers";"[1stCriteria]='1'And [2ndCriteria]='2'")

You had an extra quote in there...

 

[Telesphore]

Paul,

Thank you so much.

Just a question of quotes, but easier with the help of specialists.

Try this instead:

=DCount("*";"tbMembers";"[1stCriteria]='1'And [2ndCriteria]='2'")

You had an extra quote in there...

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com

In a control box I have this formula:
=DCount("*";"tbMembers";"[1stCriteria]='1'")

Now I would like:
=DCount("*";"tbMembers";"[1stCriteria]='1'" And [2ndCriteria]='2'")

But it doesn't work.

Thank you.

 

[Steve Schapel]

Telesphore,

The ""s go arount the whole of the criteria section. You have one too
many " in there. Try...
=DCount("*";"tbMembers";"[1stCriteria]='1' And [2ndCriteria]='2'")

- Steve Schapel, Microsoft Access MVP

 

[ALESSANDRO Baraldi]

Paul,

Thank you so much.

Just a question of quotes, but easier with the help of specialists.

Remember a Fields Type ....!
This work only with TextType:
=DCount("*";"tbMembers";"[1stCriteria]='1'And [2ndCriteria]='2'")
If you have Data or Number Type you must change like this:

For DataType:
=DCount("*";"tbMembers";"[1stCriteria]=#Data1# And [2ndCriteria]='2'")

For NumberType
=DCount("*";"tbMembers";"[1stCriteria]=1 And [2ndCriteria]='2'")

The first Criteria change.

Bye.

Alessandro(IT).

 

[Tom Stoddard]

Actually, in the example supplied, you'd be right about the quotes. However,
if the numbers '1' or '2' are being supplied dynamically through controls on
a form or some other method the quotes might need to be applied differently.
You could write the code as:

=DCount("*";"tbMembers";"[1stCriteria]='" & ctlOne.Value & "' AND " &
"[2ndCriteria]='" & ctlTwo.Value & "'")

The control references would need to be outside of the quotes in order for
their values to be properly represented. This example assumes that your
criteria is matching values to fields with string data formats. If you were
using fields with number data formats, the single quotes would not be
necessary.

The use of quotation marks in criteria can get incredibly complicated. I
remember reading an explanation in one of the earlier versions of the Access
Developers Handbook which took several pages of small print to accomplish. I
had to read it several times.

Telesphore,

The ""s go arount the whole of the criteria section. You have one too
many " in there. Try...
=DCount("*";"tbMembers";"[1stCriteria]='1' And [2ndCriteria]='2'")

- Steve Schapel, Microsoft Access MVP

In a control box I have this formula:
=DCount("*";"tbMembers";"[1stCriteria]='1'")

Now I would like:
=DCount("*";"tbMembers";"[1stCriteria]='1'" And [2ndCriteria]='2'")

But it doesn't work.

Thank you.

 

[ALESSANDRO Baraldi]

Actually, in the example supplied, you'd be right about the quotes. However,
if the numbers '1' or '2' are being supplied dynamically through controls on
a form or some other method the quotes might need to be applied differently.
You could write the code as:

=DCount("*";"tbMembers";"[1stCriteria]='" & ctlOne.Value & "' AND " &
"[2ndCriteria]='" & ctlTwo.Value & "'")

The control references would need to be outside of the quotes in order for
their values to be properly represented. This example assumes that your
criteria is matching values to fields with string data formats. If you were
using fields with number data formats, the single quotes would not be
necessary.

The use of quotation marks in criteria can get incredibly complicated. I
remember reading an explanation in one of the earlier versions of the Access
Developers Handbook which took several pages of small print to accomplish. I
had to read it several times.

Hi Tom.
The single Apostroph can produce some trouble with Text data formats, so i
think can be better chr(34).
I remember the "BuildCriteria" function in A97 that automate and Build
itself the right criteria, passing Fields and DataType, but i never use
it......!

Application.BuildCriteria (........)

May be a good solution.

Alessandro(IT).

 

[Telesphore]

Thank you all.

Telesphore