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.
Code:
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