Results 1 to 4 of 4
  1. #1
    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?
    Version: Access 2010

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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'")
    Have a nice day!

  3. #3
    Join Date
    Nov 2012
    Posts
    79
    Aha! DCount! Thanks buddy!
    Version: Access 2010

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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