DCount error

[azhar2006]

Hello guys


I have a form I've put into it unstructured text field I've put into it this expression, but it appears there is an error
= DCount ("*"; "tblMaster"; "(Rank = 'Dean' OR Rank = 'colonel' OR Rank = 'provider' OR Rank = 'pioneer' OR Rank = 'captain' OR Rank = 'first lieutenant' OR Rank = 'lieutenant') ")

[ssanfu]

Try replacing the semi-colons with commas.
Does this error out?

= DCount ("*", "tblMaster", "Rank = 'Dean' ")

If not, starting adding in the criteria one at a time.

[azhar2006]

Thank you
When you change the interval semicolon show me this message
Is it possible to convert them to function sub in vba
It works well on the report

[ssanfu]

Oops, I forgot about international settings. You are right - need to use semi-colons.

It works well on the report

If it works in a report, it should work on a form.
Does this cause an error?

= DCount ("*"; "tblMaster"; "Rank = 'Dean' ")

Yes, is could be converted to a VBA function. Something like this:

Public Function CountRanks() As Integer
    Dim r As DAO.Recordset
    Dim sSQL As String
    Dim RC As Integer   ' RC = record count

    RC = 0

    sSQL = "SELECT Rank FROM tblMaster"
    sSQL = sSQL & " WHERE Rank = 'Dean' OR Rank = 'colonel' OR Rank = 'provider' OR"
    sSQL = sSQL & " Rank = 'pioneer' OR Rank = 'captain' OR Rank = 'first lieutenant' OR Rank = 'lieutenant'"

    Set r = CurrentDb.OpenRecordset(sSQL)
    If Not (r.BOF And r.EOF) Then
        r.MoveLast
        RC = r.RecordCount
    End If

    r.Close
    Set r = Nothing

    CountRanks = RC

End Function

In the control source of the text box:
=CountRanks()

[azhar2006]

Thank you very much Steve
I will experiment
Yes works very well on the report

[azhar2006]

Thank you very much Steve Steve Steve Steve
I've been wonderful to work successfully
sSQL = "SELECT Rank FROM Query1"