How do you concatanate two table fields in CFQUERY with MS Access

Hey all, I need some help with MSAccess SQL statements. This code throws the error: Too Few Parameters SELECT tblusers.username, tblJobSeekers.jsfname, tblJobSeekers.jslname, tblJobSeekers.jsemail, tblJobSeekers.jscountry, tblJobSeekers.pverify, tblJobSeekers.jsphone1, [atfname] & " " & [atlname] AS AgentName FROM tblAgent INNER JOIN (tblusers INNER JOIN tblJobSeekers ON tblusers.userid = tblJobSeekers.userid) ON tblAgent.atid = tblJobSeekers.agentid ORDER BY tblJobSeekers.jslname; >From this you can see that I want to concatenate the atfname and atlname fields into one field and call it Agent Name. There's a reason I need to have one column with this value rather than do it later when I'm displaying the data, its for use with a custom tag. Can anyone suggest why I'm getting this error, and what should be the correct syntax? This particular query works perfectly from within MS Access itself. -Gel

Sql uses +   & is the CF concatenation. Silly gel.   _____ Sent: Friday, May 14, 2004 9:40 PM To: CF-Talk Subject: How do you concatanate two table fields in CFQUERY with MS Access Hey all, I need some help with MSAccess SQL statements. This code throws the error: Too Few Parameters SELECT tblusers.username, tblJobSeekers.jsfname, tblJobSeekers.jslname, tblJobSeekers.jsemail, tblJobSeekers.jscountry, tblJobSeekers.pverify, tblJobSeekers.jsphone1, [atfname] & " " & [atlname] AS AgentName FROM tblAgent INNER JOIN (tblusers INNER JOIN tblJobSeekers ON tblusers.userid = tblJobSeekers.userid) ON tblAgent.atid = tblJobSeekers.agentid ORDER BY tblJobSeekers.jslname; >From this you can see that I want to concatenate the atfname and atlname fields into one field and call it Agent Name. There's a reason I need to have one column with this value rather than do it later when I'm displaying the data, its for use with a custom tag. Can anyone suggest why I'm getting this error, and what should be the correct syntax? This particular query works perfectly from within MS Access itself. -Gel   _____  

Try atfname || ' ' || atlname AgentName I got that from my teaching yourself sql in 21 days book p 54. Jim Louis Best Meetings Inc. >Hey all, ----- Excess quoted text cut - see Original Post for more -----

tblJobSeekers.pverify,tblJobSeekers.jsphone1 [atfname] + " " + [atlname] AS AgentName Looks like you are missing a comma after .jsphone]   _____ Sent: Saturday, May 15, 2004 6:15 AM To: CF-Talk Subject: FW: How do you concatanate two table fields in CFQUERY with MS Access Well Sandy...I tried that initially but it just didn't work. So I thought I was doing something totally wrong and that Access SQL uses something beside the +. But! Good news! I tried that code in an SQL Statement with nothing else but Select atfname + ' ' + atlname AS Agentname FROM tblagent And it worked fine. So there must be something else in the syntax of SELECT tblusers.username, tblJobSeekers.jsfname, tblJobSeekers.jslname,tblJobSeekers.jsemail, tblJobSeekers.jscountry, tblJobSeekers.pverify,tblJobSeekers.jsphone1 [atfname] + " " + [atlname] AS AgentName FROM tblAgent INNER JOIN (tblusers INNER JOIN tblJobSeekers ON tblusers.userid = tblJobSeekers.userid) ON tblAgent.atid = tblJobSeekers.agentid ORDER BY tblJobSeekers.jslname; That's causing it to fail. *sigh* I hate syntax errors. And I don't understand why the MSACcess SQL string failed to work. Could someone explain that? Thanks for the help :) -Gel Access From: "Sandy Clark" Date: Fri, 14 May 2004 22:18:07 -0400 Thread: http://www.houseoffusion.com/cf_lists/index.cfm/method=messages&threadid =32529&forumid=4#163293 Sql uses +   & is the CF concatenation. Silly gel.   _____   _____