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,