Results 1 to 5 of 5
  1. #1
    gwenny2007 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2018
    Posts
    2

    If field meets this criteria, update this other field to...

    I am wanting to update my query to change a certain field's data depending on if it meets the criteria in another field. For example:

    If [TAFS_at_ETS] is >=10, change [term] to "3"

    I currently have a separate query that shows which records need to have the field [term] changed to 3, but I would prefer it be updated in the main query in order to do future cross tab queries for terms 1 through 3.

    Any suggestions are greatly appreciated!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    do you mean update your query or update a table?

  3. #3
    gwenny2007 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2018
    Posts
    2
    Quote Originally Posted by Ajax View Post
    do you mean update your query or update a table?
    I would like the update to be present in the query results.

    I have a Personnel table which includes all personnel (this is pulled from a different system and imported into Access) and I have an Eligible query created from the Personnel table to show those eligible for reenlistment based off specific criteria.

    The issue is, I need to account for term conversions. Personnel within the Eligible query who have 10 or more [TAFS_at_ETS] need to be changed from the term the system (outside of Access) shows them as and changed to a term “3”.

    I could always do a Make Table query from the Eligible query if need be.

    Whatever works! Lol

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I would prefer it be updated in the main query
    A select query ("main query"?) cannot update, append or delete. If you need this, you will also need an update query, which will only update the records that satisfy criteria that you provide. If none, every record in the fields you choose to update on will be updated. This query can show what fields would be updated by switching to datasheet view, in which case it won't actually run. However, if the fields to be updated are null, you won't see anything as this view shows what records will be updated, not what the update will be.

    A make table query is not the way to go.

    EDIT: you could try switching your select query to an update query. Regardless, I'll reiterate what's in my signature - play around with copies of your tables/queries until you're sure all is good.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    as a calculated value in a query you could use something like


    RevisedTerm:iIf([TAFS_at_ETS] >=10, "3",[term])

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

Similar Threads

  1. Replies: 1
    Last Post: 07-19-2017, 12:21 PM
  2. Replies: 11
    Last Post: 01-31-2017, 04:08 PM
  3. .EOF where it meets criteria
    By Thompyt in forum Programming
    Replies: 23
    Last Post: 09-16-2016, 04:04 PM
  4. Replies: 1
    Last Post: 05-13-2015, 02:17 AM
  5. Replies: 7
    Last Post: 12-12-2014, 11:58 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