Help with a small query

[Datech]

I want to make a query to display only bugs submitted by the current user. I've put in a text box that holds the value of the UserID, which is tied to the UserName of who submitted the bug. I'm having no luck getting this filter to work correctly though. Here's the SQL statement I'm using, and it's giving me nothing:

SELECT DISTINCTROW tblBugReport.BugNumber, tblBugReport.UserName AS [User], tblBugReport.Summary AS Summary, tblBugReport.Priority AS Priority, tblBugReport.Status AS StatusFROM tblBugReport
GROUP BY tblBugReport.BugNumber, tblBugReport.UserName, tblBugReport.Summary, tblBugReport.Priority, tblBugReport.Status
HAVING (((tblBugReport.UserName)=[Forms]![frmBugs].[txtUserID].[value]));

txtUserID is the text box containing the user name info on the frmBugs form. The code that populates the text box works, the query doesn't. This query populates a list box that displays all the info in a nice little selectable column arrangement. I've made it work so that it will display all the bug report entries, just can't get it right to filter on user type.

Thanks!

[orange]

I've put in a text box that holds the value of the UserID, which is tied to the UserName

txtUserID is the text box containing the user name info on the frmBugs form.

Please show us an example of what exactly is in the txtUserId txtbox, and
what is in UserName in the tblBugReport.

[ssanfu]

SELECT DISTINCTROW tblBugReport.BugNumber, tblBugReport.UserName  AS [User], tblBugReport.Summary AS Summary, tblBugReport.Priority AS  Priority, tblBugReport.Status AS StatusFROM tblBugReport
GROUP BY tblBugReport.BugNumber, tblBugReport.UserName, tblBugReport.Summary, tblBugReport.Priority, tblBugReport.Status
HAVING (((tblBugReport.UserName)=[Forms]![frmBugs].[txtUserID].[value]));

You need a space between Status and FROM


You don't need "Value" >> [Forms]![frmBugs].[txtUserID].[value]
Should be just : [Forms]![frmBugs].[txtUserID]

I want to make a query to display only bugs submitted by the current user. I've put in a text box that holds the value of the UserID, which is tied to the UserName of who submitted the bug. I'm having no luck getting this filter to work correctly though.
<snip>

<snip>
txtUserID is the text box containing the user name info on the frmBugs form. The code that populates the text box works, the query doesn't. This query populates a list box that displays all the info in a nice little selectable column arrangement. I've made it work so that it will display all the bug report entries, just can't get it right to filter on user type.

Why are you using a totals query when you are not using an aggregate function?
You might try:

SELECT DISTINCTROW tblBugReport.BugNumber, tblBugReport.UserName  AS [User], tblBugReport.Summary AS Summary, tblBugReport.Priority AS  Priority, tblBugReport.Status AS Status
FROM tblBugReport
WHERE tblBugReport.UserName = [Forms]![frmBugs].[txtUserID]
ORDER BY tblBugReport.BugNumber, tblBugReport.UserName, tblBugReport.Summary, tblBugReport.Priority, tblBugReport.Status

My $0.02

[Datech]

orange, an example of txtUserID is "dvanbeek", and UserName is "dvanbeek". That way I pull up all the BugNumber (ID for tblBugReports) where the associated UserName is equal to the logged in user. The logged in user's UserName is stored in the txtUserID field after a With statement sets a UserInfo variable in a recordset in the LogInForm. I know y'all don't have visibility for half that stuff, but I didn't think it was needed at first.

The code that worked was:

SELECT DISTINCTROW tblBugReport.BugNumber, tblBugReport.UserName AS [User Name], tblBugReport.Summary AS Summary, tblBugReport.Priority AS Priority, tblBugReport.Status AS StatusFROM tblBugReport
WHERE (((tblBugReport.UserName)=[Forms]![frmBugs]![txtUserID].[value]));

The '.value' was apparently necessary (feel free to tell me why), and I should have used a WHERE instead of a HAVING. The lack of a space between the Status and FROM was a copy/paste error I believe. This is solved, but feel free to comment on the .value and the difference between a WHERE and HAVING if you want to educate me and any lurkers.

[orange]

I think Steve has the answer.

I thought .value was the default ????

HAVING see http://www.techonthenet.com/sql/having.php after aggregation
Where is used before aggregation

Glad this is resolved.