Results 1 to 3 of 3
  1. #1
    Steven Seagal is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2012
    Posts
    20

    Having trouble using update query to update fields within the same field using logic.

    I have a list of over 100 projects in a database and some of them are quick hits so the idea I have in mind is that on a form, I make something as [In use] using a checkbox. This data lives in a table called [ProjectList] If this box is checked off, then [Test Progress] for this project should default to 100% (and 0 otherwise) - the field should be edited.


    Ultimately all of this will be printed on an excel sheet to highlight project statuses.


    Now, the fields [In Use] and [Test Progress] are on a form where if you input data, it should go on the respective fields in the table [project list]. On the forms, I used in the field for [test progress], iif([in use] = -1 , "100", "0") and it worked PERFECTLY for all the projects marked as "in Use"

    But it wasn't being sent to the table [ProjectList].

    so I have this update query that reads something like this:

    Field: [Test Progress]
    Table: [Project List]
    Update: Iif([ProjectList.in use]=-1,"100","0")

    However, it is NOT going into the table. Am I missing a really simple step here? I manually 'ran' the query and had it update, but that didn't work either.

    Let me know if there's anything I can do to make this work. Thanks!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why even save this TestProgress value into table - can calculate whenever needed. Saving calculated data that is dependent on value in other fields is often bad idea. A principle of relational database is save raw data, do calcs on report.
    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
    Steven Seagal is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2012
    Posts
    20
    thanks! your answer gave me the intuition to come up with a better idea compared to what I was doing earlier!

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

Similar Threads

  1. Replies: 2
    Last Post: 10-02-2012, 10:29 PM
  2. Replies: 9
    Last Post: 04-13-2012, 10:10 AM
  3. Replies: 1
    Last Post: 01-26-2012, 03:58 PM
  4. Replies: 1
    Last Post: 02-03-2010, 09:17 PM
  5. Replies: 1
    Last Post: 08-04-2008, 03:30 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