IIf with Wildcards 2

[ba4crm]

In my table, there are group numbers that begin with DH, PD, and many other values. I am trying to come up with a way to collect all the group numbers that begin with DH or PD and count them separately from the rest of the groups.
The criteria in my query is:

IIf([GroupNumber] Like "DH*" & "PD*", 1, 0)

All I get is the 0. When I only have one value after Like (either "DH*" or "PD*" the query provides the 1).

Based on what I have read, the above should work. What am I doing wrong? The Group Number is a text field.

 

[r937]

IIf([GroupNumber] Like "DH*"
Or [GroupNumber] Like "PD*", 1, 0)



r937.com | rudy.ca

 

[JBinQLD]

ba4crm, just to clarify, r937 is spot on, you should give him a star (click on "Thank r937 for this valuable post" link above, we all like a pat on the back for the free help we give - makes it worthwhile).

But anyhoos, the & symbol serves to concatenate (ie, join together) strings. So your original expression was effectively looking for records beginning "DHPD" and it would seem there were none!

The irony was that when you described you used the appropriate word - OR!

Good luck with the rest of your project, JB

 

[dhookom]

You should also be able to use something like:

Abs(Left([GroupNumber],2) IN ("DH","PD"))

r937's solution should work very well, this is just another syntax to increase your knowledgebase.

Duane
Hook'D on Access
MS Access MVP

 

[r937]

i'm curious to know what effect you think the ABS function will have on strings

r937.com | rudy.ca

 

[dhookom]

[blue]Left([GroupNumber],2) IN ("DH","PD")[/blue] should return either True/-1 or False/0.

Duane
Hook'D on Access
MS Access MVP

 

[ba4crm]

r937 and dhookom - you two are awesome!
Tried both methods and they work like a charm!