Need help with forming a Query

[mrdod]

I have a list box full of audits. This information can be drilled down by location/area and whether the it is New , Assigned (a), or Complete (c). The user pushes buttons on the form and text boxes are updated with a 1 or a 0. When it is 1 the button is pushed. So if n=1 a=1 and c=1 then the user want to see all the audits for that area. My problem is even though I have selected an area I am getting other areas to populate my list box. I am by no means an SQL guru so any help/suggestions would be wonderful! Here is just part of my code, I can post more if what I posted is not helping.

If intn = 1 And inta = 1 And intc = 1 Then
With Me.lbhistory
.RowSource = ""
strSQL = "SELECT * FROM tblhistory" & _
" WHERE([Status]='New' OR [Status]='Assigned' OR [Status]='Complete' AND [Area Audited]=""" & strarea & """ );"
.RowSource = ""
.RowSource = strSQL
.Requery
End With


I have all my endifs at the end of the code.

Thanks for any help you could provide.

 

[dhookom]

I think you would want code like:

strSQL = "SELECT * FROM tblhistory" & _
" WHERE([Status]IN ('New' ,'Assigned','Complete') AND [Area Audited]=""" & strarea & """ );"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Instead of a lot of If:

Me!lbhistory.Rowsource = "SELECT * FROM tblhistory WHERE Status In (" _
 & "'" & IIf(intn = 1, "New", "?") & "'," _
 & "'" & IIf(inta = 1, "Assigned", "?") & "'," _
 & "'" & IIf(intc = 1, "Complete", "?") & "')" _
 & " AND [Area Audited]='" & strarea & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886