Using IIf in Criteria for a query

[Paul]

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?

[Keith Wilby]

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.

[Roberto Spier]

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

[Bruce]

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

[Keith Wilby]

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?