Results 1 to 9 of 9
  1. #1
    Drew101 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    22

    Debit/Credit if then

    This is a small question as I am learning, in my query I have Expr1ebit Field. I just need the sql to write the if the statement

    In [Transaction_Activity].Amount>0 enter it in [Debit]

    I will be able to write the opposite

    What I am doing is related to separating my debits and credits in the Amount column from the [Transaction_Action] table. So in a query I am creating a debit field and a Credit Field. Is this the best way of doing this as well?

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Just realised this is only any use for a calculated field in a query


    Code:
    debit:Iif([transaction_activity].amount>0,[transaction_activity].amount, 0)
    
    credit:Iif([transaction_activity].amount<0,[transaction_activity].amount, 0)

  3. #3
    Drew101 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    22
    So far I have this and it is giving me an error for Enter Parameter Value For Debit

    [SELECT Date() AS [TRX DATE], [company code] & "-" & [Natural Account] & "-" & [Department] & "-" & [Intercompany/REL PTY] AS [Account Number], [Debit] AS Expr1
    FROM Transaction_Activity
    WHERE ((([Debit])=IIf([transaction_activity].[amount]>0,[transaction_activity].[amount],0)));
    ][code]

  4. #4
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Are you using the designer for queries

  5. #5
    Drew101 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    22
    I am in Design view
    Attached Thumbnails Attached Thumbnails Design View.JPG  

  6. #6
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Then my first code needs to be written in an empty field column (where you see expr1)

    The second one needs to go in the one next to it.

  7. #7
    Drew101 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    22
    I am in Design View here

    Click image for larger version. 

Name:	Design View 1.JPG 
Views:	13 
Size:	30.4 KB 
ID:	28893

  8. #8
    Drew101 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    22
    Perfect I will mark this solved

  9. #9
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    The whole line debit: iif(.......

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

Similar Threads

  1. Replies: 4
    Last Post: 01-21-2017, 12:08 PM
  2. debit and credit matching criteria
    By BlakeW in forum Queries
    Replies: 5
    Last Post: 02-12-2015, 08:02 PM
  3. Calculate Debit and Credit
    By naks in forum Access
    Replies: 11
    Last Post: 05-08-2013, 02:34 PM
  4. Replies: 5
    Last Post: 01-05-2013, 04:53 PM
  5. running sum for debit-credit in subform
    By roshanzarmal in forum Queries
    Replies: 1
    Last Post: 09-29-2011, 09:37 PM

Tags for this Thread

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