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

Using IIf in Criteria for a query

P: n/a
I have a query with a numeric field called BALBOOK. I have a form
(we'll call it Form1) that runs this query. On this form is a text
box (we'll call it Text1). When the query is run, I want is to use
all the records from a file where BALBOOK is equal to Text1. However,
if Text1 is blank (or null) I want the query to use all the records.

I don't know how to write the IIf statement. It would look something
like this:

IIf(Isnumeric([Forms].[Form1].[Text1]),CInt(([Forms].[Form1].[Text1])),"???")

The "???" is where I am stuck. I have tried ">0" (without the
quotes). I have tried leaving it blank. It works of Text1 is
numeric. It does not work if it is left blank.

Any suggestions?
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
pw****@hotmail.com (Paul) wrote:
IIf(Isnumeric([Forms].[Form1].[Text1]),CInt(([Forms].[Form1].[Text1])),"
???")


Try this (untested):

IIf(Not IsNull([Forms]![Form1]![Text1]),CInt([Forms]![Form1]![Text1]),Null)

If there's something in the text box it will be used as the criteria. If
the text box is null then so will the criteria.

Regards,
Keith.
Nov 13 '05 #2

P: n/a
Paul wrote:
I have a query with a numeric field called BALBOOK. I have a form
(we'll call it Form1) that runs this query. On this form is a text
box (we'll call it Text1). When the query is run, I want is to use
all the records from a file where BALBOOK is equal to Text1. However,
if Text1 is blank (or null) I want the query to use all the records.

I don't know how to write the IIf statement. It would look something
like this:

IIf(Isnumeric([Forms].[Form1].[Text1]),CInt(([Forms].[Form1].[Text1])),"???")

The "???" is where I am stuck. I have tried ">0" (without the
quotes). I have tried leaving it blank. It works of Text1 is
numeric. It does not work if it is left blank.

Any suggestions?


Hae a look at

http://www.mvps.org/access/queries/qry0001.htm


Nov 13 '05 #3

P: n/a
Keith Wilby <ke*********@AwayWithYerCrap.com> wrote in message news:<Xn***********************@10.15.188.42>...
pw****@hotmail.com (Paul) wrote:
IIf(Isnumeric([Forms].[Form1].[Text1]),CInt(([Forms].[Form1].[Text1])),"
???")


Try this (untested):

IIf(Not IsNull([Forms]![Form1]![Text1]),CInt([Forms]![Form1]![Text1]),Null)

If there's something in the text box it will be used as the criteria. If
the text box is null then so will the criteria.


Maybe I'm a little confused, but I'm thinking that if this works at
all it will at best limit your results to those records where this
field is Null rather than applying no criterion to this field at all.

In situations like this I usually build an SQL string in the form
itself and then run it or, if you need compiled query performance,
just build two separate queries, one with the criterion based on the
text box defined, and one without.

Bruce
Nov 13 '05 #4

P: n/a
br***@aristotle.net (Bruce) wrote:
I'm thinking that if this works at
all it will at best limit your results to those records where this
field is Null rather than applying no criterion to this field at all.


I would have thought that to return null instances, the 'false' part would
have to read "Is Null". Isn't by having just 'Null' in there the same as
not entering any criteria, ie leave it null?
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.