Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-22-2018, 08:22 AM   #1
murray83
sega collector
 
Join Date: Mar 2017
Location: Daventry, UK
Posts: 316
Thanks: 52
Thanked 0 Times in 0 Posts
murray83 is on a distinguished road
Lightbulb Can i concatenate

here is my following code

Code:
SELECT 

Location_Id, Qty_Allocated, Qty_On_Hand

FROM 

Pick_Face 

Where

Qty_Allocated >1 AND Qty_On_Hand <1

ORDER BY Location_Id
and attached is the output what i wish to do if possible concatenate the LOCATION so it is prefaced with a ;

if i was doing this in excel would be easy but as i am very new to this would like if poss a few pointers

cheers
Attached Images
File Type: png exp.png (4.1 KB, 13 views)


Last edited by murray83; 06-22-2018 at 08:33 AM. Reason: missed concatenate from title
murray83 is offline   Reply With Quote
Old 06-22-2018, 08:53 AM   #2
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,132
Thanks: 268
Thanked 317 Times in 302 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Can i co

I would first try
Code:
Location:";" & Location_Id
if that does not work, then
Code:
Location:";" & [Location_Id]
and finally if that did not work
Code:
Location:";" & [Pick_Face].[Location_Id]
HTH
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 06-22-2018, 10:24 AM   #3
murray83
sega collector
 
Join Date: Mar 2017
Location: Daventry, UK
Posts: 316
Thanks: 52
Thanked 0 Times in 0 Posts
murray83 is on a distinguished road
Re: Can i co

neither of them worked throwed up errors

im guessing i would of put it in front of here

Code:
SELECT 

Location_Id,

murray83 is offline   Reply With Quote
Old 06-22-2018, 10:45 AM   #4
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,132
Thanks: 268
Thanked 317 Times in 302 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Can i co

Really?
Use the query Gui to put the fields in.

In one of mine with made up names I have
Code:
FullName: ";" & [Forenames] & " " & [tblClient.Surname]
which produces the results in the picture

SQL is
Code:
SELECT ";" & [Forenames] & " " & [tblClient.Surname] AS FullName
In fact it is no different to Excel?
A1 = ;
B1 = Name
C1= A1&B1 = ;Name
Attached Images
File Type: png prefix semi colon.PNG (4.7 KB, 8 views)
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
murray83 (06-22-2018)
Old 06-22-2018, 11:41 AM   #5
murray83
sega collector
 
Join Date: Mar 2017
Location: Daventry, UK
Posts: 316
Thanks: 52
Thanked 0 Times in 0 Posts
murray83 is on a distinguished road
Re: Can i co

yeah sorry i feel rather foolish as i was trying to put it all on the SQL Query but yes this works when you do it in Query Design View
murray83 is offline   Reply With Quote
Old 06-22-2018, 11:50 AM   #6
murray83
sega collector
 
Join Date: Mar 2017
Location: Daventry, UK
Posts: 316
Thanks: 52
Thanked 0 Times in 0 Posts
murray83 is on a distinguished road
Re: Can i co

sorry for the DP but just would like one last thing

attached is how it is displayed, sub-form shows the info then the text box will show the locations with the ;

but as you can see from attached shot it just shows the first and here is my code for the source for the text box

Code:
=[Empty_Pick_Face_Conc_Subform].[Form]![LOC_ID] & [Empty_Pick_Face_Conc_Subform].[Form]![LOC_ID]
Attached Images
File Type: png 1.png (5.1 KB, 14 views)
murray83 is offline   Reply With Quote
Old 06-22-2018, 11:10 PM   #7
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,132
Thanks: 268
Thanked 317 Times in 302 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Can i co

Well firstly you appear to be just concatenating the same field?
I expect your subform is 1 to many, as most subforms are?

In which case, it will only pick up the first record in the subform and you have just concatenated it twice.?

You would need a function to read all the LOC_ID in the related subform and concatenate each to a build a string. It is that string that you would use in your control.

Off the top of my head, I think you could do it with a function in the form (just for ease of use with recordsource).

pseudo logic
Code:
Set a recordset object to the subform recordset (so you have exactly the same data)
Move first
Perform until EOF of recordset
    strString = strString & rs!LOC_ID
    Move Next
Loop

Set recordset object to Nothing
then return the string as the function name
Concat_LOC_ID = strString

if you called the function Concat_LOC_ID

HTH

Edit:
Also if all you wanted the ';' for was as a delimiter, you could do that in the function and not create extra data in the query.?

You would use
Code:
strString = strString & rs!Location_ID & ";"
and then remove the last ';' with
Code:
strString = LEFT(strString,LEN(strString -1))


__________________
Access novice. Sometimes trying to give something back.
Access 2007

Last edited by Gasman; 06-22-2018 at 11:22 PM.
Gasman is offline   Reply With Quote
Reply

Tags
concatenate , sql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 06:40 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World