Results 1 to 7 of 7
  1. #1
    newuserthatneedsassistanc is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    66

    How to subtract two columns and get total in report view


    Click image for larger version. 

Name:	Captur34523523e.PNG 
Views:	13 
Size:	28.9 KB 
ID:	29680


    I want to subtract HOURSLABOR - SumOfQTY to get a new column with the remaining for each job

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Don't understand the issue. Create calculated field in query that references the other fields or create another textbox and build an expression in its ControlSource property that references fields or textboxes.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    newuserthatneedsassistanc is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    66
    This is the first time using this so just bare with me - yeah?

    How do you use expression builder, i'm getting blanks.


    Click image for larger version. 

Name:	Captur2345235e.PNG 
Views:	12 
Size:	16.7 KB 
ID:	29681Click image for larger version. 

Name:	Captu412342134re.PNG 
Views:	12 
Size:	60.6 KB 
ID:	29682Click image for larger version. 

Name:	Captu2456re.PNG 
Views:	12 
Size:	28.1 KB 
ID:	29683

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Put that textbox in the Detail section. Leave its label in the Header.

    Could do the calc in query and then bind textbox to the calculated field.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    newuserthatneedsassistanc is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    66
    How would I do an if statement with the Hours Remaining expression I just built in this report?

    if HOURSLABOR - SumOfQTY < 0 , output 0

    However it only outputs zeros and not any of the other numbers


    Click image for larger version. 

Name:	Captur25235e.PNG 
Views:	12 
Size:	27.9 KB 
ID:	29700


    Click image for larger version. 

Name:	Captu23542354re.PNG 
Views:	12 
Size:	21.4 KB 
ID:	29698Click image for larger version. 

Name:	Capt5ure.PNG 
Views:	12 
Size:	23.5 KB 
ID:	29699


    I want it to show 0 if it's negative in the Hours Remaining column

  6. #6
    newuserthatneedsassistanc is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    66
    =IIf([HOURSLABOR]-[SumOfQTY]<0,0,IIf([HOURSLABOR]-[SumOfQTY]>0,[HOURSLABOR]-[SumOfQTY]))

    ok this worked


    Now I need to figure out how to total this new column


    Would it be like this??
    =Sum IIf([HOURSLABOR]-[SumOfQTY]<0,0,IIf([HOURSLABOR]-[SumOfQTY]>0,[HOURSLABOR]-[SumOfQTY]))



    Yes

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Your expression ignores 0 value. Use: =IIf([HOURSLABOR]-[SumOfQTY]<=0, 0, [HOURSLABOR]-[SumOfQTY])

    The Sum would be:

    =Sum(IIf([HOURSLABOR]-[SumOfQTY]<=0, 0, [HOURSLABOR]-[SumOfQTY]))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-29-2017, 03:14 PM
  2. Replies: 4
    Last Post: 04-04-2017, 08:06 AM
  3. Replies: 7
    Last Post: 06-25-2015, 01:03 PM
  4. Replies: 4
    Last Post: 12-29-2014, 11:27 AM
  5. Total number of data in datasheet view
    By ashu.doc in forum Queries
    Replies: 2
    Last Post: 09-24-2012, 06:52 AM

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