Results 1 to 5 of 5
  1. #1
    windme is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jun 2017
    Posts
    2

    Sum Of A Report Column If Certain Text Is In Another Column

    Hello everyone, my first post in the Access forum. Using Access 2013. Only been using Access for the last month.



    I have a Payment Receipt report that contains a column called Type which would be either Cash or Check and another column called Total , which is the cost of the product.
    Right now I just have a sum in the footer that sums up all the monies in the Total column. I want to have a separate total for both Cash and Check and then have a grand total of the monies received. So the totals in the footer would be: if Type = Cash , it would then sum the Totals of Cash, and if Check it would sum the totals of Check.
    I found a formula on the web,
    Code:
    sum(Iif([Type]="Cash",[Total],0)
    but received this error Syntax Error in Query Expression Firstsum(Iif([Type]="Cash",[Total],0).
    Appreciate any help or suggestions,
    Thanks
    Mike
    Click image for larger version. 

Name:	snytax error.PNG 
Views:	10 
Size:	4.8 KB 
ID:	29770
    Attached Thumbnails Attached Thumbnails Error Message in design field.PNG  

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Given your picture, you need = at the beginning and another ) at the end.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum......


    First, "Type" is a reserved word in Access and shouldn't be used for an object name. Plus it is not very descriptive. How about "PaymentType" or "PymtType" or "PaidType"???


    Looking at the error message, why do you have "First(" in the formula?
    That is not like the formula you found.



    Maybe this example will help you.....


    Good luck......
    Attached Files Attached Files

  4. #4
    windme is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jun 2017
    Posts
    2
    Hi Steve, thanks for the reply. The actual name of the field is "tender type" I changed the field label to Type, I messed up putting the label in the formula.
    I put the formula you made up for me but I receive the error Data Type Mismatch in Criteria Expression. When I made up the table I made the Tender Type a lookup to the table called Tender Type. After I made the lookup it changed the data type from short text to number, is that where the problem is?

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Probably.... I never use Lookup FIELDS in tables. See http://access.mvps.org/access/lookupfields.htm
    (also see http://access.mvps.org/access/tencommandments.htm)


    Some suggestions:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not use Look up FIELDS, Multi-value fields (MVF) or Calculated fields in tables.
    Do not begin object names with a number.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Create a 4 Column Report from a 2 column data
    By trident in forum Reports
    Replies: 4
    Last Post: 01-19-2017, 11:50 AM
  2. Replies: 4
    Last Post: 09-05-2014, 02:00 PM
  3. Replies: 15
    Last Post: 07-10-2013, 01:27 PM
  4. Replies: 1
    Last Post: 01-10-2012, 12:06 AM
  5. Divide a column by another Column in a Report?
    By taimysho0 in forum Reports
    Replies: 2
    Last Post: 01-06-2012, 06:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums