Compile Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
You might need to double up on the quotation marks as below:
"where Year([Submit_Date])=" & strPrompt & " AND [Assigned
Team
Member] in (""Open"", ""Closed"")"
Click to expand...

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

Tom Lake said:
You might need to double up on the quotation marks as below:
"where Year([Submit_Date])=" & strPrompt & " AND [Assigned
Team
Member] in (""Open"", ""Closed"")"
Click to expand...

Tom Lake
Click to expand...
 
Back
Top