Concatenate in a VBA "INSERT INTO" script

[Haagimus]

I am trying to update an older MS Access 2010 VBA script that is embedded into a button click. This button will update another Access table for another squadron. I need to change a couple things around however because my update will basically break this script by not sending all the required information.

What needs to be done is there is a "Top3Comments" and a "Call Sign" entry that have their own boxes. What I have updated is now requiring the "Top3Comments" to be concatenated with the mission commander "Commander" and the "Call Sign" to be concatenated with "C/S_Number". I need the combined output of both of those to output to a single cell in the table.

I am assuming that I will just use a JOIN function after calling all variables into a Dim? I am not really sure how to accomplish this though. Anyone have any ideas.

Private Sub Btn_Update_Top_3_Buddy_Click()
'When you click the Update button, this procedure will delete the selected day's comments in the [TOP 3 LOG Table]
'And then add the current mission comments to the [TOP 3 LOG Table].
On Error GoTo Err_Btn_Report_Click
 
    Dim DeleteQuery As String
    Dim InsertQuery As String
 
 
    DoCmd.SetWarnings False
    DeleteQuery = "DELETE [TOP 3 LOG Table].Squadron, [TOP 3 LOG Table].Date FROM [TOP 3 LOG Table] " _
                  & "WHERE (((([TOP 3 LOG Table].Squadron='489 RS')OR([TOP 3 LOG Table].Squadron='427 RS')OR([TOP 3 LOG Table].Squadron='306 IS')))" _
                  & "AND (([TOP 3 LOG Table].Date)=#" & Forms![Ops Sup]![Date_SITREP] & "#));"
 
 
    DoCmd.RunSQL DeleteQuery
 
    InsertQuery = "INSERT INTO [TOP 3 LOG Table] ( [Date], Squadron, Top3Name, Top3Comments, " _
                & "[Line#], Aircraft, Block, SortieType, [NE/CNX], Reason, FlightTime, [Call Sign], [Tail#]) " _
                & "SELECT Missions.Date_Mission, Missions.Squadron, Missions.Top3Name, Missions.Top3Comments, " _
                & "Missions.[Line#], Missions.Aircraft, Missions.Block, Missions.[Sortie Type], Missions.[NE/CNX], " _
                & "Missions.Reason, Missions.FlightTime, Missions.[Call Sign], Missions.[Tail#] " _
                & "FROM Missions " _
                & "WHERE (((Missions.Date_Mission)= #" & Forms![Ops Sup]![Date_SITREP] & "#));"
 
    DoCmd.RunSQL InsertQuery
    DoCmd.SetWarnings True
 
    MsgBox ("Top 3 Buddy updated.")
 
Exit_Btn_Report_Click:
    Exit Sub
 
Err_Btn_Report_Click:
    DoCmd.SetWarnings True
    MsgBox Err.Description
 
End Sub

This is the current code that I have.

[zmbd]

What needs to be done is there is a "Top3Comments" and a "Call Sign" entry that have their own boxes. What I have updated is now requiring the "Top3Comments" to be concatenated with the mission commander "Commander" and the "Call Sign" to be concatenated with "C/S_Number". I need the combined output of both of those to output to a single cell in the table.

Respectfully, why are you denormalizing your database?
What happens with the callsign changes... I have known this to happen in the Navy.

Should be no need for VBA nor to denormalize your database:
- Start out with a simple select query in the query designer.
- Make sure that this pull all of the original data that you desire.
- Now add a calculated field that will create your new value:nameoffield: [firstfield] & [secondfield] this would adjoin the two fields without space between.
- Once this is done and the results are what you need.
- At this point you haven't altered your database; however, you now have the new values you are after.

-!- IF you insist on denormalizing your database at this point, then you can base an insert query from the designer on this new query.

Also it is not best practice to use a backslash in the field names... as you are doing an update, this might be the time to put the effort into changeing everything that refers to such.
-alphanumeric:
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access