Re: implimenting select...Case

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 03/17/05 

Date: Thu, 17 Mar 2005 12:11:12 -0500

This setup doesn't really lend itself to a Select Case syntax, as there
isn't a single field/data source that is being used. But perhaps using the
Switch function:

Search = Switch( _
    Not IsNull(Me![UniID])) And IsNull(Me![Course]), _
                "studentsearch2", _
    Not IsNull(Me![Building])) And IsNull(Me![Block]) And _
        IsNull(Me![Flat]) And IsNull(Me![Room]), _
                "StudentSearch3", _
    Not IsNull(Me![Building])) And (Not IsNull(Me![Block])) And _
        IsNull(Me![Flat]) And IsNull(Me![Room]), _
                "StudentSearch4", _
    Not IsNull(Me![Building])) And (Not IsNull(Me![Block])) And _
        (Not IsNull(Me![Flat])) And IsNull(Me![Room]), _
                "StudentSearch5", _
    1=1, _
                "studentsearch" _
    )

The above uses "1=1" as the "no other conditions apply" condition (the final
"Else" branch in your original code). Switch evaluates each condition, and
the first one that is true causes the result to be selected). 

-- 
        Ken Snell
<MS ACCESS MVP>
"DowningDevelopments" <DowningDevelopments@discussions.microsoft.com> wrote 
in message news:4CABC3CC-AC0C-4265-8799-3FDB3C8A0246@microsoft.com...
> this is more or less a pure coding problem in VB
>
> I have a 'StudentSearch' form which has many criteria, Fname, Lname,
> University, Course etc which are put in through text boxes or drop down 
> lists
> and the results of which appear in a listbox called Results.
>
> This works fine except that ive got 7 queries for the rowsource of Result
> which are defined through the following If..else statement
>
> <!--
>    Dim Search As String
>
>    'if a uni is picked and a course is not then change the type of query
>
>    If (Not IsNull(Me![UniID])) And IsNull(Me![Course]) Then
>        Search = "studentsearch2"
>    Else
>       'find all the students in a building
>        If (Not IsNull(Me![Building])) And IsNull(Me![Block]) And
> IsNull(Me![Flat]) And IsNull(Me![Room]) Then
>            Search = "StudentSearch3"
>        Else
>         'find all the students ina block in a building
>            If (Not IsNull(Me![Building])) And (Not IsNull(Me![Block])) And
> IsNull(Me![Flat]) And IsNull(Me![Room]) Then
>            Search = "StudentSearch4"
>            Else
>            'Find all the students in a flat in a block in a building
>                If (Not IsNull(Me![Building])) And (Not IsNull(Me![Block]))
> And (Not IsNull(Me![Flat])) And IsNull(Me![Room]) Then
>                 Search = "StudentSearch5"
>                Else
>                   'impliment the default search
>                    Search = "studentsearch"
>                End If
>            End If
>        End If
>    End If
>
>   With Me![results]
>      .RowSource = Search
>      .Requery
>     End With
>
> -->
>
> Getting silly isnt it?
> Its going to get worse cos ive  been told that further criteria will be
> added and that the users will want to be able to search for students in 
> ever
> more imaginative  combinations of criteria.
> How can i change this to a Select..Case statement?
>
> With much thanks,
>
> Amit

Relevant Pages

  • Re: Switch Statements and Refactoring
    ... map nicely to a single Key like switch statements, ... if it meets the criteria... ... in which case you may not care which type ... >> and execute a switch on all of the subs. ...
    (comp.object)
  • Re: ALTERNATIVE TO SUMPRODUCT NEEDED
    ... You can use DCOUNT by setting the criteria text to *switch*. ... switch will occur in a paragraph, so I need a wild card and SUMPRODUCT ...
    (microsoft.public.excel.worksheet.functions)
  • RE: DHCP security
    ... 802.1X is a nice switch level protocol that enables one to restrict the ... criteria including MAC address, username, machine name, certificate, ...
    (Security-Basics)
  • Re: Subform in a form
    ... Here's the criteria: ... The main form has two combo boxes and the data types are text. ... So if I'm in Jan and switch to Feb the Jan is now say Feb. ...
    (microsoft.public.access.forms)
  • Re: implimenting select...Case
    ... reverse the usual logic and write it ... > This works fine except that ive got 7 queries for the rowsource of Result ... 'find all the students in a building ... > Its going to get worse cos ive been told that further criteria will be ...
    (microsoft.public.access.formscoding)