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!

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

Concatenate lines in a query to one line

Status
Not open for further replies.

haerion

IS-IT--Management
Sep 13, 2006
130
US
Hi all, I am trying to figure out how I could put the result of a query into a subject in a concatenate way.

Exemple:
query1
PO Supplier
1256 46
4587 46
8945 46

So the subject line would be "PO 1256-4587-8945"

I don't know enough coding to make this work, but I'm sure it can be done, prolly by mixing a DAO recordset and a concatenate of all the line in it.

Just need this part of the code, I can do the rest after to put it into a mail on the subject line using sendobject.

Btw, there is a maximum of 3 PO on each mail I send, and a minimum of 1

Thanks,
Haerion
 
Hi, haerion,

Something like this?
Code:
Private Function ConcatenateMyString()

[green]'declare some variables[/green]
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim i As Integer
Dim x As Integer
Dim myString As String

[green]'set object variables, open and populate the recordset[/green]
Set Db = CurrentDb()
Set Rs = Db.OpenRecordset("[red]qryMyQuery[/red]")
Rs.MoveLast
Rs.MoveFirst

[green]'determine number of records to use for PO string[/green]
If Rs.RecordCount >= 3 Then
    i = 3
    Else
        i = Rs.RecordCount
End If
    
[green]'initialize the string[/green]
myString = "PO "

[green]'loop through the recordset and build the string[/green]
For x = 1 To i Step 1
    myString = myString & Rs![PO] & "-"
    Rs.MoveNext
Next x

[green]'strip the trailing hyphen[/green]
myString = Left(myString, Len(myString) - 1)

[green]'return the string to the function[/green]
ConcatenateMyString = myString

[green]'destroy object variables[/green]
Rs.Close
Set Db = Nothing

End Function
HTH,

Ken S.
 
Short Answer:

Look at ADODB.Recordset and .Getstring

Long Answer:

Assumptions:
You only want values from PO not supplier.
Your query name is: qryMyQuery

Code:
Private function FetchPO() as string
dim rst as new adodb.recordset
rst.open "Select PO from qryMyQuery", currentproject.connection
fetchPO = PO & rst.getstring(,,,"-")
set rst = nothing
end function

I did this by hand off the top of my head, but it should work.

If you want to see the query returned you'll call FetchPO as the value of something.



Randall Vollen
Merrill Lynch
 
I got error 3061, too few parameter, expected 1
 
correction, missed some quotes.

Code:
Private function FetchPO() as string
dim rst as new adodb.recordset
rst.open "Select PO from qryMyQuery", currentproject.connection
fetchPO = "PO" & rst.getstring(,,,"-")
set rst = nothing
end function

Randall Vollen
Merrill Lynch
 
Got an error on the:

rst.open "Select PO from qryMyQuery", currentproject.connection

Any idea?
 
How are ya haerion . . .

. . . and this:
Code:
[blue]Public Function POSubject() As String
   Dim db As DAO.Database, rst As DAO.Recordset, Build As String
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("[purple][b][i]QueryName[/i][/b][/purple]", dbOpenDynaset)
   
   If Not rst.BOF Then
      Do
         If Build <> "" Then
            Build = Build & "-" & CStr(rst!PO)
         Else
            Build = "PO " & "CStr(rst!PO)"
         End If
         
         rst.MoveNext
      Loop Until rst.EOF
   End If
   
   Set rst = Nothing
   Set db = Nothing
      
End Function[/blue]
[blue]Your Thoughts? . . .[/blue]

Calvin.gif
See Ya! . . . . . .
 
got PO CStr(rst!PO)-P6437 :p

so i change the:

Build = "PO " & "CStr(rst!PO)"
to
Build = "PO " & CStr(rst!PO)

hehe, but more than that it work like a charm :)

You're good man, and always helping me out on a lot of stuff, thanks Ace :)

Eupher and ranger, I'm sure they would have work and there were some nice code Im prolly gonna use in the future :)

My main error was that my query had a WHERE clause referencing to the form, so I had to put the sql inside the code and modify it so that I got the data by vba instead of by the query. Not sure I'm clear enough tough :p

In the end, thank you all for your help :)

here is my final code:

Code:
Private Sub cmdsendEmail_Click()
Dim SentTo As String
Dim body As String

   Dim db As DAO.Database, rst As DAO.Recordset, Build As String
   Dim strsql As String
   strsql = "SELECT qrySplittingOrder.Date, [PO Made].PO, Fournisseurs.Langue, Fournisseurs.SupplierName, Acheteur.Acheteur, [PO Made].DateDeCommande, Fournisseurs.Contact, Fournisseurs.Fax AS FaxSupp, Acheteur.Extension, Acheteur.Email, qrySplittingOrder.VendorNo" & _
            " FROM ((qrySplittingOrder INNER JOIN Fournisseurs ON qrySplittingOrder.VendorNo=Fournisseurs.[Supplier#]) INNER JOIN [PO Made] ON qrySplittingOrder.PO=[PO Made].PO) INNER JOIN Acheteur ON [PO Made].Acheteur=Acheteur.Numéro" & _
            " WHERE qrySplittingOrder.VendorNo = '" & Forms!OrderSplitting!lboSupplier & "'" & _
            " GROUP BY qrySplittingOrder.Date, [PO Made].PO, Fournisseurs.Langue, Fournisseurs.SupplierName, Acheteur.Acheteur, [PO Made].DateDeCommande, Fournisseurs.Contact, Fournisseurs.Fax, Acheteur.Extension, Acheteur.Email, qrySplittingOrder.VendorNo" & _
            " ORDER BY qrySplittingOrder.VendorNo"

   Set db = CurrentDb
   Set rst = db.OpenRecordset(strsql, dbOpenDynaset)
   
   If Not rst.BOF Then
      Do
         If Build <> "" Then
            Build = Build & "-" & CStr(rst!PO)
         Else
            Build = "PO " & CStr(rst!PO)
         End If
         
         rst.MoveNext
      Loop Until rst.EOF
   End If
   
   Set rst = Nothing
   Set db = Nothing

body = "If you can't open this file go on this link and download the program:" & Chr(13) & _
"[URL unfurl="true"]http://support.microsoft.com/kb/q175274/"[/URL] & Chr(13)

SentTo = Forms!OrderSplitting.[Supplier sub form].Form!Email

DoCmd.SendObject acSendReport, "PO Send by Fax", acFormatSNP, SentTo, , , Build, body

End Sub
 
Btw, you guys know how to change the name of a file when doing a sendobject? like other than giving it the report name :)
 
Back to the OP, have a look here: faq701-4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top