Re: Wanting help regarding dynamic queries in MS-Access
Date: 1996/11/12
Message-ID: <01bbd091$34e6d900$39a30fcb_at_dialup.ozemail.com.au>
The best way to do this is to build the SQL string on the fly. The following example is from VB4 but is still applicable (sorry it's a bit complex but it's the only example I had on hand).
This example takes parameters from a grid and places them in an array for further processing then builds the SQL string, including creating multiple table joins and where conditions.
Sub cmdResults_Click()
On Error GoTo Err_cmdResults_Click
Dim strSQL As String
Dim i As Integer
Dim bytNumQuals As Byte
Dim audtMandCriteria() As SearchCriteria
Dim alngOptCriteria() As Long
Dim bytNumMandQuals As Byte
Dim bytNumOptQuals As Byte
Dim snpSkillSearchResults As Recordset
Dim lngQualID As Long
Dim intRatingTemp As Integer
Dim lngPersonID As Long
Dim strName As String
Dim strCompany As String
Dim strMandScore As String
Dim strOptScore As String
'Dim strTotalScore As String
Dim strReqd As String
Dim fMand As Boolean
Dim fOpt As Boolean
Dim intMandScore As Integer
Dim intMandScoreReqd As Integer
Dim intOptScore As Integer
'clear the grid of any existing results
lstResult.Clear
'read the selected qualification into an array for further processing
'find the total number of qualifications selected
bytNumQuals = frmSkillSearch.sdgQualifications.Rows
'load data from the grid into the array
For i = 0 To bytNumQuals - 1
frmSkillSearch.sdgQualifications.Row = i
'is this a Mandatory criteria
If frmSkillSearch.sdgQualifications.Columns(3).Value Then
'resize the Mandatory counter and array (NB 1 based)
bytNumMandQuals = bytNumMandQuals + 1
ReDim Preserve audtMandCriteria(1 To bytNumMandQuals)
audtMandCriteria(bytNumMandQuals).QualID =
frmSkillSearch.sdgQualifications.Columns(7).Value
audtMandCriteria(bytNumMandQuals).Rating =
frmSkillSearch.sdgQualifications.Columns(8).Value
audtMandCriteria(bytNumMandQuals).Years =
Val(frmSkillSearch.sdgQualifications.Columns(4).Value)
Else
'resize the Optional counter and array (NB 1 based)
bytNumOptQuals = bytNumOptQuals + 1
ReDim Preserve alngOptCriteria(1 To bytNumOptQuals)
alngOptCriteria(bytNumOptQuals) =
frmSkillSearch.sdgQualifications.Columns(7).Value
End If
Next i
'there must be at least one Mandatory qualification
If bytNumMandQuals <= 0 Then
MsgBox "You must select at least one Mandatory Qualification.", 48, "Select Mandatory Qualification"
Exit Sub
End If
'fields to be selected and start of FROM clause
strSQL = "SELECT DISTINCTROW People.PersonID, People.FirstName,
People.Surname, Companies.CompanyName, CompaniesDepartments.Department,
People.RateHourly, People.RateAnnual, PeopleQualifications.QualificationID,
PeopleQualifications.Rating FROM (Companies INNER JOIN CompaniesDepartments
ON Companies.CompanyID = CompaniesDepartments.CompanyID) INNER JOIN "
'add opening brackets for each additional join
'add one PeopleQualifications table to show results and one for
selection criteria
strSQL = strSQL & String$(bytNumMandQuals + 1, "(") & "People INNER JOIN PeopleQualifications ON People.PersonID = PeopleQualifications.PersonID) INNER JOIN PeopleQualifications AS PeopleQualifications_1 ON People.PersonID = PeopleQualifications_1.PersonID"
'add other PeopleQualifications tables for additional criteria
For i = 2 To bytNumMandQuals
strSQL = strSQL & ") INNER JOIN PeopleQualifications AS PeopleQualifications_" & i & " ON People.PersonID = PeopleQualifications_" & i & ".PersonID"
Next i
'finish off the FROM clause
strSQL = strSQL & ") ON CompaniesDepartments.DepartmentID =
People.DepartmentID"
'start the WHERE clause and add criteria for first QualificationID
strSQL = strSQL & " WHERE (((PeopleQualifications_1.QualificationID)="
& audtMandCriteria(1).QualID & ")"
'add criteria for first Rating and Year only if value > 0
If audtMandCriteria(1).Rating > 0 Then
strSQL = strSQL & " AND ((PeopleQualifications_1.Rating)>=" & audtMandCriteria(1).Rating & ")"
End If
If audtMandCriteria(1).Years > 0 Then
strSQL = strSQL & " AND ((PeopleQualifications_1.Years)>=" & audtMandCriteria(1).Years & ")"
End If
For i = 2 To bytNumMandQuals
'add criteria for other QualificationID's
strSQL = strSQL & " AND ((PeopleQualifications_" & i &
".QualificationID)=" & audtMandCriteria(i).QualID & ")"
'add criteria for first Rating and Year only if value > 0
If audtMandCriteria(i).Rating > 0 Then
strSQL = strSQL & " AND ((PeopleQualifications_" & i &
".Rating)>=" & audtMandCriteria(i).Rating & ")"
End If
If audtMandCriteria(i).Years > 0 Then
strSQL = strSQL & " AND ((PeopleQualifications_" & i &
".Years)>=" & audtMandCriteria(i).Years & ")"
End If
Next i
'add closing bracket for WHERE clause, ORDER BY clause and closing ;
for SQL
strSQL = strSQL & ") ORDER BY People.PersonID;"
'open the results recordset
Set snpSkillSearchResults = gDB.OpenRecordset(strSQL, dbOpenSnapshot)
'check that at least one record was found
If snpSkillSearchResults.EOF And snpSkillSearchResults.BOF Then
MsgBox "No candidates were found for the Qualifications requested. Reduce the some of the Ratings and try again.", 48, "No Candidates Found"
Exit Sub
End If
'find the minimum score required for the mandatory requests
For i = 1 To bytNumMandQuals
If audtMandCriteria(i).Rating > 0 Then
intMandScoreReqd = intMandScoreReqd +
audtMandCriteria(i).Rating
Else
intMandScoreReqd = intMandScoreReqd + 3
End If
Next i
'calculate the percentages for each person and fill list
'load temporary variables
lngPersonID = snpSkillSearchResults!PersonID
strName = snpSkillSearchResults!Surname & ", " &
snpSkillSearchResults!FirstName
strCompany = snpSkillSearchResults!CompanyName & ", " + snpSkillSearchResults!Department
strReqd = Format$(0 & snpSkillSearchResults!RateAnnual, "$#,###;;-")
Do Until snpSkillSearchResults.EOF
'if this is a new person then add the previous person's results to the list view
If lngPersonID <> snpSkillSearchResults!PersonID Then
strMandScore = Format$(intMandScore / intMandScoreReqd, "#%")
strOptScore = Format$(intOptScore)
'strTotalScore = Format$(intMandScore + intOptScore)
'add person to results form
AddResult lngPersonID, strMandScore, strName, strCompany,
strOptScore, strReqd
'reset variables for this person
lngPersonID = snpSkillSearchResults!PersonID
strName = snpSkillSearchResults!Surname & ", " &
snpSkillSearchResults!FirstName
strCompany = snpSkillSearchResults!CompanyName & ", " +
snpSkillSearchResults!Department
strReqd = Format$(0 & snpSkillSearchResults!RateAnnual,
"$#,###;;-")
intMandScore = 0
intOptScore = 0
End If
lngQualID = snpSkillSearchResults!QualificationID
intRatingTemp = 0 & snpSkillSearchResults!Rating
If intRatingTemp = 0 Then intRatingTemp = 3
'is this a mandatory skill
fMand = False
For i = 1 To bytNumMandQuals
If lngQualID = audtMandCriteria(i).QualID Then
fMand = True
Exit For
End If
Next i
If fMand = False Then
'is this an optional skill
fOpt = False
For i = 1 To bytNumOptQuals
If lngQualID = alngOptCriteria(i) Then
fOpt = True
Exit For
End If
Next i
End If
If fMand Then
intMandScore = intMandScore + intRatingTemp
ElseIf fOpt Then
intOptScore = intOptScore + intRatingTemp
End If
snpSkillSearchResults.MoveNext
Loop
'add the last person to results form
strMandScore = Format$(intMandScore / intMandScoreReqd, "#%") strOptScore = Format$(intOptScore)
'strTotalScore = Format$(intMandScore + intOptScore)
AddResult lngPersonID, strMandScore, strName, strCompany, strOptScore, strReqd
'show the results list
sdgQualifications.Visible = False
lstResult.Visible = True
For i = 0 To 4
lblResult(i).Visible = True
Next i
lstResult.SetFocus
cmdQualifications.Visible = True
cmdResults.Visible = False
Exit_cmdResults_Click:
Exit Sub
Err_cmdResults_Click:
Call frmError.ShowDialog("frmSkillSearch", "cmdResults_Click", Err, Now())
Resume Exit_cmdResults_Click
End Sub
E. Rijk <egonrijk_at_concepts.nl> wrote in article <01bbcf52$0b240b00$b40786c2_at_egonrijk>...
> I want some help regarding dynamic queries in MS-Access. > I want to make a query where people can use one or more parameters as a > criterium for the query. The parameters are entered in a form showing the > several fields. If just one parameter is entered in a field, then the > query should be limited to this field. If more than one field is filled > in, then the query should consider these fields using the 'AND' operator. > The problem is that the query is not allowed to look at the fields that > are left blank. I solved it with the following construction (see later), > which works well. > > This subroutine evaluates the contents of each field and assigns then a > value to the several parameters. These are then summoned. This sum isthen
> used to choose via a 'SELECT CASE' instruction the right query. > Although this works well, the amount of code rapidly increases when the > amount of fields is increased (2 to the power n, n being the amount of > fields). > > Surely, there must be another way to do this, BUT HOW??? > Any help is highly appreciated! > --------------------------------------------------------------- > > Sub Knop17_Click () > On Error GoTo Err_Knop17_Click > > Dim DocName As String > Dim LinkCriteria As String > Dim BU As Integer > Dim PL As Integer > Dim PT As Integer > Dim PF As Integer > Dim Sum As Integer > > > DocName = "frm_PortfolioView" > > 'The next four statements evaluate the contents > 'of each field > > If Not IsNull(Forms![frm_Zoek1]![BU]) Then > BU = 1 > End If > If Not IsNull(Forms![frm_Zoek1]![Projekttype]) Then > PT = 2 > End If > If Not IsNull(Forms![frm_Zoek1]![Projektleider]) Then > PL = 8 > End If > If Not IsNull(Forms![frm_Zoek1]![Ontwikkelingsfase]) Then > PF = 4 > End If > > 'The result of the evaluation gives a unique sum > 'for each of the possible combinations > > Sum = BU + PT + PL + PF > > 'The sum is then used to choose from the right query > > Select Case Sum > Case 0 > KnopMsgBox = MsgBox("You didn't enter any values!" & Chr$(13) & > Chr$(10) & "All records will be shown." & Chr$(13) & Chr$(10) & "Is this > what you want?", 1) > If KnopMsgBox = 2 Then > Exit Sub > End If > Case 1 > LinkCriteria = "[BU-groep] = Forms![frm_Zoek1]![BU]" > Case 2 > LinkCriteria = "[Projekttype] = Forms![frm_Zoek1]![Projekttype]" > Case 3 > LinkCriteria = "[BU-groep] = Forms![frm_Zoek1]![BU] AND > [Projekttype] = Forms![frm_Zoek1]![Projekttype]" > Case 4 > LinkCriteria = "[Ontwikkelingsfase] = > Forms![frm_Zoek1]![Ontwikkelingsfase]" > Case 5 > LinkCriteria = "[BU-groep] = Forms![frm_Zoek1]![BU] AND > [Ontwikkelingsfase] = Forms![frm_Zoek1]![Ontwikkelingsfase]" > Case 6 > LinkCriteria = "[Projekttype] = Forms![frm_Zoek1]![Projekttype] > AND [Ontwikkelingsfase] = Forms![frm_Zoek1]![Ontwikkelingsfase]" > Case 7 > LinkCriteria = "[BU-groep] = Forms![frm_Zoek1]![BU] AND > [Projekttype] = Forms![frm_Zoek1]![Projekttype] AND [Ontwikkelingsfase] = > Forms![frm_Zoek1]![Ontwikkelingsfase]" > Case 8 > LinkCriteria = "[Projektleider] = > Forms![frm_Zoek1]![Projektleider]" > Case 9 > LinkCriteria = "[BU-groep] = Forms![frm_Zoek1]![BU] AND > [Projektleider] = Forms![frm_Zoek1]![Projektleider]" > Case 10 > LinkCriteria = "[Projekttype] = Forms![frm_Zoek1]![Projekttype] > AND [Projektleider] = Forms![frm_Zoek1]![Projektleider]" > Case 11 > LinkCriteria = "[BU-groep] = Forms![frm_Zoek1]![BU] AND > [Projekttype] = Forms![frm_Zoek1]![Projekttype] AND [Projektleider] = > Forms![frm_Zoek1]![Projektleider]" > Case 12 > LinkCriteria = "[Ontwikkelingsfase] = > Forms![frm_Zoek1]![Ontwikkelingsfase] AND [Projektleider] = > Forms![frm_Zoek1]![Projektleider]" > Case 13 > LinkCriteria = "[BU-groep] = Forms![frm_Zoek1]![BU] AND > [Ontwikkelingsfase] = Forms![frm_Zoek1]![Ontwikkelingsfase] AND > [Projektleider] = Forms![frm_Zoek1]![Projektleider]" > Case 14 > LinkCriteria = "[Projekttype] = Forms![frm_Zoek1]![Projekttype] > AND [Ontwikkelingsfase] = Forms![frm_Zoek1]![Ontwikkelingsfase] AND > [Projektleider] = Forms![frm_Zoek1]![Projektleider]" > Case 15 > LinkCriteria = "[BU-groep] = Forms![frm_Zoek1]![BU] AND > [Projekttype] = Forms![frm_Zoek1]![Projekttype] AND [Ontwikkelingsfase] = > Forms![frm_Zoek1]![Ontwikkelingsfase] AND [Projektleider] = > Forms![frm_Zoek1]![Projektleider]" > End Select > > 'linkcriteria = "[Projekttype] = Forms![frm_Zoek1]![Projekttype] and > [BU-groep] = Forms![frm_Zoek1]![BU]" > DoCmd OpenForm DocName, , , LinkCriteria > > Exit_Knop17_Click: > Exit Sub > > Err_Knop17_Click: > MsgBox Error$ > Resume Exit_Knop17_Click > > End Sub > ----------------------------------------------------- > >Received on Tue Nov 12 1996 - 00:00:00 CET