Results 1 to 5 of 5
  1. #1
    louise is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    112

    Query IFF statement that leaves field unchange if condition returns as "false"

    Hello,


    I want to make an IIF statement in a query that evaluates an existing field and, if it is null/blank, makes it equal to another field, but if it is already filled in (I/e., not null/blank) leaves it as it is.

    so,
    ShortTitle: IIF(Is Null, [...equals LongTitle], [equals itself])

    Later, in a form, I want to edit whatever is in that field.

    Can you give me some pointers?

    Thanks

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Use the NZ function. See: http://www.techonthenet.com/access/f...dvanced/nz.php

    Note that you cannot edit a calculated field in a Form, you can only edit the underlying field.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,948
    are you sure you cannot edit the control source on the fly? Not that I can think of a situation where that would be a good idea.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,471
    I agree with Joe; he is 100% correct.

    Think about it. If the field in the query is a calculation, where is it stored? Not in a table. If it is not stored, how will you be able to edit the value later? You can only edit data stored in tables or unbound controls on a form.
    Since you have created a (virtual) field ("ShortTitle") in a query, there is no way to edit the value later (unless you edit the existing field that the calculation is based on).
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,948
    Note that you cannot edit a calculated field in a Form, you can only edit the underlying field.
    Ok, I think I misinterpreted the meaning. I now realize the user wanted to edit data that was being calculated in a query field. I was thinking more along the lines of changing a calculation in an unbound control by modifying the expression used in the control source.

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

Similar Threads

  1. Replies: 2
    Last Post: 01-03-2014, 09:35 AM
  2. Replies: 3
    Last Post: 04-22-2013, 06:08 AM
  3. oie.document.all.item("AHHHH").value = False
    By redbull in forum Programming
    Replies: 4
    Last Post: 12-13-2012, 01:54 PM
  4. Replies: 8
    Last Post: 10-24-2012, 12:47 PM
  5. SELECT "False" if any records are false?
    By Azurewrath in forum Queries
    Replies: 6
    Last Post: 12-21-2011, 03:36 PM

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