'" & [strvalue] & "' - Error Why???

[CharlesWhiteman]

Hi All, I am at a loss as to why my section of code is returning a Syntax Error INSERT INTO runtime error 3134?

Dim strSQLHistory As String
    strSQLHistory = "INSERT INTO TblPrimaryDataHistory (PrimaryDataID, DateTime, HistoryNote, HistoryOutcome)"
    strSQLHistory = strSQLHistory & " VALUES(PrimaryDataID, '" & [strRegDate] & "', HistoryNote, HistoryOutcome)"
            
            'DoCmd.SetWarnings False
    DoCmd.RunSQL strSQLHistory
            'DoCmd.SetWarnings True

 

[boblarson]

You're off on your quotes, control references, and date delimiter and I think you need to put the field DateTime into square brackets since it is a reserved word in Access (you shouldn't use those for object or field names):

    strSQLHistory = "INSERT INTO TblPrimaryDataHistory (PrimaryDataID, [DateTime], HistoryNote, HistoryOutcome)"
    strSQLHistory = strSQLHistory & " VALUES([B][COLOR=red]" & Me.[/COLOR][/B]PrimaryDataID & ", [COLOR=red][B]#[/B][/COLOR]" & [strRegDate] & "[B][COLOR=red]#,'" & Me.[/COLOR][/B]HistoryNote [B][COLOR=red]& "','" & Me.[/COLOR][/B]HistoryOutcome [COLOR=red][B]& "'[/B][/COLOR])"

So that is my guess (not completely sure about the HistoryNote and HistoryOutcome if those are on your form).

 

[gemma-the-husky]

if its a DATE (strregdate) it needs surrounding by # characters, not quote marks

and note if you are in the UK, you may need to handle ambiguity between UK/US dates, within SQL statements

------------
trap the error anyway - then you will find out what the issue is!

 

[boblarson]

and note if you are in the UK, you may need to handle ambiguity between UK/US dates, within SQL statements

Good point, I frequently forget about that because I don't have to worry about it.

 

[MarkK]

Alternatively, you can leverage DAO to do the delimiters for you, since it can know what type the columns are if you create a QueryDef ...

dim qdf as dao.querydef
set qdf = currentdb.createquerydef("", _
  "INSERT INTO TblPrimaryDataHistory " & _
    "( PrimaryDataID, [DateTime], HistoryNote, HistoryOutcome ) " & _
  "VALUES " & _
    "( p0, p1, p2, p3 )"
with qdf
  .parameters(0) = Me.PrimaryDataID
  .parameters(1) = strRegDate
  .parameters(2) = Me.HistoryNote
  .parameters(3) = Me.HistoryOutcome
  .execute dbfailonerror
end with

I agree that this is more code, but it is very clean and very reliable.
Particularly compared to ...

Me.PrimaryDataID & ", #" & [strRegDate] & "#,'" & _
Me.HistoryNote & "','" & Me.HistoryOutcome & "')"

 

[CharlesWhiteman]

Thanks for all the replies, Bob Lagbolt and Gemma, Irs rather strange as even though its a date as I've previously dim'd it as a string I've never had any issues like that. Will look at further but in the interim period have doen a workaround.