locked
How to use an "OR" in an Access function RRS feed

  • Question

  • Has anyone tried to use something akin to the OR function from Excel, in ACCESS? I am trying to derive a new field in a query by summing the field if another field has either "1027" or "BLANK" (BLANK spelled out in the field) in it. The field I am trying to sum is a numeric field.
    Friday, May 27, 2011 4:28 PM

Answers

  • Try it this way --

    Pending #:Sum(IIF([TBL_Original_Set_Org].[WF_STATUS] ="1027" OR [TBL_Original_Set_Org].[WF_STATUS] = "BLANK", [TBL_Original_Set_Org].[CountOfACCT_ID] ,0))

    • Marked as answer by DonFox Tuesday, May 31, 2011 9:09 PM
    Saturday, May 28, 2011 1:17 AM

All replies

  • Or has both bitwise and logical comparison functionality in Access as well (actually it's a VBA thing, so any program that uses VBA will have them).

    Set some criteria for your query like so:

    ([Field] = "1027") OR ([Field] = "BLANK")

    (any particular reason to use "BLANK" rather than Null for the table storage?)

    hth


    Access Wiki: http://www.utteraccess.com/wiki
    Friday, May 27, 2011 4:54 PM
  • Hi,

    it's not absolutely clear what you are trying to do. I see  2 cases.

    1. You have a table like this

    ProjectID ProjectType Cost   AdditionalCost

    1            1027          $100  $10

    2            BLANK         $50   $5

    3            1111          $300  $100

    and you want to calculate a total cost where an additional cost should be added only if a ProjectType is either 1027 or BLANK. Then you should add a new field 

    TotalCost: Iif([ProjectType]="1027" Or [ProjectType]="BLANK", [Cost] + [AdditionalCost], [Cost])

    And the result will be:

     

    ProjectID ProjectType Cost   AdditionalCost  TotalCost

    1            1027          $100  $10                 $110

    2            BLANK         $50   $5                   $55

    3            1111          $300  $100               $300

     

     

    2. You want to sum the total cost for all the projects with a particular ProjectType.

    a. Only a sum

     

    SELECT Sum(MyTable.Cost) AS SumOfCost
    FROM MyTable
    WHERE (((MyTable.ProjectType)="1027" Or (MyTable.ProjectType)="BLANK"));
    

     

    b. Sums which are grouped by ProjectType:

     

    SELECT MyTable.ProjectType, Sum(MyTable.Cost) AS SumOfCost
    FROM MyTable
    GROUP BY MyTable.ProjectType
    HAVING (((MyTable.ProjectType)="1027" Or (MyTable.ProjectType)="BLANK"));
    

     

    c. Calculate a TotalCost but only show records with particular ProjectType:

     

    SELECT MyTable.ProjectType, Sum(MyTable.Cost) AS SumOfCost, Sum(MyTable.AdditionalCost) AS SumOfAdditionalCost, Sum([Cost]+[AdditionalCost]) AS TotalCost
    FROM MyTable
    GROUP BY MyTable.ProjectType
    HAVING (((MyTable.ProjectType)="1027" Or (MyTable.ProjectType)="BLANK"));
    
    d, e, f... If you have a different situation just describe it. :)

     

    Friday, May 27, 2011 5:06 PM
  • Hi Jack,

    Thank you for the response. In response to your question about BLANK vs NULL, we were concerned that there might have been spaces within the column throwing off any tupe of reporting based on a NULL in the column.  That is a good question.

     

    Still going through your and Andey's response.  Andrey had helped me on another occasion.

    Don

    Friday, May 27, 2011 5:24 PM
  • Concerning spaces, you can (at table level) set the field property Allow Zero-Length String to false.  If you peruse Allen Browne's website (http://www.allenbrowne.com/tips.html) you'll find a procedure to run through every table and set this property to false for you.  Quite handy, I run it (and his similar one to set subdatasheets to none) every time I add tables/fields to the backend.  The Trim() function can be used to remove leading and trailing spaces, so if there is by chance a field that has nothing by spaces, it will result in a ZLS, which will then error if you disallow them.

    Just a thought - null handling can be very critical in normalization, performance and general design, so I'd definately recommend working "with" the flow on that.  The chances someone might type "BLNK" or something instead of BLANK are just as good (if not better) than a space in a field.  If you're going to tell me the blanks are handled programmatically, feel free to handle nulls programmatically as well! <g> (null's do take some getting used to, but sooner or later you'll be forced into it, so might as well do yourself a favor and start now)

    Good luck with the project


    Access Wiki: http://www.utteraccess.com/wiki
    Friday, May 27, 2011 5:39 PM
  • Hi Andrey,

    Thank you for your reply. What I am trying to do is to create a field on the fly such that if the two values ("1027","BLANK") are found, then sum the amount from another field.  Here is what the statement looks like when I am just trying to work with one value.

    Approved $: Sum(IIf([TBL_Original_Set_Org]![WF_STATUS]="1051",
    ([TBL_Original_Set_Org]![SumOfAsset]),0))

    The field name will be "Approved $" and is created by checking to see if the WF_STATUS value is "1051". I put this in the "Build" part of the field.  Access would not let me use your formula even when I tried to surround with parens.

    Pending #:Sum((IIF( [TBL_Original_Set_Org]![WF_STATUS] ="1027"
    OR [TBL_Original_Set_Org]![WF_STATUS] = "BLANK"),
    [TBL_Original_Set_Org]![CountOfACCT_ID] ,0)

    BTW I was summing on a field that was a COUNT of another field. Here is how it would look for the Approved # field:

    Approved #: Sum(IIf([TBL_Original_Set_Org]![WF_STATUS]="1051",([TBL_Original_Set_Org]![CountOfACCT_ID]),0))

    Not sure if there is a solution to this.

    Thanks for your help!

    Friday, May 27, 2011 10:12 PM
  • Try it this way --

    Pending #:Sum(IIF([TBL_Original_Set_Org].[WF_STATUS] ="1027" OR [TBL_Original_Set_Org].[WF_STATUS] = "BLANK", [TBL_Original_Set_Org].[CountOfACCT_ID] ,0))

    • Marked as answer by DonFox Tuesday, May 31, 2011 9:09 PM
    Saturday, May 28, 2011 1:17 AM
  • I don't see why if your Sum() does anything since the IIF() returns a single value.


    Monday, May 30, 2011 2:21 AM
  • I think you can firstly create a query which determines a status Approved/Pending, then use this query to sum up the results and group by Status.

    Sorry, can't imagine your structure ans as result, I got confused with all these fields in a real example.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Monday, May 30, 2011 9:37 AM
  • Thank you Karl, (and to everyone else who submitted a response),

    I thought I had tried coding it the way that you had suggested but I must have missed something. Your response worked!

    Don

    Tuesday, May 31, 2011 9:11 PM