Access query from Northwind database

[bharatkk]

Hi all,

I am not sure how significant this question is but I am posting this all the same because I am very baffled by it. Following is a modified sql statement

SELECT IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])) AS [File As], 


IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name])) AS [Contact Name]


FROM Customers

As per my understanding when the query is run their should appear two field 1) "File As" and 2) "Contact name" and in this order. What baffles me is that instead on File As appearing as first field it appears 2nd after "Contact Name" but actually "Contact Name" field appears first and then "File As".

I thought may be this is because alphabetically "C" comes before "F" but when I changed it to anything else "File As" field comes first and then the other field. I have even tried just "A" with same consequence i.e. "File As" field appears first and then "A".



Am I making any sense ? Could anyone please help me clear this confusion ?

By the way the code I posted in one single query. I have broken them in different lines for readability.

Thanks & regards everybody,

Bharat

[June7]

I built query using those alias names and cannot replicate this. [File As] comes before [Contact Name].

[bharatkk]

Hello,

Thanks for your response. I am inserting screen shot for your reference :


Thanks & regards,

Bharat

I built query using those alias names and cannot replicate this. [File As] comes before [Contact Name].

[jwhite]

In that the design view order and datasheetview order are not the same, it implies the fields were re-arranged at some point and the view was saved in that state. While in datasheetview you can order the columns any way you want, close it, and you will be prompted to save the layout.

You could also reset the order as seen in design view:

Public Sub ResetColumnOrder(strQueryName)

    Dim fld As DAO.Field
    Dim qdf As DAO.QueryDef

    Set qdf = CurrentDb.QueryDefs(strQueryName)

    For Each fld In qdf.Fields
        On Error Resume Next
        fld.Properties.Delete "ColumnOrder"
    Next fld

End Sub

[bharatkk]

Hello!

Thanks for your response. Based on your suggestion I reset the order and it worked.

In that the design view order and datasheetview order are not the same, it implies the fields were re-arranged at some point and the view was saved in that state. While in datasheetview you can order the columns any way you want, close it, and you will be prompted to save the layout.

You could also reset the order as seen in design view:

Public Sub ResetColumnOrder(strQueryName)

    Dim fld As DAO.Field
    Dim qdf As DAO.QueryDef

    Set qdf = CurrentDb.QueryDefs(strQueryName)

    For Each fld In qdf.Fields
        On Error Resume Next
        fld.Properties.Delete "ColumnOrder"
    Next fld

End Sub

Thanks once again.

Bharat