Dcount with miltiply criteria (1 Viewer)

[TipsyWolf]

Hey guys !
i have difficalties with DCount. it says Invalid syntax.
im trying to count how many records i have using 2 criteria in 2 fileds - status1 and status2.
=Nz(DCount("status1";"Form";"status1='" & "rejected" & "' and "status2"="new'");0)

please help me out...

 

[theDBguy]

Looks like you were missing one single quote for the second criteria.

Sent from phone...

 

[TipsyWolf]

Looks like you were missing one single quote for the second criteria.

Sent from phone...

u mean instead of "new'" i need to use "new"'" ?

DCount("Field1";"Form";"status1='" & "rejected" & "' and "status2"="new''")
i think i dont get where exactly i lost a quote. it didn't work though

 

[AHeyne]

If 'rejected' and 'new' are fix string literals (so no variables or controls) then you can use this:

=Nz(DCount("Field1";"Form";"status1='rejected' and status2='new'");0)

Edit: Please try to use code blocks in your postings. This makes code much more readable for us (see my code above).

 

[theDBguy]

u mean instead of "new'" i need to use "new"'" ?

DCount("Field1";"Form";"status1='" & "rejected" & "' and "status2"="new''")
i think i dont get where exactly i lost a quote. it didn't work though

No, I meant:

... =" & "'new'"

or better yet:

... ='" & "new" & "'"

 

[Gasman]

Put the criteria into a string variable.
Debug.print that until you get it correct.
Then use that variable in the function.

 

[TipsyWolf]

Please try to use code blocks in your postings. This makes code much more readable for us (see my code above).

okay, thanks for leting me know about it. didn't know it.

 

[TipsyWolf]

=Nz(DCount("Field1";"Form";"status1='rejected' and status2='new'");0)

thank you very much AHeyne ! your code worked for me just flawlessly !

 

[TipsyWolf]

No, I meant:

... =" & "'new'"

or better yet:

... ='" & "new" & "'"

theDBguy, thank you for your help. it did work for me as well.

 

[theDBguy]

theDBguy, thank you for your help. it did work for me as well.

Glad we could assist. Good luck with your project.