Results 1 to 7 of 7

using criteria in calculated filed showing no records

  1. #1
    iranbir is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    12

    using criteria in calculated filed showing no records

    hi,

    I have to tables 1 showing advance taken and other showing amount paid. I have made query using both the tables to arrive at amount outstanding in a calculated field. when I put criteria as >0, it opens up the parameter screen.

    kindly assist,

    Thanks

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    12,534
    If you want focused responses, you'll have to provide more information. Readers do not know you, your environment, your database skills, your business nor any of its processes. I suggest you start at the 30,000 ft level and then gradually add detail. Readers need some context to understand "what you have" and "what you are trying to accomplish". You appear to be focused on HOW to do something, and it is that something we know nothing about.

    Good luck with your project.

  3. #3
    iranbir is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    12
    hi

    I have changed the data now with the sample attached herewith. I am facing issue with the qry_DD_Outstanding as I am unable to get the result of all the outstanding drawdowns.

    I have table tbl_Drawdown which is having data of drawdown taken and tbl_BankPayout which is having data for the payments against the same.

    tbl_BankPayout is having a field "status" which has 3 criteria "pending, partial-paid, settled", I am trying to execute the query so that all the drawdown which are either partial-paid or pending will be shown.


    Thanks
    Attached Files Attached Files

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    12,534
    Try this, but I only see 2 records.
    Code:
    SELECT tbl_BankPayout.BankName
    , tbl_BankPayout.AmtPaid
    , tbl_Drawdown.TotAmt
    , tbl_BankPayout.Status
    FROM tbl_Drawdown INNER JOIN tbl_BankPayout ON 
    tbl_Drawdown.DDNO = tbl_BankPayout.DDNO
    WHERE (((tbl_BankPayout.Status)<>"Settled"));
    Totals and calculations in general are done in queries when required - they are not normally stored in tables.

  5. #5
    iranbir is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    12
    hi,

    thanks for the reply.

    however, the problem I am facing is how to get the records from tbl_Drawdown where tbl_Drawdown.TotPay-tbl_BankPayout.AmtPaid>0

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,125
    Isn't that what the <>"Settled" accomplishes?

    However, that status really should be calculated when needed, not saved in table.

    Alternatively,

    SELECT tbl_BankPayout.BankName, tbl_BankPayout.AmtPaid, tbl_Drawdown.TotPay, tbl_Drawdown.TotAmt, [TotPay]-Nz([AmtPaid],0) AS Due
    FROM tbl_Drawdown INNER JOIN tbl_BankPayout ON tbl_Drawdown.DDNO = tbl_BankPayout.DDNO
    WHERE ((([TotPay]-Nz([AmtPaid],0))>0));


    Advise not to use field caption property in table. I find it annoying not seeing the real field name in datasheet when working with tables and queries. The caption doesn't show in the field list. Just complicates things.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  7. #7
    iranbir is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    12
    hi the issue is not yet resolved, it is showing only records from tbl_BankPayout whereas the result should includes all records from tbl_Drawdown and Amt outstanding should be shown as [TotPay]-([AmtPaid]

    Thanks

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

Similar Threads

  1. Replies: 5
    Last Post: 02-12-2016, 05:46 PM
  2. Replies: 5
    Last Post: 03-16-2015, 06:06 AM
  3. Replies: 3
    Last Post: 10-26-2014, 05:35 PM
  4. Calculated Fields Not Showing Up
    By egorzik in forum Access
    Replies: 2
    Last Post: 02-07-2014, 02:44 PM
  5. Calculated filed in Access 2007
    By fekrinejat in forum Access
    Replies: 6
    Last Post: 02-05-2013, 06:54 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
  •  
Tech Forums: Microsoft Office Forums