Build Query from Array
My problem was that I needed to step across multiple fields in a table and pick up each field once and move on. The first few attempts made a file of hundreds of lines of code and was impossible to fix errors consistently.
SpecAwd = Array("Grand", "ReserveGrand", "SingleGrand", "SingleReserve", "GeorgeBrettCup",
"APS_Pre1900","APS_Post1980","ReseachMedal", "AAPE_Gold")
For G = LBound(SpecAwd) To UBound(SpecAwd)
If DCount("[ExhibitID]", "tblSpecialAwards", "[" & SpecAwd(G) _
& "]=true AND [ShowYear] = '" & ExYear & "'") = 0 Then
GoTo AwErr
Else
Print #1, vbCrLf & " ====== " & SpecAwd(G) & " ============"
SQLstr = "SELECT ExhNameFirst([tblExhibitors]![ID]) AS Name, tblExhibits.Title,"
SQLstr = SQLstr & " tblSpecialAwards.ShowYear, tblExhibits.ExhingYr, tblSpecialAwards."
SQLstr = SQLstr & " & SpecAwd(G) & vbCrLf
SQLstr = SQLstr & " FROM tblExhibitors LEFT JOIN (tblExhibits LEFT JOIN "
SQLstr = SQLstr & " tblSpecialAwards ON tblExhibits.ID = tblSpecialAwards.ExhibitID)"
SQLstr = SQLstr & " ON tblExhibitors.ID = tblExhibits.ExhibitorID" & vbCrLf
SQLstr = SQLstr & " WHERE (((tblSpecialAwards.ShowYear)=" & QUO & ExYear & QUO &
SQLstr = SQLstr & " ")) AND ((tblExhibits.ExhingYr)=" & QUO & ExYear & QUO & ") "
SQLstr = SQLstr & " AND ((tblSpecialAwards.[" & SpecAwd(G) & "])=True);"
' Debug.Print "Award= " & SpecAwd(G)
' Debug.Print SQLstr
Set EXH = DB.OpenRecordset(SQLstr, dbOpenDynaset)
EXH.MoveFirst
Do While Not EXH.EOF
Print #1, EXH!Name & " ~~~~~ " & EXH!Title
EXH.MoveNext
Major:
Loop
End If
AwErr:
Next G
The generated query:
SELECT ExhNameFirst([tblExhibitors]![ID]) AS Name, tblExhibits.Title, _
tblSpecialAwards.ShowYear, tblExhibits.ExhingYr, tblSpecialAwards.PostalHistorySoc
FROM tblExhibitors LEFT JOIN (tblExhibits LEFT JOIN tblSpecialAwards _
ON tblExhibits.ID = tblSpecialAwards.ExhibitID) ON tblExhibitors.ID = tblExhibits.ExhibitorID
WHERE (((tblSpecialAwards.ShowYear)="2025")) AND ((tblExhibits.ExhingYr)="2025") _
AND ((tblSpecialAwards.[PostalHistorySoc])=True);
See the resulting query above.
|