need help with single and double quotes

[vicsaccess]

I have a SQL from a recordsource that I am trying to put to code behind a command button so that when the user clicks on the command button the recordsource changes. I have the string together but am having problems understanding when to use single and double quotes. I understand that I need to use single if the variable is a text string but I haven't done this with a trim before and am pulling out my hair. can someone please help me understand where and why to use single quotes in this string

Private Sub cmdcraft_Click()
    Dim strnewrecord As String
    strnewrecord = "SELECT CraftT.Craft, CraftT.Specialty, JobCraftT.Hours,  " _
        & "Trim([department] "& "  , " & [system] & " , " & [asset] & " , " & [component]") AS Task, " _
        & "WorkQueT.SchInd, JobT.JobID, WorkQueT.Summary, WorkT.WorkType, WorkQueT.WorkQueID, JobT.SafeWorkPermit, " _
        & "JobT.HoursPlanned, JobT.ContractorsNeeded, JobT.PartsStatus, JobT.schedule, WorkQueT.ScheduledDate, " _
        & "WorkQueT.ScheduleTime, [6AssetT].Asset" _
        & "FROM (JobCraftT RIGHT JOIN ((((((WorkQueT INNER JOIN 4DepartmentT ON WorkQueT.DeptID = [4DepartmentT].DeptID) " _
        & "INNER JOIN 5SystemT ON WorkQueT.SystemID = [5SystemT].SystemID) INNER JOIN 6AssetT ON WorkQueT.AssetID = [6AssetT].AssetID) " _
        & "INNER JOIN 7ComponentT ON WorkQueT.ComponentID = [7ComponentT].ComponentID) INNER JOIN WorkT ON WorkQueT.WorkID = WorkT.WorkID) " _
        & "INNER JOIN JobT ON WorkQueT.WorkQueID = JobT.WorkQueID) ON JobCraftT.JobID = JobT.JobID) " _
        & "LEFT JOIN CraftT ON JobCraftT.CraftID = CraftT.CraftID " _
        & "WHERE (((JobT.schedule)=True))ORDER BY WorkQueT.SchInd DESC; " _
        Debug.Print (strnewrecord)
    'Me.RecordSource = strnewrecord
    
End Sub

[June7]

An apostrophe or doubled quote marks are usually used to delimit text parameters in filter criteria. You don't have any text fields in the WHERE clause.

However, you are trying to build concatenation of fields which I think requires escaping the & character so the SQL statement will build with the literal character and delimiting the commas with apostrophes.

Try:

"Trim([department] && ', ' && [system] && ', ' && [asset] && ', ' && [component]) AS Task, "

Alternatively, don't concatenate in query, concatenate in textbox.

Why do you need to programmatically change object RecordSource?

Why are you using Trim()?

[vicsaccess]

thanks June7, that's getting me closer, now I just have to find my syntax error. the trim is for the user to view only one text box rather than 4. is there a better way?

[June7]

Alternatively, don't concatenate in query, concatenate in textbox.

Why do you need to programmatically change object RecordSource?

Why are you using Trim()? How does Trim() help with concatenation?

[vicsaccess]

hadn't thought of using the text box. that would be easier. as for using trim(), because up till now that's how I learned to concatenate. i'll change it to the text box and keep it simple. if you were to concatenate programmatically how should it be done? I'm trying to use the command button to change record source from a simple grouped by overview to a more detailed view.

[June7]

Missing a space at end of line:

& "WorkQueT.ScheduleTime, [6AssetT].Asset "

Didn't I already show the programmatic concatenation?

[vicsaccess]

yes, thank you. I miss understood. thought maybe there was a better way to do the concatenation. will move it to the text box and simplify it.

[June7]

You could build a query object and then simply reference the query object name when setting the RecordSource.