Can i co (1 Viewer)

murray83

Games Collector
Local time
Today, 10:34
Joined
Mar 31, 2017
Messages
728
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
 

Attachments

  • exp.png
    exp.png
    4.1 KB · Views: 203
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 10:34
Joined
Sep 21, 2011
Messages
14,038
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
 

murray83

Games Collector
Local time
Today, 10:34
Joined
Mar 31, 2017
Messages
728
neither of them worked throwed up errors

im guessing i would of put it in front of here

Code:
SELECT 

Location_Id,
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:34
Joined
Sep 21, 2011
Messages
14,038
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
 

Attachments

  • prefix semi colon.PNG
    prefix semi colon.PNG
    4.7 KB · Views: 219

murray83

Games Collector
Local time
Today, 10:34
Joined
Mar 31, 2017
Messages
728
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

Games Collector
Local time
Today, 10:34
Joined
Mar 31, 2017
Messages
728
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]
 

Attachments

  • 1.png
    1.png
    5.1 KB · Views: 241

Gasman

Enthusiastic Amateur
Local time
Today, 10:34
Joined
Sep 21, 2011
Messages
14,038
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))
 
Last edited:

Users who are viewing this thread

Top Bottom