Calculated field with Dcount & "And"[Control]

[Eljefegeneo]

I am having a problem combining the DCount function with & "And" [ControlName] either equal or not equal to something. I can get it to work with just the DCount, but when I combine it with the "And", all I get is the #Name? error:

=IIf(DCount("[ClientLangID]","qryLanguages","[ClientID] = " & [ClientID])=1 And [Language]="English","No",IIf(DCount("[ClientLangID]","qryLanguages","[ClientID] = " & [ClientID])>1 And [Language]<>"English","Yes","Both"))
Please see the attached sample DB.
I would like to have a calculated field so that:
1. If there is only one language and it is "English", the answer would be "no"
2. If there is one or more language and one of them is not "English", the answer would be "yes"
3. Else "both"

 

[CJ_London]

This works, but not sure if I have selected the right field for lanquage

=IIf(DCount("[ClientLangID]","qryLanguages","[ClientID] = " & clientid)=1 And Combo21.column(2)="English","No",IIf(DCount("[ClientLangID]","qryLanguages","[ClientID] = " & ClientID)>1 And Combo21.column(2)<>"English","Yes","Both"))

 

[Eljefegeneo]

Thank you for your prompt reply. I doesn't seem to work. All I get is the "Both" response to the calculation for any of the records. I tried changing the column number and still the same. I don't understand why you would reference the combo box since it is unbound, but then my knowledge is limited.

I also tried using Dlookup in combination with the DCount and I always get the same #name? error message.

 

[Eljefegeneo]

I even tried this to no avail. Still get the #Name? error.
=IIf(DCount("[ClientLangID]","qryLanguages","[ClientID] = " & ClientId)=1 And Forms![frmNewMain]![frmNewTblClientLanguages].Form!Language="English","No",IIf(DCount("[ClientLangID]","qryLanguages","[ClientID] = " & [ClientId])>1 And Forms![frmNewMain]![frmNewTblClientLanguages].Form![Language]="English"<>"English","Yes","Both"))

 

[JHB]

In which cases do you think "Both" would be shown, (I can't find any, because you say if one (or more) language not = English, it should be Yes)?

=IIf(DCount("[ClientLangID]";"qryLanguages";"[ClientID]=" & [ClientId] & " And [Language]<>'English'")>=1;"YES";IIf(DCount("[ClientLangID]";"qryLanguages";"[ClientID]=" & [ClientId] & " And [Language]='English'")=1;"NO";"Both"))

 

[Eljefegeneo]

The control name (in the old DOS db) is "Has foreign language?" So, if the Language is only English, the answer is no. If the language(s) are one or more and they do not include English, then the answer is "Yes", the ELSE is "Both", both English and one or more languages that are not English.

I tried it, and now all I get is "Yes". I assume you meant to use commas and not semi-colons. Kept coming up invalid syntax.

I do appreciate you trying to solve this for me.

 

[JHB]

Ok now came the last condition in your description.
Now it should work.

 

[Eljefegeneo]

Thank you! I had worked on this for days, even used a combination of DCount and DLookup. I thought I had something similar to what you has as the final code, but couldn't get it to work. I think I now understand how to use two criteria in the DCount function. For that I thank you too.

 

[Eljefegeneo]

And thank you CJ London too.