Parameter query to find null dates at runtime

B

bobdydd

Hi Everybody

I am trying to be able to filter records by whether the date field
which is called "RiskDate" in the
recordset, is either:

1. Is Null
2. Not Null

The parameter in the query field RiskDate is:
Like "*" & [Forms]![frmCommissionContainer]![ComboRiskDate]

But I cannot figure what to put in ComboRiskDate to supply the
parameter at runtime.

Most grateful if anyone could help.
 
D

Douglas J. Steele

One approach would be to put anything you like in the combo box ("Null" and
"Not Null" or even "Red" and "Blue" if that means something to you).

You'd then want the WHERE clause in your SQL to look like:

WHERE ((RiskDate IS NULL) AND
([Forms]![frmCommissionContainer]![ComboRiskDate] = "Null"))
OR ((RiskDate IS NOT NULL) AND
([Forms]![frmCommissionContainer]![ComboRiskDate] = "Not Null"))

You might be able to specify this in the query grid as:

IS NULL AND [Forms]![frmCommissionContainer]![ComboRiskDate] = "Null"

on one line, and

IS NOT NULL AND [Forms]![frmCommissionContainer]![ComboRiskDate] = "Not
Null"

on another line, although I'd recommend going into the SQL view and making
sure it's correct.
 
J

John W. Vinson

Hi Everybody

I am trying to be able to filter records by whether the date field
which is called "RiskDate" in the
recordset, is either:

1. Is Null
2. Not Null

The parameter in the query field RiskDate is:
Like "*" & [Forms]![frmCommissionContainer]![ComboRiskDate]

But I cannot figure what to put in ComboRiskDate to supply the
parameter at runtime.

Most grateful if anyone could help.
Click to expand...

Well, Like won't work. What are the actual values of the bound column in
ComboRiskDate? If (e.g.) it's the text strings IS NULL and IS NOT NULL, try

WHERE (RiskDate IS NULL AND [Forms]![frmCommissionContainer]![ComboRiskDate]
= "IS NULL")
OR (RiskDate IS NOT NULL AND [Forms]![frmCommissionContainer]![ComboRiskDate]
= "IS NOT NULL")


John W. Vinson [MVP]
 
B

bobdydd

Thanks guys

Some success

I managed to do this in the query grid by putting in the RiskDate
Column

0. Is Null
1. Is Not Null

I then added another "Unseen column" with "Forms]!
[frmCommissionContainer]![ComboRiskDate"
and then put
0
Or 1
In that column. And bingo the combo box selects correctly
 
Top