[AccessD] Changing Criteria

    You are most welcome Martin! 

    Two clarifications -

    (a) It is seen that your existing code uses the following syntax for Case statement
        Case Is = 1  ' (and so on)
        This can be replaced by
        Case 1    ' (and so on)

    (b) Is it correct that the only modification carried out in Where clause of the suggested SQL statement is by way of omitting the second argument in Nz() function, replacing Nz([HotelCode],"") by  Nz([HotelCode]). Was there any compulsion to do so? Was there any problem with the original one? Although the second argument is optional, and if not supplied, zero length string is assumed as the default value, it is considered a desirable practice to provide this argument explicitly.

Best wishes,
A.D.Tejpal
--------------

  ----- Original Message ----- 
  From: Martin Caro 
  To: Access Developers discussion and problem solving 
  Sent: Thursday, June 02, 2005 16:29
  Subject: Re: [AccessD] Changing Criteria


  Thanks A.D. & Rocky for your replies

  Managed to get ADs code working although needed to make a slight mod to the
  WHERE line first

  WHERE
  (((Nz([HotelCode],""))=IIf([Forms]![F_HotelFilter]![FilterCode]="ALL",Nz([Ho
  telCode],""),[Forms]![F_HotelFilter]![FilterCode])));

  Modified to:

  WHERE
  (((Nz([HotelCode]))=IIf([Forms]![F_HotelFilter]![FilterCode]="ALL",Nz([Hotel
  Code]),[Forms]![F_HotelFilter]![FilterCode])));

  Thanks again

  Martin

  ----- Original Message -----
  From: "A.D.Tejpal" <adtp at touchtelindia.net>
  To: "Access Developers discussion and problem solving"
  <accessd at databaseadvisors.com>
  Sent: Thursday, June 02, 2005 3:56 PM
  Subject: Re: [AccessD] Changing Criteria


  Martin,

      You wish to incorporate criteria options in a query (to be picked up
  from a form) for -
      (a) Null values only
      (b) All values (including Nulls)
      These two involve non-string expressions if direct comparison with
  existing field in the table is attempted. The situation does not directly
  afford an appropriate string that could straightaway be used in the manner
  applicable to case statements 1 to 6.

      For getting the desired results, following course of action is
  suggested. HotelCode is the name of text type field in table T_HotelCode.

      (a) Select query may be modified as shown below. It would  mean that the
  value of FilterCode on form F_HotelFilter will get tested against
  Nz([HotelCode],"") in the query.
      (b) Code in form's module may be modified as shown below. It provides
  for -
          Case Is = 7
              FilterCode = ""
          Case Is = 8
              FilterCode = "ALL"

  Best wishes,
  A.D.Tejpal
  --------------

  Select Query
  ====================================
  SELECT T_HotelCode.*
  FROM T_HotelCode
  WHERE
  (((Nz([HotelCode],""))=IIf([Forms]![F_HotelFilter]![FilterCode]="ALL",Nz([Ho
  telCode],""),[Forms]![F_HotelFilter]![FilterCode])));
  ====================================

  Code in Form's module
  ====================================
  Private Sub HotelFilter_AfterUpdate()
      Select Case HotelFilter
          Case Is = 1
              FilterCode = "FH"
          Case Is = 2
              FilterCode = "FHI"
          Case Is = 3
              FilterCode = "LH"
          Case Is = 4
              FilterCode = "B"
          Case Is = 5
              FilterCode = "L"
          Case Is = 6
              FilterCode = "E"
          Case Is = 7
              FilterCode = ""
          Case Is = 8
              FilterCode = "ALL"
         End Select
      Me.Refresh
  End Sub
  ====================================

    ----- Original Message -----
    From: Martin Caro
    To: Access Developers discussion and problem solving
    Sent: Thursday, June 02, 2005 07:47
    Subject: [AccessD] Changing Criteria


    Hi Folks

    Is it possible to do the following:

    I have a query where one of the fields references a text box containing a
  Filter Code to pick up the required selection criteria for the query. It
  works fine for Case 1 to 6 but what is the syntax to make Case 7 and 8 work?

    Private Sub HotelFilter_AfterUpdate()
        Select Case HotelFilter
            Case Is = 1
                FilterCode = "FH"
            Case Is = 2
                FilterCode = "FHI"
            Case Is = 3
                FilterCode = "LH"
            Case Is = 4
                FilterCode = "B"
            Case Is = 5
                FilterCode = "L"
            Case Is = 6
                FilterCode = "E"
            Case Is = 7
                FilterCode =      Display only those records that do not have
  a code ie Code is Null
            Case Is = 8
                FilterCode =      Display all records ie no filter
           End Select
        Me.Refresh