Query assistance (1 Viewer)

[mr moe]

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

 

[arnelgp]

use "Where" instead of "Having". having is used only when you are in Total Query.

 

[mr moe]

use "Where" instead of "Having". having is used only when you are in Total Query.

hi thanks for your help, im still getting the same issue, it appears to be a punctuation error but i can't fiqure it out still

 

[mr moe]

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

thanks i fixed it, i needed to put a space before the from and having.