Syntax Error: Dlookup with Mutiple Criteria 1

[CharlieT302]

Hi,

I have the following statement which is producing a Syntax Error (missing variable).

The statement has the following parameters:
*Provider Number = Number field
*Conttype1,Conttype2,Conttype3 are text fields.

The formula checks to see if any of the three text fields (above) already contains the entry of Primary Contact for the active provider; only one is allowed.

I am unsure about the syntax. Any help?

if DCount("Provider_Number", "tbl_OAA_Provider_Contact_Info", "[PROVIDER_NUMBER] = & Me.PROVIDER_NUMBER" & " And Conttype1='Primary Contact' or Conttype2='Primary Contact' or Conttype3='Primary Contact'")>=1 then...

 

[jrbarnett]

Try

If DCount("Provider_Number", "tbl_OAA_Provider_Contact_Info", "[PROVIDER_NUMBER] =" & Me.PROVIDER_NUMBER & " And (Conttype1='Primary Contact' or Conttype2='Primary Contact' or Conttype3='Primary Contact')")>=1

Note the removal of the double quote mark after the provider_number field. Even with my additions to the brackets it still will allow multiple fields to have the "Primary Contact" value in the table unless your data entry system prevents this.

John

 

[CharlieT302]

Jrbarnett

Thank you very much. That was very helpful and appears to be working.

 

[PHV]

Another way:

If DCount("Provider_Number", "tbl_OAA_Provider_Contact_Info", "[PROVIDER_NUMBER]=" & Me.PROVIDER_NUMBER & " AND 'Primary Contact' In (Conttype1,Conttype2,Conttype3)")>=1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886