[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

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com