Results 1 to 2 of 2
  1. #1
    2tMonte is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2012
    Location
    Louisville, KY
    Posts
    13

    Update Balance AfterPayment Is Made

    Okay, I have read multiple posts on this type of update and have not found (or understood) any results that help me.



    My database is tracking billed accounts, their respective details, special APRs, payments, etc...

    I this form and subform (See Figure 1):

    Now, I am trying to reduce the account balance by the amount of the payment. My first step was to create a selective query identifying payments from today forward to avoid accounting for payments made in the past again which produces the following results (See Figure 2).

    So far so good. This gives me only the payments I have scheduled not including any payments made in the past. Now comes time to update my Biller table by the results from my PaymentHistory query (See Figure 3). I create an Update query like this (See Figure 4):

    Now this works except for one problem: It adds the payment to all of my Billers, not just the one in which the true payment applies. So, it subtracts $40.00 from Citibank's balance and $40.00 from other account balances. Thinking I know why this happened, I add a column to my Update query that attempts to match the BillerName in the query to the BillerName in my Biller table so it only updates the payments and dates that match a particular record like (See Figure 5):

    I then try to run the Update query and I receive the "Type Mismatch In Expression" error and the query has no effect on any data. I take a look at my query and Access has removed the "=" sign from my criteria column (See Figure 6).

    I added the SQL view for those of you that it makes sense to and the "=" is still there.

    Does anybody understand what is going on?
    Attached Files Attached Files

  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
    Saving calculated data, especially aggregate calc, is usually a bad idea. The balance is disconnected from the raw data. Ideally, balance on account would be calculated when needed. Sum of obligations minus sum of payments equals account balance.

    Review http://allenbrowne.com/AppInventory.html

    Using Now() as criteria doesn't make sense. Now() includes date and time parts.
    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: 15
    Last Post: 11-18-2013, 01:49 PM
  2. Balance Insert
    By shah1419 in forum Forms
    Replies: 2
    Last Post: 09-22-2013, 08:00 AM
  3. Replies: 2
    Last Post: 09-10-2013, 09:10 AM
  4. Replies: 7
    Last Post: 02-26-2013, 10:37 AM
  5. I want MSN appear when I am out of balance
    By miziri in forum Programming
    Replies: 4
    Last Post: 06-20-2010, 02:53 AM

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