DCount and 2 Criterias

  • Thread starter Thread starter Telesphore
  • Start date Start date
T

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.
 
Try this instead:

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

You had an extra quote in there...
 
Paul,

Thank you so much.

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


Paul Overway said:
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


Telesphore said:
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.
Click to expand...
Click to expand...
 
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
 
Telesphore said:
Paul,

Thank you so much.

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


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

Steve Schapel said:
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.
Click to expand...
Click to expand...
 
Tom Stoddard said:
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.
Click to expand...


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).
 
Back
Top