By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,489 Members | 1,932 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,489 IT Pros & Developers. It's quick & easy.

Parameter query to find null dates at runtime

P: n/a
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.

Jul 24 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"bobdydd" <re**************@yahoo.co.ukwrote in message
news:11*********************@d55g2000hsg.googlegro ups.com...
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.

Jul 24 '07 #2

P: n/a
On Tue, 24 Jul 2007 13:26:12 -0700, bobdydd <re**************@yahoo.co.uk>
wrote:
>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.
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]
Jul 24 '07 #3

P: n/a
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

Jul 25 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.