Thread: Expression assistance

1. Registered User
Join Date
Nov 2012
Posts
79

Unanswered: Expression assistance

Hello!

I've a table with information.
I've a report that lists all of that information.
There are two table fields named Crewmember 1 and Crewmember 2. Each column can have the same name, however the same name cannot be within the same row. For instance:

Crewmember 1 Crewmember 2
Happyman Sadman
Sadman Happyman
Happyman Sadman
Sadman Happyman

In the Report Footer I have a textbox and I wish to have a total of how many occurances of Happyman in Crewmember1 + Crewmember2.

So I would have a total of 4 in the Happyman total and 4 in the Sadman total.

I've tried Sum(Abs([Crewmember1] OR [Crewmember2] = "Happyman"))
Count([Crewmember1] OR [Crewmember2] = "Happyman"))
Sum(Abs([Crewmember1] AND [Crewmember2] = "Happyman"))
Sum(Abs([Crewmember1] + [Crewmember2] = "Happyman"))
and so and and so forth and have yet to come up with the correct number.

Any ideas?

2. Moderator
Join Date
Mar 2009
Posts
5,442
Provided Answers: 15
The SQL expression of what you're looking for is:
Code:
```SELECT Count(*) AS TotalHappyMan
FROM Table1
WHERE ((Table1.Crewmember1 = 'HappyMan') OR
(Table1.Crewmember2 = 'HappyMan')
);```
In a Domain function it would be:
Code:
`TotalHappyMan = DCount("*", "Table1", "Crewmember1 ='HappyMan' OR Crewmember2 = 'HappyMan'")`

3. Registered User
Join Date
Nov 2012
Posts
79
Aha! DCount! Thanks buddy!

4. Moderator
Join Date
Mar 2009
Posts
5,442
Provided Answers: 15
You're welcome!

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•