Compile Error

[Max]

I have the above error on the word "Open"?

Private Sub Command16_Click()
Dim qdfCurr As DAO.QueryDef
Dim strPrompt As String
Dim strSQL As String
Dim Crei As String
'Input the random number of Year you want access
'by changeing the Step1_Member_Status query
strPrompt = InputBox("Enter Year in YYYY format.", "Required Data")

If Len(strPrompt) > 0 Then
If IsNumeric(strPrompt) Then
strSQL = "select * from Requests " & _
"where Year([Submit_Date])=" & strPrompt & " AND [Assigned Team
Member] in ("Open", "Closed")"


Set qdfCurr = CurrentDb().QueryDefs("Step1_Member_Status")
qdfCurr.SQL = strSQL
End If
End If

If Check_Records Then
DoCmd.OpenReport "Step1_Status_Summary", acViewPreview
Else
MsgBox "There are no records to view", vbOK, "Error"
End If
End Sub

 

[Jeff Boyce]

Max

What happens if you "lift" that SQL statement out of code and use it to
build a query in query design view? Does it work there?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tom Lake]

You might need to double up on the quotation marks as below:

"where Year([Submit_Date])=" & strPrompt & " AND [Assigned
Team
Member] in (""Open"", ""Closed"")"

Tom Lake

 

[Max]

"where Year([Submit_Date])=" & strPrompt & " AND [Status] in ('Open',
'HOLD','IN-PROCESS','UNDER-REVIEW')"

You might need to double up on the quotation marks as below:

"where Year([Submit_Date])=" & strPrompt & " AND [Assigned
Team
Member] in (""Open"", ""Closed"")"

Tom Lake