hi guys i'm getting an error msg number 3141 and been trying with no luck. Could anybody please help. below is the code.
Dim RS As DAO.Recordset
Set RS = CurrentDb.OpenRecordset("SELECT Count(request_tbl.request_tm_num) AS Ticket" & _
"FROM (SELECT DISTINCT request_tm_num, analyst_id FROM recovery_request_tbl) AS my_count INNER JOIN user_tbl ON my_count.analyst_id = user_tbl.UserID" & _
"HAVING (((user_tbl.UserID)=IIf('" & [Forms]![search_frm]![user_id] & "'='All',[user_tbl]![UserID],'" & [Forms]![search_frm]![user_id] & "')));")
If RS.RecordCount > 0 Then
Me.tbx2 = RS.Fields(0)
Else
Me.tbx2 = 0
End If
Set RS = Nothing
Dim RS As DAO.Recordset
Set RS = CurrentDb.OpenRecordset("SELECT Count(request_tbl.request_tm_num) AS Ticket" & _
"FROM (SELECT DISTINCT request_tm_num, analyst_id FROM recovery_request_tbl) AS my_count INNER JOIN user_tbl ON my_count.analyst_id = user_tbl.UserID" & _
"HAVING (((user_tbl.UserID)=IIf('" & [Forms]![search_frm]![user_id] & "'='All',[user_tbl]![UserID],'" & [Forms]![search_frm]![user_id] & "')));")
If RS.RecordCount > 0 Then
Me.tbx2 = RS.Fields(0)
Else
Me.tbx2 = 0
End If
Set RS = Nothing