Return Null values for date field using parameter

[markcooper]

Hi

I have a query which has a date field and sometimes this field is null. In the criteria row for this field in query design view, if I type Is Null, the query will return all null records, as expected. Similarly, if I type Is Not Null, it shows all records with a date.

However, I need to be able to select either null or not null values via a form and I cannot get this to work. I have tried using a simple parameter query and typing Is Null within the square brackets, but I get a data type mismatch as I assume it is taking this as a string.

Please could someone let me know how this can be done? Eventually the plan is that this will be entered in a text box, combo box or similar in a form. I am trying to avoid VBA where ever possible, so a solution without this would be ideal.

Many thanks

 

[bob fitz]

Why not put a control on the form (possibly a combo box or check box) for the user to make the choice, and then filter the form accordingly.

 

[markcooper]

Why not put a control on the form (possibly a combo box or check box) for the user to make the choice, and then filter the form accordingly.

Hi. Thanks for the reply. I plan on putting a control on the form. I would then use that control in the query criteria to limit the the results here (the only VBA that requires is a Me.Requery as an AfterUpdate event on the control. I was really hoping there was a way to get this to work to return the Null records or Not Null records but it just treats what every is in the control as a string.

I presume filtering the form will require more VBA, which is what I am trying to avoid. If there is no other way I guess I may have to investigate this.

 

[bob fitz]

.....but it just treats what every is in the control as a string.

I believe that may be true. User might have to type something like: ((TableName.FieldNmae)=[Is Null])
In the AfterUpdate event of a Check box you could use something like:

    If Me.ActiveControl = True Then
        Me.Filter = "([TableName].[FieldName] Is Null)"
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If

 

[shokly]

How about if crate a combobox on the form with two columns the first column data to be texts ( null dates, not null dates) and second column data be ( "", Not null) the show only first column by setting zero width in properties, then set this combo as criteria for query by choosing column 1
Forms!yourform!Combobox.column(1)

 

[Minty]

How about if crate a combobox on the form with two columns the first column data to be texts ( null dates, not null dates) and second column data be ( "", Not null) the show only first column by setting zero width in properties, then set this combo as criteria for query by choosing column 1
Forms!yourform!Combobox.column(1)

@shokly You can't specify a column reference for a combo in the query designer.
You could build a SQL statement and use that column value, but not without resorting to VBA.

 

[bastanu]

You can add a hidden textbox on the form, set its control source to Forms!yourform!Combobox.column(1) then reference that control in your query.

Cheers,

 

[Pat Hartman]

However, I need to be able to select either null or not null values via a form and I cannot get this to work. I have tried using a simple parameter query and typing Is Null within the square brackets, but I get a data type mismatch as I assume it is taking this as a string.

You cannot change a query's design on the fly. Parameters can ONLY substitute one value at a time for each parameter. Change Is Null to Not Is Null actually changes the structure of the SQL and it might change the execution plan generated by the query engine which is why you cannot do it. You also can't change relational operators such as = to <> or The number in a Top values predicate.

The only way to change the structure of a query is to use embedded SQL and build the query on the fly each time rather than using a saved querydef.

Since I never use form filters (because most of my apps use SQL Server or some other RDBMS rather than Jet/ACE), I would probably save two querydefs if this was the only filter I needed and just swap one for the other. based on the user pressing a button. Be careful to not save the form via code or you will save the replacement SQL. If that happens, you need to put in the default querydef in the open event of the form so the form always opens using either the Is Null or Not Is Null query depending on what makes the most sense.