Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Union 2

Status
Not open for further replies.
Swi

Swi

Programmer
Joined
Feb 4, 2002
Messages
1,978
Location
US
How do I get the results in a union query to come out in the order of each select statement? Thanks.

Code: 
        rs.Open "SELECT [NAME] FROM [" & FileName & "] WHERE LEN([NAME]) = " & _
        "(SELECT MAX(LEN([NAME])) FROM [" & FileName & "]) UNION " & _
        "SELECT [ADDR5] FROM [" & FileName & "] WHERE LEN([ADDR5]) = " & _
        "(SELECT MAX(LEN([ADDR5])) FROM [" & FileName & "]) UNION " & _
        "SELECT [ADDR4] FROM [" & FileName & "] WHERE LEN([ADDR4]) = " & _
        "(SELECT MAX(LEN([ADDR4])) FROM [" & FileName & "]) UNION " & _
        "SELECT [ADDR3] FROM [" & FileName & "] WHERE LEN([ADDR3]) = " & _
        "(SELECT MAX(LEN([ADDR3])) FROM [" & FileName & "]) UNION " & _
        "SELECT [ADDR2] FROM [" & FileName & "] WHERE LEN([ADDR2]) = " & _
        "(SELECT MAX(LEN([ADDR2])) FROM [" & FileName & "]) UNION " & _
        "SELECT [ADDR1] FROM [" & FileName & "] WHERE LEN([ADDR1]) = " & _
        "(SELECT MAX(LEN([ADDR1])) FROM [" & FileName & "]) UNION " & _
        "SELECT [CITY] FROM [" & FileName & "] WHERE LEN([CITY]) = " & _
        "(SELECT MAX(LEN([CITY])) FROM [" & FileName & "])", conn, adOpenStatic, adLockReadOnly, adCmdText

Swi
 
Sort by date Sort by votes
You can include an additional field for sorting:

[tt]rs.Open "SELECT [red]1 As SortFld[/red], [NAME] FROM [" & FileName & "] WHERE LEN([NAME]) = " & _
"(SELECT MAX(LEN([NAME])) FROM [" & FileName & "]) UNION " & _
"SELECT [red]2 As SortFld[/red], [ADDR5] FROM [" & FileName & "] WHERE LEN([ADDR5]) = " & _

<And so on.>

& "ORDER BY SortFld"[/tt]
 
Upvote 0 Downvote
Use UNION ALL ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Thanks to both of you.

Swi
 
Upvote 0 Downvote
One additional question. I assume I need an IIF statement somewhere in the SQL statement because it does not look like the UNION ALL statement is accounting for blanks. I would have assumed that it would have been on the something like IIF((SELECT MAX(LEN([CITY])) > 0,(SELECT MAX(LEN([CITY])),0) but it gives me an error that says "SYNTAX ERROR IN UNION QUERY"

Swi
 
Upvote 0 Downvote
I gave it another shot and still can not get it to work. It still seems to be ignoring anything that does not have a value.

Code: 
    With rs
        .CursorLocation = adUseServer
        .Open "SELECT IIF(ISNULL([NAME]),'XXXXX',[NAME]) FROM [" & FileName & "] WHERE LEN([NAME]) = " & _
        "(SELECT MAX(LEN([NAME])) FROM [" & FileName & "]) UNION ALL " & _
        "SELECT IIF(ISNULL([ADDR5]),'XXXXX',[ADDR5]) FROM [" & FileName & "] WHERE LEN([ADDR5]) = " & _
        "(SELECT MAX(LEN([ADDR5])) FROM [" & FileName & "]) UNION ALL " & _
        "SELECT IIF(ISNULL([ADDR4]),'XXXXX',[ADDR4]) FROM [" & FileName & "] WHERE LEN([ADDR4]) = " & _
        "(SELECT MAX(LEN([ADDR4])) FROM [" & FileName & "]) UNION ALL " & _
        "SELECT IIF(ISNULL([ADDR3]),'XXXXX',[ADDR3]) FROM [" & FileName & "] WHERE LEN([ADDR3]) = " & _
        "(SELECT MAX(LEN([ADDR3])) FROM [" & FileName & "]) UNION ALL " & _
        "SELECT IIF(ISNULL([ADDR2]),'XXXXX',[ADDR2]) FROM [" & FileName & "] WHERE LEN([ADDR2]) = " & _
        "(SELECT MAX(LEN([ADDR2])) FROM [" & FileName & "]) UNION ALL " & _
        "SELECT IIF(ISNULL([ADDR1]),'XXXXX',[ADDR1]) FROM [" & FileName & "] WHERE LEN([ADDR1]) = " & _
        "(SELECT MAX(LEN([ADDR1])) FROM [" & FileName & "]) UNION ALL " & _
        "SELECT IIF(ISNULL([CITY]),'XXXXX',[CITY]) FROM [" & FileName & "] WHERE LEN([CITY]) = " & _
        "(SELECT MAX(LEN([CITY])) FROM [" & FileName & "])", conn, adOpenStatic, adLockReadOnly, adCmdText
        If rs.BOF And rs.EOF Then
        Else
            Print #1, "LONGEST NAME: " & rs!Name
            rs.MoveNext
            Print #1, "LONGEST ADDRESS 5: " & rs!Name
            rs.MoveNext
            Print #1, "LONGEST ADDRESS 4: " & rs!Name
            rs.MoveNext
            Print #1, "LONGEST ADDRESS 3: " & rs!Name
            rs.MoveNext
            Print #1, "LONGEST ADDRESS 2: " & rs!Name
            rs.MoveNext
            Print #1, "LONGEST ADDRESS 1: " & rs!Name
            rs.MoveNext
            Print #1, "LONGEST CITY: " & rs!Name
        End If
        .Close
    End With

Swi
 
Upvote 0 Downvote
Correct.

Swi
 
Upvote 0 Downvote
I am still having issues with this. Can anyone shine a light on the issue? Thanks.

Swi
 
Upvote 0 Downvote
Are you sure that the fields are Null and not zero length strings? You could try:

IIF(Trim([City] & "")="",
 
Upvote 0 Downvote
That is a good point but I tried the below code and it still did not work:

Code: 
    With rs
        .CursorLocation = adUseServer
        .Open "SELECT IIF(TRIM([NAME] & '')='','',[NAME]) FROM [" & FileName & "] WHERE LEN([NAME]) = " & _
        "(SELECT MAX(LEN([NAME])) FROM [" & FileName & "]) UNION ALL " & _
        "SELECT IIF(TRIM([ADDR5] & '')='','',[ADDR5]) FROM [" & FileName & "] WHERE LEN([ADDR5]) = " & _
        "(SELECT MAX(LEN([ADDR5])) FROM [" & FileName & "]) UNION ALL " & _
        "SELECT IIF(TRIM([ADDR4] & '')='','',[ADDR4]) FROM [" & FileName & "] WHERE LEN([ADDR4]) = " & _
        "(SELECT MAX(LEN([ADDR4])) FROM [" & FileName & "]) UNION ALL " & _
        "SELECT IIF(TRIM([ADDR3] & '')='','',[ADDR3]) FROM [" & FileName & "] WHERE LEN([ADDR3]) = " & _
        "(SELECT MAX(LEN([ADDR3])) FROM [" & FileName & "]) UNION ALL " & _
        "SELECT IIF(TRIM([ADDR2] & '')='','',[ADDR2]) FROM [" & FileName & "] WHERE LEN([ADDR2]) = " & _
        "(SELECT MAX(LEN([ADDR2])) FROM [" & FileName & "]) UNION ALL " & _
        "SELECT IIF(TRIM([ADDR1] & '')='','',[ADDR1]) FROM [" & FileName & "] WHERE LEN([ADDR1]) = " & _
        "(SELECT MAX(LEN([ADDR1])) FROM [" & FileName & "]) UNION ALL " & _
        "SELECT IIF(TRIM([CITY] & '')='','',[CITY]) FROM [" & FileName & "] WHERE LEN([CITY]) = " & _
        "(SELECT MAX(LEN([CITY])) FROM [" & FileName & "])", conn, adOpenStatic, adLockReadOnly, adCmdText
        If rs.BOF And rs.EOF Then
        Else
            Print #1, "LONGEST NAME: " & rs.Fields(0).Value
            rs.MoveNext
            Print #1, "LONGEST ADDRESS 5: " & rs.Fields(0).Value
            rs.MoveNext
            Print #1, "LONGEST ADDRESS 4: " & rs.Fields(0).Value
            rs.MoveNext
            Print #1, "LONGEST ADDRESS 3: " & rs.Fields(0).Value
            rs.MoveNext
            Print #1, "LONGEST ADDRESS 2: " & rs.Fields(0).Value
            rs.MoveNext
            Print #1, "LONGEST ADDRESS 1: " & rs.Fields(0).Value
            rs.MoveNext
            Print #1, "LONGEST CITY: " & rs.Fields(0).Value
        End If
        .Close
    End With

Swi
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TheresAlwaysAWay
  • Locked
  • Question Question
Union query provides different reslt than when summing the combined queries individually
Replies
12
Views
2K
TheresAlwaysAWay
TheresAlwaysAWay
leifoet
  • Locked
  • Question Question
SQL query with UNION ALL of 2 tables does not work
Replies
7
Views
573
Andrzejek
Andrzejek
VickyC
  • Locked
  • Question Question
Help with UNION query?
Replies
4
Views
296
dhookom
dhookom
air1access
  • Locked
  • Question Question
Union Query to Reformat Results
Replies
7
Views
382
air1access
air1access
GPnew
  • Locked
  • Question Question
Outer join or Union or
Replies
3
Views
269
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top