Concat String help

[bjreb]

I have a form base on a recordset that is created at runtime when the form loads. Part of the string includes some open arguments. One of them is a folder path.
The final string should be C\foldername\picturename
The first part is the static path name ex(C\folder one).
The second half is a column from the recordset used for the form.

My string to populate the recordset looks something like this just a short example
Dim sDisplay as string
sDisplay = me.openargs
"Select " & sDisplay & " mytable.customer as cust from mytable"
I can't figure out the correct syntax. I'm sure its easy

Thanks
Andy

 

[jdraw]

I suggest you post more details eg: what exactly are you trying to do in plain English and some sample code. You should use Debug.Print statement(s) to help with debugging.

 

[bjreb]

Ok. I have a form that collects a display name from a list and a folder from the file dialog. These are passed as open arguments to this form whos recordset is set upon loading.

Private Sub Form_Load()
Dim dbs As DAO.Database
Dim qrf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim sDisplay As String
Dim iDisplay As Integer
Dim intPos As Integer
Dim strControlName As String
Dim strValue As String
Dim sStr As String

If Len(Me.OpenArgs) > 0 Then
    ' Position of the pipe
    intPos = InStr(Me.OpenArgs, "|")

    If intPos > 0 Then

      ' Retrieve Control Name from the first part of the string
      [B]sDisplay[/B] = Left$(Me.OpenArgs, intPos - 1)  This is the directory string
      

      ' Retrieve Value to Assign from the end of the string
      iDisplay = Mid$(Me.OpenArgs, intPos + 1)


    End If
  End If

Set dbs = CurrentDb

sStr = "SELECT Transactions.Image_1, Extract_Photo_1.Customer, Extract_Photo_1.MerchandiserID," & _
"Extract_Photo_1.Display, Extract_Photo_1.DisplayType, Extract_Photo_1.Program.Id, " & _
"Extract_Photo_1.QtCases, Extract_Photo_1.Keybolt[B],""" & sDisplay & """& Extract_Photo_1.cust as Mike ," & _[/B]
"Extract_Photo_1.Expr1, Extract_Photo_1.Name, Extract_Photo_1.Transactions.Id,Extract_Photo_1.cust " & _
"FROM Transactions INNER JOIN Extract_Photo_1 ON Transactions.Id = Extract_Photo_1.Transactions.Id where Extract_Photo_1.Program.Id = " & iDisplay & ";"

Debug.Print sDisplays
Set rs = dbs.OpenRecordset(sStr, dbOpenSnapshot)
Set Me.Recordset = rs
End Sub

 

[Orthodox Dave]

You haven't said what is going wrong, so I can only say what is obvious to me.

I suggest you change

iDisplay = Mid$(Me.OpenArgs, intPos + 1)

to

iDisplay = Val(Mid(Me.OpenArgs, intPos + 1))

(I am assuming the first character after the pipe is numeric. Mid$ returns a string.)

Also this part of your SQL looks wrong

Keybolt,""" & sDisplay & """&

(there should be a space before the last &)

I then suggest you add debug.print sStr, run the sub if it will run, copy the sStr result from the Immediate window and paste it into the SQL section of a blank query and go to design view. If the query design view looks ok, run the query and see if the result is right. That's the easy way to do SQL syntax. If the design view looks wrong, modify it then go back to the SQL, read off any changes and modify your code accordingly.