RE: How do you concatanate two table fields in CFQUERY with MS Access
tblJobSeekers.pverify,tblJobSeekers.jsphone1 [atfname] + " " + [atlname] AS AgentName Looks like you are missing a comma after .jsphone] _ From: Angel Stewart [mailto:[EMAIL PROTECTED] 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 -Original Message- From: CF-Talk [mailto:[EMAIL PROTECTED] Sent: Saturday, May 15, 2004 3:01 AM To: CF-Talk Subject: CF-Talk: Digest every 2 hours Subject: How do you concatanate two table fields in CFQUERY with MS 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. _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: How do you concatanate two table fields in CFQUERY with MS Access
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, > >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 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: How do you concatanate two table fields in CFQUERY with MS Access
Sql uses + & is the CF concatenation. Silly gel. _ From: Angel Stewart [mailto:[EMAIL PROTECTED] 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 _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]