Dcount IIF revision 2

[ciarra41]

Hi,

I currently use this to determine if I have more than one of the same value per rows.

CNT: IIf(IsNull([Custlogocnt]),"",IIf(DCount("Custlogocnt ","tbltickets","[ Custlogocnt] = '" & [Custlogocnt & "'")>1,"MANY",""))

But it’s only looking at text values. How could this be revise to look at number values or even both regardless if number or text.

 

[dhookom]

Your values should be stored as either number or text, not both. If Custlogocnt is numeric, try:

CNT: IIf(IsNull([Custlogocnt]),"",IIf(DCount("Custlogocnt ","tbltickets","[Custlogocnt] = " & [Custlogocnt])>1,"MANY",""))

I'm not sure why you didn't copy and paste your working expression. There were at least 2 errors in your expression.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ciarra41]

Thanks on the original response but could I revise this another way?

Now I need it to show how many times, exp...

IIf(IsNull([Custlogocnt]),"",IIf(DCount("Custlogocnt ","tbltickets","[Custlogocnt] = " & [Custlogocnt])>1,"MANY",""))

Instead on "Many" it will count the number of times I have the value.

 

[PHV]

You wanted simply this ?
IIf(IsNull([Custlogocnt]),0,DCount("*","tbltickets","Custlogocnt=" & [Custlogocnt]))

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

 

[ciarra41]

Using the same example above from (PHV) example how I can do this with a text value. The example (PHV) provided only works for number values. I need to do the same thing for text values too.

Regards
C..

 

[lespaul]

add string delimiters:

IIf(IsNull([Custlogocnt]),0,DCount("*","tbltickets",
"Custlogocnt=[COLOR=red]'[/color]" & [Custlogocnt] & "[COLOR=red]'[/color]"))

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins