Wanting help regarding dynamic queries in MS-Access

From: E. Rijk <egonrijk_at_concepts.nl>
Date: 1996/11/10
Message-ID: <01bbcf52$0b240b00$b40786c2_at_egonrijk>#1/1

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 is then 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 Sun Nov 10 1996 - 00:00:00 CET