Question Dount using 2 criteria for Field 1 and field 2

[tjtolbe1]

Dcount using 2 criteria for Field 1 and field 2

Hi team, I was hoping the guru's on this site can help me plz.
I have a Dcount which needs to check 2 fields and it works, but when i add a second criteria to field 1 it returns an incorrect result or shows an error#.

System used:
Access: 2010
This is used in a Userform> Control Source
Field 1 should [mty] = "990" or "996" (Source in short text type)
and field 2 should look for "2. Afternoon Shift"


Returns a correct result, but only checks field 1 for criteria 1 and field 2 for criteria 1.
DCount("[ID]","[tbl_UpdatedRawData]","[MTy]=" & Chr(34) & "990" & Chr(34) & "AND [Typ] =" & Chr(34) & "RW1" & Chr(34) & " AND [Shift] =" & Chr(34) & "2. Afternoon Shift" & Chr(34))


Returns a incorrect results when i add a second criteria to field 1.
DCount("[ID]","[tbl_UpdatedRawData]","[MTy]=" & Chr(34) & "990" & Chr(34) & " Or [MTy] = " & Chr(34) & "996" & Chr(34) & "AND [Typ] =" & Chr(34) & "RW1" & Chr(34) & " AND [Shift] =" & Chr(34) & "2. Afternoon Shift" & Chr(34))

 

[sneuberg]

If you are just getting unexpected results then it's most likely that you want parenthesis around the Ored parts as shown in red below. Otherwise the First [MTy]= '990' dominates the entire expression.

DCount("[ID]", "[tbl_UpdatedRawData]", "[COLOR="Red"][B]([/B][/COLOR][MTy]= '990' Or [MTy] = '996[COLOR="red"][B]')[/B][/COLOR] AND [Typ] = 'RW1'  AND [Shift] = '2. Afternoon Shift'")

Here I've assumed all of the fields are text. You can make your expressions more readable with single quotes which you can use within double quotes. In any case the use of Chr(34) in place of double quotes isn't necessary and you don't have to concatenate the double quote in like the Chr function.

If you want to use double quotes within double quotes then just double them where you want one. It would look like:

DCount("[ID]", "[tbl_UpdatedRawData]", "([MTy]= ""990"" Or [MTy] = ""996"") AND [Typ] = ""RW1""  AND [Shift] = ""2. Afternoon Shift""")

You won't see it done that way very often.

 

[tjtolbe1]

Thanks sneubery, that worked. cant thank you enough.