Merging 2 columns into one with Access SQL

Select Cntct_Lnme & ", " & Cntct_Fname AS ContactName, Sup_Cmpny_Nme, Adres_1 & ", " & Adres_2 & ", " & Adres_3 & ", " & Adres_4 & " " & Adres_5 AS Address FROM tblName

sirbounty,

I get a syntax error message with your suggestion.

Jmantha,

when I run the query, I get a window asking me to enter a parameter value for Contact F_name, If I click on ok, I get another window asking me to enter a parameter value for Contact_Lname.

Can you fix this?

Thanks

Did you write this textually (Except for MyTable) ?

SELECT  Cntct_Lnme  + ', ' + Cntct_Fnme  AS Contact_Name, Sup_Cmpny_Nme , Adres_1 + ', ' + Adres_2 + ', ' + Adres_3 + ', ' + Adres_4 + ' ' + Adres_5 AS Address FROM MyTable

There's no place that it says Contact F_name or Contact_Lname in that sql...  Unless you replaced MyTable, with another query and that query is generating the error...

Did you make a new query or edited one already existing ?  If it already existed, try a brand new query...

What's the error and where does it occur?
tblName would be the name of your table..since you didn't post one, I used tblName as a substitute...

It's not really an error message, but after I run the query, I get a pop up window asking me to ENTER A PARAMETER VALUE for Contact_Lname, if I hit ok I get another Window asking to ENTER A PARAMETER VALUE for Contact_Fname.

I have modified the syntax by replacing it with the correct field names that my table has and I did change the MyTable name to the name of the table I have.  Here are the results I get:

Contact_Name    Sup_Company_Name                     Address
            Wilson Sporting Inc. 1001 W. Johnson Dr., Suite 100, Springfield, MO 65804
                Lake Tahoe Sport      
                A.J. Sport      
        Golf Discount of Springfield      11025 Vanguard Dr., Suite 55, Sprinfield, IL 85850
      Mathews Sporting      
      Jimmy Johnson Used Sport Equipment      
      Magic Sport Equip      
      Commando Sport      
     K.C. Sporting Inc.      12120 E. 158th Street, Suite 101, Kansas City, KS 66661
      Sporting Hut      


As you can see not all of the Suppliers came back with Address or Contact Names.

System Expert,

The results shouldn't come back with empty results, The tables where the data is have all those fields filled with data, that's why I don't understand why the query returns empty fields....does that mean the syntax is incorrect?

e.g
adres_1='203, Hilton '
adres_2='NY'
adres_3=Null or say ''

if you just right
(adres_1 + ' ' + adres_2 + ' ' + adres_3 ) as Addredd

then blank result will come

that's why iif(isNull(),,) i used and it work properly
 check it
Bye

Ok Expert,

I used yours works but I am missing commas to separate the last name from the first name, and commas separating street address from city, state, zip code....also I tried sorting the table by Contact Name by using Order By and it didn't work for me, what's the appropriate syntax for it sort by Asc on Contact Name field.

Thanks

Sirbounty,

I don't know what the hell I did wrong, but I got your last message posted and it worked.  It came back with the results I need...I am sorry and thank you!

Contact_Name contains two Field =Cntct_Fnme and
Cntct_Lnme

So Try

Order by (Cntct_Fnme  + ' ' + Cntct_Lnme )