A.D.Tejpal
adtp at touchtelindia.net
Thu Jun 2 00:56:35 CDT 2005
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