Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Concatenate Fields withing SQL Select Clause

Status
Not open for further replies.
2Plan

2Plan

MIS
Mar 29, 2005
55
US
How can I concatenate CommProjCode with UniqProjCode as FilePlanCode?

sql = "SELECT MasterProjectCodes.CommProjCode \& '-' \& CommonProjectCodes.UniqProjCode As FilePlanCode, " & _
" INTO Temp FROM CommonProjectCodes INNER JOIN MasterProjectCodes ON " & _
"CommonProjectCodes.CommProjCode = MasterProjectCodes.CommProjCode " & _
"WHERE MasterProjectCodes.FileCodeOwner Like " _
& "'" & Form.txtCodeSearch.Value & "'
 
Sort by date Sort by votes
SELECT MasterProjectCodes.CommProjCode & " & "-" & " & CommonProjectCodes.UniqProjCode As FilePlanCode, " & _
" INTO Temp FROM CommonProjectCodes INNER JOIN MasterProjectCodes ON " & _
"CommonProjectCodes.CommProjCode = MasterProjectCodes.CommProjCode " & _
"WHERE MasterProjectCodes.FileCodeOwner Like " _
& "'" & Form.txtCodeSearch.Value & "'"
 
Upvote 0 Downvote
Hello 2Plan!

I you're wanting the results of your query to be concatenated, use the double pipe ||.
Code: 
sql = "SELECT COLUMN1 || '-' || COLUMN2 FROM TABLENAME"

Good luck!

He who has knowledge spares his words, and a man of understanding is of a calm spirit. Even a fool is counted wise when he holds his peace; when he shuts his lips, he is considered perceptive. - King Solomon
 
Upvote 0 Downvote
This works!

sql = "SELECT MasterProjectCodes.* " & _
" INTO Temp FROM CommonProjectCodes INNER JOIN MasterProjectCodes ON " & _
"CommonProjectCodes.CommProjCode = MasterProjectCodes.CommProjCode " & _
"WHERE MasterProjectCodes.FileCodeOwner Like " _
& "'" & Form.txtCodeSearch.Value & "*'"

But this does not work! I get missing operator in query expression

sql="SELECT MasterProjectCodes.CommProjCode & " & "-" & " & CommonProjectCodes.UniqProjCode As FileCodePlan " & _
" INTO Temp FROM CommonProjectCodes INNER JOIN MasterProjectCodes ON " & _
"CommonProjectCodes.CommProjCode = MasterProjectCodes.CommProjCode " & _
"WHERE MasterProjectCodes.FileCodeOwner Like " _
& "'" & Form.txtCodeSearch.Value & "*'"

sql = "SELECT MasterProjectCodes.CommProjCode || '-' || CommonProjectCodes.UniqProjCode As FileCodePlan " & _
" INTO Temp FROM CommonProjectCodes INNER JOIN MasterProjectCodes ON " & _
"CommonProjectCodes.CommProjCode = MasterProjectCodes.CommProjCode " & _
"WHERE MasterProjectCodes.FileCodeOwner Like " _
& "'" & Form.txtCodeSearch.Value & "*'"

Can you see the problem?
Thanks!
 
Upvote 0 Downvote
What about
Code: 
"SELECT MasterProjectCodes.CommProjCode & '-' & CommonProjectCodes.UniqProjCode As FilePlanCode, "

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

theConjurian
  • Locked
  • Question
Assistance with where clause coding
Replies
5
Views
488
combo
combo
AS Mughal
  • Locked
  • Question
Select Multiple Images and store their file path and image name in text fields of ms access table 1
Replies
7
Views
628
Andrzejek
Andrzejek
lameid
  • Locked
  • Question
ADO UpdateBatch SQL Server Example?
Replies
2
Views
465
Andrzejek
Andrzejek
Mke85
  • Locked
  • Question
VBA, SQL to select and then delete records 1
Replies
16
Views
1K
dhookom
dhookom
TheresAlwaysAWay
  • Locked
  • Question
Trying to programmatically select Navigation Options in Access Options
Replies
4
Views
615
combo
combo

Part and Inventory Search

Sponsor

Back
Top