Results 1 to 4 of 4
  1. #1
    dgrimes is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    11

    SQL language issue

    I know there is a simple solution to this but I cannot figure this out. I need to create a new field in a query that shows data for one field, but if it is null, shows another field, and if THAT field is null, shows a 3rd field. I can get this to work for 2 of them by adding the language below in the field name:



    NewFieldName: IIf(tblemployees2.COMPANY is not null,tblemployees2.COMPANY,tblconsultants.COMPANY)

    As you can see I have 2 tables with identical field names. This string compares one field with another and if the first is null, uses data from the 2nd.

    I tried adding the 3rd field in the same fashion but it will not work. I keep getting errors everywhere.

    Please help!

    Thank you!

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You didn't post some critical info.

    What is the SQL of the query?
    What is the tableName.fieldName of the 3rd field?

    You might try:
    Code:
    NewFieldName: IIf(tblemployees2.COMPANY is not null,tblemployees2.COMPANY,IIf(tblconsultants.COMPANY is not null,tblconsultants.COMPANY,tblTable3.FieldName))
    Change the RED text.....

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    alternatively you can use the nz function

    NewFieldName: nz(tblemployees2.COMPANY,nz(tblconsultants.COMPANY,tblTable3.FieldName))

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Ajax,
    Nice!
    So much easier to read. Never crossed my mind to do that. I am definitely going to remember that.

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

Similar Threads

  1. Code language
    By bhatti in forum Programming
    Replies: 10
    Last Post: 09-18-2015, 10:25 AM
  2. Greek Language Help
    By Potatohead in forum Access
    Replies: 3
    Last Post: 01-30-2013, 07:09 PM
  3. Language Issue in Access Query
    By somnath in forum Queries
    Replies: 2
    Last Post: 09-07-2011, 10:31 AM
  4. Language in Access
    By Bill Casanova in forum Access
    Replies: 3
    Last Post: 05-23-2011, 02:00 PM
  5. can't find language DLL
    By tequila in forum Access
    Replies: 0
    Last Post: 05-01-2008, 09:31 PM

Tags for this Thread

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