Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Union Query - united in wrong order (Access2000-03)

    I had inherrited a query.

    IIf([reg/temp]="R","Regular",IIf([tbl_1]![Grade]="IC","Ind Contractor",IIf([reg]="t",IIf([long descr]="RH","Contract",
    IIf([reg]="t",IIf([long descr]="PT" And [tbl_1]![TYPE]<>"Home","Temp","Don't Know"))))))
    _______________________________________________

    It gets in union with another query

    IIf([tbl_1]![type] Is Null,"Office",IIf([TBL]![code]="Office","Office",IIf([tbl_1]![code]="Home","Home or Client",
    IIf([tbl_1]![code]="Client","Home or Client","don't know"))))


    When executed - I need to have 5 columns with names
    Regular
    Contract
    Home or Client
    Temp
    Ind Contractor

    I am assuming because of these queries are not in sync column-wise - wrong numbers getting into wrong columns.
    When I am trying to rearrange order of the columns it tells me - wrong number of arguments.

    Please, help me to get the result as it has to be 5 columns in order I had put.
    If possible, please.

    Thanks,T

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Union Query - united in wrong order (Access2000-03)

    Change the SQL of the union query to select the fields from each of the queries in the desired order. Something like

    SELECT Field1, Field3, Field4, Field2, Field5
    FROM Query1
    UNION SELECT FieldC, FieldE, FieldA, FieldD, FieldB
    FROM Query2

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query - united in wrong order (Access2000-03)

    I had tried to change forst quesry first to make correct order
    Regular
    Contract
    Home or Client
    Temp
    Ind Contractor

    But I do not understand these parts
    IIf([reg]="t",IIf([long descr]="RH","Contract",IIf([reg]="t",IIf([long descr]="PT" And [tbl_1]![TYPE]<>"Home","Temp","Don't Know"

    Logically it is one IIf([reg]="t",IIf([long descr]="RH","Contract"
    and another one IIf([long descr]="PT" And [tbl_1]![TYPE]<>"Home","Temp","Don't Know"
    but having it not separated by anything else but single comma...I am very confused.

    Can you please, help me to understand this syntax?
    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Union Query - united in wrong order (Access2000-03)

    The entire expression

    IIf([reg/temp]="R","Regular",IIf([tbl_1]![Grade]="IC","Ind Contractor",IIf([reg]="t",IIf([long descr]="RH","Contract",
    IIf([reg]="t",IIf([long descr]="PT" And [tbl_1]![TYPE]<>"Home","Temp","Don't Know"))))))

    is a nested IIf formula with 6 levels; it represents a single field.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query - united in wrong order (Access2000-03)

    I do not see 6 - I only see 5. What is the 6th you see?

    OK, if I change it like this should it work you think?
    IIf([reg/temp]="R","Regular",IIf([reg]="t",IIf([long descr]="RH","Contract",IIf([reg]="t",IIf([long descr]="PT" And [tbl_1]![TYPE]<>"Home","Temp","Don't Know",IIf([tbl_1]![Grade]="IC","Ind Contractor"))))))

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Union Query - united in wrong order (Access2000-03)

    This expression is not valid: the part

    IIf([long descr]="PT" And [tbl_1]![TYPE]<>"Home","Temp","Don't Know",IIf(...)

    has 3 arguments after the condition, there should be only two.

    (I count 6 IIf's)

  7. #7
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query - united in wrong order (Access2000-03)

    OK, how come in original version it gets executed fine?
    But when I re-arranging it curses at me?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Union Query - united in wrong order (Access2000-03)

    Because you rearrange it incorrectly. You need to conform to the syntax of the IIf function.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query - united in wrong order (Access2000-03)

    I did it I didi ti Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •