Access SQL Statement to Format a Dropdown List

jonesl3 · November 24th, 2004, 08:02 AM

Hi All:

I am trying to get a dropdown list to show the items in Alpha order but can not get the SQL statement that works. Here is my current command line:

SELECT IIf([tblCustomerMain].[CustFirstName] Is Null,[tblCustomerMain].[CustLastName],[tblCustomerMain].[CustLastName]+", "+[tblCustomerMain].[CustFirstName]) AS CustomerName, tblCustomerMain.CID
FROM tblCustomerMain
WHERE (((tblCustomerMain.Office)=Get_OfficeID()));

I would like the Alias "CustomerName" to be listed by the CustLastName Alpha order. I greatly appreciate any help.

Thanks,

Lee

jemacc · November 24th, 2004, 09:27 AM

is your statement not working or your alpha order not ordered correctly?

You should be able to add order by custFirstName

Code:

SELECT IIf([tblCustomerMain].[CustFirstName] Is Null,[tblCustomerMain].[CustLastName],[tblCustomerMain].[CustLastName]+", "+[tblCustomerMain].[CustFirstName]) AS CustomerName, tblCustomerMain.CID
FROM tblCustomerMain
WHERE (((tblCustomerMain.Office)=Get_OfficeID()));
Order by CustLastName

Jaime E. Maccou
Applications Analyst

jonesl3 · November 24th, 2004, 09:38 AM

The statement is working, just the order is not working correctly. I see
that I may have made a syntax error. I thought that the ; at the end of
the third line was the last command that could be entered. I will try your
suggestion.

Thank you very much.

Lee Jones

Mihai B · November 24th, 2004, 10:08 AM

I am wondering why don't you create a query in Design mode, and then shift to SQL view and copy-paste the code?

Mihai