Query to Return Only Records Satisfying Certain Multiple Criteria

[Egan]

I have two tables with a one-to-many relationship. Each record in table “A” is a make of car and table “B” contains the car’s many attributes (e.g., color, engine, etc.) I would like to have a query based on multiple criteria and have returned only those records meeting that criteria. I have used the “AND” connector in a sql statement . However, it returns not only records containing all the search terms, but also records only partially satisfying the criteria. For instance, if my search terms are red and convertible, the query with also return hits for red cars with hardtops. Is there a solution that does not require too much complexity? TIA.

[June7]

The hardtop records should not be retrieved. The query is not structured properly. Post the sql statement for analysis.

[Egan]

The hardtop records should not be retrieved. The query is not structured properly. Post the sql statement for analysis.

June7, thank you for taking an interest in my post. I am a causal user of Access and do not have the knowledge base to work out these problems by myself.

Each vehicle in Table A has its own unique identifier. The unique identifier is stored in Table B and is associated with each individual record for the vehicle’s various attributes. In my database, Table B is named Item_TagTbl and I’m using the label “Tags” as the column heading for the attributes. I have a form containing a listbox with the attributes. The selected attributes end up in a temporary table called TempTag2Tbl. The function containing the sql statement has a “Do Until End of File” loop to cull the attributes from the temporary table. The loop creates a string variable named “Str” containing the selected attributes concatenated with the sql “AND” connector. The function then concatenates the “Str” variable to the end of the sql statement resulting in the following string: “"SELECT * FROM Item_TagTbl WHERE Tags =" & Str & ";" The function then changes the form’s recordsource to the sql statement. Below is the VBA code for the function. Note: while the sql statement I’ve described uses an asterisk I would specify the fields in a working version of the function to populate the form.

Private Sub TestBtn_Click()
Dim Mydb As Database, Myrec As Recordset, Str As String

Set Mydb = CurrentDb
Set Myrec = Mydb.OpenRecordset("TempTag2Tbl", dbOpenDynaset)

Myrec.MoveFirst
Str = "'" & Myrec("TempTag") & "'"
Myrec.MoveNext

Do Until Myrec.EOF
Str = Str & " AND " & "'" & Myrec("TempTag") & "'"
Myrec.MoveNext
Loop

Me.RecordSource = "SELECT * FROM Item_TagTbl WHERE Tags =" & Str & ";"

End Sub

[June7]

I don't understand need for a temp table. TempTag is field in the recordset? This field holds field name?

I don't understand the {Tags =}.

The constructed string probably should be one of the following syntax:

1. [fieldname1]="value1" And [fieldname2]="value2" And [fieldname3]="value3"

2. [fieldname1] In ("value1", "value2", "value3")

Review:

http://allenbrowne.com/ser-50.html

Be sure to also look at the link in item 5 in the Notes at the bottom of that article.

Debug. Review link at bottom of my post for guidelines. Use a breakpoint and Debug.Print to examine the constructed string.

[Egan]

June7, thank you for your analysis and link to Allen Browne’s page. As for the temp table, I was using a “for next loop” to retrieve the selected criteria from the listbox and storing the selections in the temp table before moving on to constructing the sql statement. I believe your point is that I should have been building the sql statement at the same time as I was looping through the listbox.

[June7]

Yes, that would be more efficient. This is solved?

[Egan]

Yes. Thanks again for your help. Egan.