Syntax error in SQL string?

[smaier]

Hello, all!



The purpose of what I'm working on is to be able to audit machine preventative maintenance logs. People fill out a form saying they performed certain tasks maintaining a machine, and later someone else needs to see if these tasks were done and when. The process I am having problems with involves programmatically identifying weekends and holidays out of a range of dates.

The below code works fine (will label weekend dates as such or leave the field blank with the Comment variable) until I un-comment the if/then statement that includes "New Year's Day". At that point I get an error '3075' "Syntax error (missing operator) in query expression "New Year's Day')', and the debugger hilights the DoCmd.RunSQL strSQL2 line of code. I'm thinking there's a formatting/syntax error in the strSQL statement (probably at the end). I'm horrible with single/double quotation marks so I'm currently staring blankly at them.

Private Sub Command4_Click()

Dim MyDate, MyWeekday, MyYear
Dim StartDayOfYear As Integer
Dim EndDayOfYear As Integer
Dim StartDayEndDayDifference As Integer
Dim DayOfYear As Integer
Dim DateToCheck As Date
Dim Comment As String


Dim strSQL As String
Dim strSQL2 As String




MyDate = Date 'Establishes MyDate as the current system date
MyYear = year(MyDate) 'Returns the 4 digit value of the current year
StartDayOfYear = DatePart("y", StartDateTextBox) 'Returns the numeric day-of-year value of the StartDateTextBox control
EndDayOfYear = DatePart("y", EndDateTextBox) 'Returns the numeric day-of-year value of the EndDateTextBox control
StartDayEndDayDifference = DateDiff("d", StartDateTextBox, EndDateTextBox) 'Calculates the difference, in days, between StartDateTextBox and EndDateTextBox
DateToCheck = DateSerial(MyYear, 1, StartDayOfYear) 'Finds the traditional date expression that represents the numeric day-of-the-year value of the StartDayOfYear variable
'MyWeekday = Weekday(DateToCheck) 'Returns the numeric value where Sunday = 1 and so on until Saturday = 7 and assigns that value to the Myweekday variable


strSQL = "DELETE FROM MissingDatesTable;"




DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True


For x = StartDayOfYear To EndDayOfYear
    MyWeekday = Weekday(DateToCheck)
    DayOfYear = DatePart("y", DateToCheck)
    If MyWeekday = 1 Or MyWeekday = 7 Then
        Comment = "Weekend"
    Else
        Comment = ""
    End If
    
    'If DayOfYear = 1 Then
    '    Comment = "New Year's Day"
    'Else
    '    Comment = ""
    'End If
    
    If DCount("[ID]", "DoosanPMLogData", "[PerformedOn] = #" & DateToCheck & "#") = 0 Then
        strSQL2 = "INSERT INTO MissingDatesTable (MissingDates, Comment) VALUES ('" & DateToCheck & "', '" & Comment & "')"
        MsgBox strSQL2
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL2
        DoCmd.SetWarnings True
    End If
    Comment = ""
    DateToCheck = DateToCheck + 1
Next x


Me.MissingDatesListBox.Requery

Any help would be greatly appreciated!


End Sub

[kd2017]

Here's your problem

Comment = "New Year's Day"
...
strSQL2 = "INSERT INTO MissingDatesTable (MissingDates, Comment) VALUES ('" & DateToCheck & "', '" & Comment & "')"

You need to escape the single quote in the New Year's Day string OR use double quotes in the sql statement instead of the single quotes.

Try this instead:

strSQL2 = "INSERT INTO MissingDatesTable (MissingDates, Comment) VALUES ('" & DateToCheck & "', """ & Comment & """)"

[EDIT]
Also, when you're using dates in a ms access sql you surround them with # rather than quotes

strSQL2 = "INSERT INTO MissingDatesTable (MissingDates, Comment) VALUES (#" & DateToCheck & "#, """ & Comment & """)"

[smaier]

kd2017;

Worked like a charm, thank you!

I really should work more at this rather than avoid it or I'll never be comfortable with it. Also, good tip on the pound signs in SQL queries as well. I'm used to using them in domian and Access queries, but never occurred to me to use them in SQL queries as well.

Thank you!

[kd2017]

kd2017;

Worked like a charm, thank you!

Happy to help!

Also, good tip on the pound signs in SQL queries as well. I'm used to using them in domian and Access queries, but never occurred to me to use them in SQL queries as well.

Note that the # notation is specific to microsoft access's flavor of sql.