[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([HotelCode],""),[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