CODEWITHSUNDEEP
ms-accessvba
santaaimonce
santaaimonce

Reputation: 144

Syntax Error with DLookup

I can't seem to figure out whats wrong with my code here. It keeps saying that there's a syntax error

Dim NullorNot As Date
NullorNot = Nz(DLookup("[Week]", "[Weeks on Call]", "[Week] = #" & cboWeek.Column(1) & "#"), [#01/01/2000#])

EDIT:

Here is the full code of the Insert Button. The goal of this is that I want to be able to add in an entry for two employees to be on call, one being primary and the other being a backup for a certain two weeks.

Private Sub cmdInsert_Click()
Response = MsgBox("Are you sure you would like to add this form?", [vbYesNoCancel], "Confirm Insert")
If Response = vbYes Then
Dim TempCount As Date
Dim TempCount2 As Date
Debug.Print "[Week] = #" & cboWeek.Column(1) & "#"
TempCount = Nz(DLookup("[Week]", "[Weeks on Call]", "[Week] = #" & cboWeek.Column(1) & "#"), #1/1/2000#)
TempCount2 = Nz(DLookup("[Week]", "[Weeks on Call]", "[Week] = #" & cboWeek2.Column(1) & "#"), #1/1/2000#)
If ((TempCount = #1/1/2000#) And (TempCount2 = #1/1/2000#)) Then
GoTo Method_Run
Else
Dim Msg2 As String
Msg2 = "Employees have already been assigned to be On Call for that date."
Dim title2 As String
title2 = "Duplicate Error"
MsgBox Msg2, [vbOKOnly], title2

End If
Method_Run:
Dim SQL As String
Dim SQL2 As String
On Error GoTo Err_Insert
DoCmd.SetWarnings False
SQL = "INSERT INTO [Weeks on Call]([Primary Employee], [Backup Employee], [Week]) VALUES ('" + cboName.Column(1) + "','" + cboName2.Column(1) + "','" + cboWeek.Column(1) + "')"
SQL2 = "INSERT INTO [Weeks on Call]([Primary Employee], [Backup Employee], [Week]) VALUES ('" + cboName2.Column(1) + "','" + cboName.Column(1) + "','" + cboWeek2.Column(1) + "')"

DoCmd.RunSQL SQL
DoCmd.RunSQL SQL2



Exit_Err:
    Exit Sub

Err_Insert:
If Err.Number = 94 Then GoTo Err_Msg
Err_Msg:
Dim Msg As String
Msg = "One or more of the fields is null, please make sure each field is filled"
Dim title As String
title = "Null Error"
MsgBox Msg, [vbOKOnly], title
End If

If Response = vbNo Then
Response = MsgBox("Entry was not saved", [vbOKOnly], "Not Saved")
End If
If Response = vbCancel Then
Response = MsgBox("Entry was not saved", [vbOKOnly], "Not Saved")
End If

End Sub

Upvotes: 0

Views: 227

Answers (1)

HansUp
HansUp

Reputation: 97101

When you enclose the date value in square brackets, Access interprets it to be an object identifier instead of a literal Date/Time value.

Eliminate the square brackets.

NullorNot = Nz(DLookup("[Week]", "[Weeks on Call]", "[Week] = #" & cboWeek.Column(1) & "#"), #01/01/2000#)

Upvotes: 2

Related Questions