Results 1 to 5 of 5
  1. #1
    Slink is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    3

    query field value = null based on value of other field

    Hi all. New to the forum. I have a query with many fields. 2 of the fields are REGION and COUNTRY. REGION is limited to the values A, B, C, D, E. Country can be any country within those regions. I want both fields included in my query results. However, If Region = B then I want the COUNTRY value to be blank.

    I would love to be able to set it up using simple query criteria. I tried entering this formula into the COUNTRY field criteria iif([REGION]<>"B",[COUNTRY],"") and iif([REGION]<>"B",[COUNTRY],NULL). Both of them deleted all the records for REGION B from the results, which I don't want.

    I also tried to create 2 separate queries, one for A,C,D,E and one for B in which B did not include the field COUNTRY and then join them with a UNION query, which failed due to inconsistent fields.

    My last option, that I can think of, is to use a make table query and then use an update query to set the value of COUNTRY = "" if REGION = B. I am reluctant to do this because of the manual nature of it and worry it could get forgotten during an update (I create and use many macros in Excel, but have never attempted one in Access).



    Any help would be appreciated.
    Thanks

  2. #2
    Slink is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    3
    I figured it out ... I put the formula I listed ... iif([REGION]<>"B",[COUNTRY],"") in the field name area instead of the criteria. CountryX:iif([REGION]<>"B",[COUNTRY],""). Thanks

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Are you looking to physically update your underlying table so that the Country field will be permanently blanked out if the Region field is "B",
    or are you just looking for a calculated field in your query to return a value of blank if Region is "B", or the Country value otherwise (so not permanently update the underlying table record)?

  4. #4
    Slink is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    3
    Hey Joe, Thanks for the reply. I am NOT looking to physically update the source table. I am building a query that will feed an Excel Pivot table. I don't want to lose the data from the source table, but I don't want the Excel users to be able to see the country if the record is in REGION B.

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Then calculated field is the way to go, and it looks like you already have it figured out!

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

Similar Threads

  1. Replies: 1
    Last Post: 11-14-2016, 10:36 AM
  2. Replies: 1
    Last Post: 08-31-2012, 01:56 PM
  3. If/Then Null based on Null Value of Another Field
    By vbpeterson in forum Queries
    Replies: 11
    Last Post: 06-05-2012, 05:00 PM
  4. Query Help - Show field if another field is null
    By lukekonrad in forum Access
    Replies: 1
    Last Post: 03-05-2012, 04:02 PM
  5. Replies: 1
    Last Post: 08-31-2011, 04:03 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