IIF Stament in a Query

  • Thread starter Thread starter Gus
  • Start date Start date
G

Gus

HI,

I'm trying to perform a query based on 1 to 3 variables in
a form. These are Date, RMR and Postcode. The unbound text
boxes in each of these fields are txtDate1, txtDate2,
txtRMR1, txtRMR2, txtPostCode1 and txtPostCode2. I wish
for a user to be able to perform a search using and one or
combination of the entered values.

On the form next to each of these is an unbound check box
named accordingly eg chktxtDate.

In a query which looks at these I have written the
following code in the criteria:

IIf([Forms]![frmResignMenu]![chkdate]=-1,
([tblContractExpiration].[Expires]) Between [Forms]!
[frmResignMenu]![txtdate1] And [Forms]![frmResignMenu]!
[txtdate2],([tblContractExpiration].[Expires]) Between
#01/01/1900# And #01/01/2090#)

I have also tried:

IIf([Forms]![frmResignMenu]![chkdate]=-1,Between [Forms]!
[frmResignMenu]![txtdate1] And [Forms]![frmResignMenu]!
[txtdate2],Between #01/01/1900# And #01/01/2090#)

Each of the two criterias individualy work fine but when I
set the IIF statement into the equation no data come up.

I have tried using variuos other terms for the "on/off"
parts of the check box but these do not appear to work and
have no idea what is wrong with the logic.

Could you tell me how to make this code work or what is
another option to pursue if this is not possible.

Thanks in advance.
 
Gus,

Well, the first thing you can do is remove the checkboxes from the form,
as they perform no useful function. And... where did you come up with
that idea of 1900 and 2090?

Anyway, try it like this...
In the criteria of the Expires column put...
Between [Forms]![frmResignMenu]![txtdate1] And
[Forms]![frmResignMenu]![txtdate2] Or [Forms]![frmResignMenu]![txtdate1]
Is Null

It is not clear why you have two RMR criteria boxes and two PostCode
criteria boxes... is this supposed to be a Between/And situation as well?
 
Steve,

Firstly thanks that works great.

The idea behind 1900 and 2090 was to give year date values
which would caputre all data on the database should there
be no specified data. (I doubt the company will be using
this report in 80 odd years.

The RMR is a "recuring revenue" so that searches for
customers between dollar amounts. The same with postcode
so clients can be broken down into region. These should
work with identical code - just changing the field names.

I guess i will get rid of my check boxes and set a "Reset
Data" cmdbutton to blank values after use.

Thanks again

Gus
-----Original Message-----
Gus,

Well, the first thing you can do is remove the checkboxes from the form,
as they perform no useful function. And... where did you come up with
that idea of 1900 and 2090?

Anyway, try it like this...
In the criteria of the Expires column put...
Between [Forms]![frmResignMenu]![txtdate1] And
[Forms]![frmResignMenu]![txtdate2] Or [Forms]! [frmResignMenu]![txtdate1]
Is Null

It is not clear why you have two RMR criteria boxes and two PostCode
criteria boxes... is this supposed to be a Between/And situation as well?

--
Steve Schapel, Microsoft Access MVP

HI,

I'm trying to perform a query based on 1 to 3 variables in
a form. These are Date, RMR and Postcode. The unbound text
boxes in each of these fields are txtDate1, txtDate2,
txtRMR1, txtRMR2, txtPostCode1 and txtPostCode2. I wish
for a user to be able to perform a search using and one or
combination of the entered values.

On the form next to each of these is an unbound check box
named accordingly eg chktxtDate.

In a query which looks at these I have written the
following code in the criteria:

IIf([Forms]![frmResignMenu]![chkdate]=-1,
([tblContractExpiration].[Expires]) Between [Forms]!
[frmResignMenu]![txtdate1] And [Forms]![frmResignMenu]!
[txtdate2],([tblContractExpiration].[Expires]) Between
#01/01/1900# And #01/01/2090#)

I have also tried:

IIf([Forms]![frmResignMenu]![chkdate]=-1,Between [Forms]!
[frmResignMenu]![txtdate1] And [Forms]![frmResignMenu]!
[txtdate2],Between #01/01/1900# And #01/01/2090#)

Each of the two criterias individualy work fine but when I
set the IIF statement into the equation no data come up.

I have tried using variuos other terms for the "on/off"
parts of the check box but these do not appear to work and
have no idea what is wrong with the logic.

Could you tell me how to make this code work or what is
another option to pursue if this is not possible.

Thanks in advance.
Click to expand...
.
Click to expand...
 
Gus,

If you use the kind of syntax I suggested in all three columns of the
query, and then close/save the query and open again in design view, you
will see that Access has changed things around to suit its own
purposes... don't worry about this.
 
Steve,

Do you know if it is possible to put the expression you
gave me earlier in the "form filter properties" as this
might be easier to duplicate the form I designed earlier.

Thanks again in advance I'm heading home for dinner so
wont be able to respone again today.

You've been a great help.

Gus
 
Back
Top