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

Passing comparison criteria and listbox questions

P: n/a
I'm currently building a form (called frmReports) to set the criteria
for a query, but I'm having some trouble with syntax and hope that one
of the guru's here can help me achieve what I'm do.

First troublesome variable:

I would like to sort records by numbers of employees (numeric data
type) into three groups: 1-250, 251-2000, and 2000+. Based on three
check-boxes on my form (which we'll call Box1, Box2, and Box3), I've
tried to use the following as criteria:

IIf([Forms]![frmReports]![Box1] = -1, <250, Null) OR
IIf([Forms]![frmReports]![Box2] = -1, >250 AND <2001, Null) OR
IIf([Forms]![frmReports]![Box1] = -1, >2000, Null)

The problem is that this returns an empty record set every time.

Second troublesome variable:

I have a list-box with various types of software (called 'Software').
I want to be able to select a software type from that list box, then
filter on that. If no software is selected from the list-box, I want
to return all of the records in the recordset. Again, I've tried to
do this by using the following syntax:

IIf([Forms]![frmReports]![Software] is not null,
[Forms]![frmReports]![Software], "*")

The problem is that this returns an empty record set every time as
well.

Can anyone correct my syntax, or suggest a better way of performing
these two functions in a query?

Many thanks in advance for any opinions offered!
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
ma*****@matthewclement.co.uk (Matthew Clement) wrote in message news:<3e**************************@posting.google. com>...
I'm currently building a form (called frmReports) to set the criteria
for a query, but I'm having some trouble with syntax and hope that one
of the guru's here can help me achieve what I'm do.

First troublesome variable:

I would like to sort records by numbers of employees (numeric data
type) into three groups: 1-250, 251-2000, and 2000+. Based on three
check-boxes on my form (which we'll call Box1, Box2, and Box3), I've
tried to use the following as criteria:

IIf([Forms]![frmReports]![Box1] = -1, <250, Null) OR
IIf([Forms]![frmReports]![Box2] = -1, >250 AND <2001, Null) OR
IIf([Forms]![frmReports]![Box1] = -1, >2000, Null)

The problem is that this returns an empty record set every time.
AFAIK, you can't pass in a comparison operator the way you're
attempting to do it. if this is for a query, you'll have to build the
Where statement in code, open the QueryDef, change the Where clause
(build it in a string variable) and tack it on.

If I were you, I'd just use a report and then build the Where clause.
Then just pass the Where clause you built in the Open event of the
report.
Second troublesome variable:

I have a list-box with various types of software (called 'Software').
I want to be able to select a software type from that list box, then
filter on that. If no software is selected from the list-box, I want
to return all of the records in the recordset. Again, I've tried to
do this by using the following syntax:

IIf([Forms]![frmReports]![Software] is not null,
[Forms]![frmReports]![Software], "*")

The problem is that this returns an empty record set every time as
well.
If you're only choosing ONE value, then don't use a listbox, use a
combobox.

there's code at www.mvps.org/access to do what you are talking about
either in the forms or queries section. Can anyone correct my syntax, or suggest a better way of performing
these two functions in a query?

Many thanks in advance for any opinions offered!

Nov 12 '05 #2

P: 1
Yeah, I figured this out some time ago that wildcards don't work properly when you try to do this. You have to use the originating field name.

IIf([Forms]![frmReports]![Software] is not null, [Forms]![frmReports]![Software], [Software])

Assuming that "Software" is the field you are putting this IIF statement in the Query. It is basically saying if you can't find anything in the Search field on the form, then each record must match itself. The only one thing I can't figure out how to handle with this is Null values. When I put an "OR Null" statement in there, it does not work properly.

Example that does not work: IIf([Forms]![frmReports]![Software] is not null, [Forms]![frmReports]![Software], [Software] or Null)
Jun 30 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.