Re: Wanting help regarding dynamic queries in MS-Access

On 10 Nov 1996 21:56:24 GMT, "E. Rijk" <egonrijk_at_concepts.nl> wrote:

>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
>-----------------------------------------------------
>

 Yeah that's quite a procedure. Fortunate for you...I have a very easy solution. With each parameter create an expression like this...

  Me![controlname] Or Me![controlname] Is Null

 This on the criteria row. Test it with a parameter query under something like customer ID with the NWIND sample database.

  [Enter a Customer ID] or [Enter a Customer ID] Is Null

 If you enter something then that criteria will be applied but if you ignore it then all records are returned.

  Good Luck! Brian_RestonVA
  1996 Microsoft MVP Received on Mon Nov 11 1996 - 00:00:00 CET