Results 1 to 5 of 5
  1. #1
    bpalmereng is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    7

    Iff nested query


    I'm trying to create a field for current location that will change the inventory item to the new location based on what type of transaction is made. The three locations are Warehouse, Employee, Removed. The 4 types of transactions are Issued, Returned, Addition, Removed

    Below is the code that I'm trying to use, but its returning a error code of invalid syntax "you have entered an operand without and operator"

    Any help would be appreciated.



    Code:
    Current Location: IIf([Transaction Types].[Add/Remove]="Addition" Or [Transaction Types].[Add/Remove]="Returned","Warehouse",IIf(Transaction Types].[Add/Remove]="Issued","Employee","Removed"))

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you dont really want to use IIFs in a query like this,
    instead use a lookup table to convert the values. Youd join the lookup table to the field and replace it with another field in the lookup table.
    or
    for complex lookups, make a custom function.
    the fuction would replace the values.

    dont decipher nested IIFs.

  3. #3
    bpalmereng is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    7
    Thanks for the reply, I'm not sure exactly what you mean or how to go about doing that. the Transaction type values are from the transaction type table, and the locations that need to be changed are in the Inventory table.
    I'm still fairly new to access

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You are missing an opening [ in the second Iif:

    IIf([Transaction Types]

  5. #5
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Create a table with two fields transtype and currLocation

    Addition warehouse
    Returned warehouse
    Issued employee

    Then join transactiontypes to transtype and drag the currLocation field.

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

Similar Threads

  1. Nested Query Question
    By mindbender in forum Queries
    Replies: 2
    Last Post: 04-08-2014, 12:12 PM
  2. Nested And/Or query
    By kagoodwin13 in forum Queries
    Replies: 2
    Last Post: 05-10-2012, 12:10 PM
  3. Nested SQL Query
    By springboardjg in forum Queries
    Replies: 5
    Last Post: 05-08-2011, 05:01 PM
  4. Another Nested IIF Query problem
    By Brian Collins in forum Queries
    Replies: 8
    Last Post: 10-22-2010, 10:12 AM
  5. Nested IIf query will not work
    By ddog171 in forum Queries
    Replies: 1
    Last Post: 06-20-2006, 02: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