Two logical tests in IIF statement

  • Thread starter Thread starter Pete Provencher
  • Start date Start date
P

Pete Provencher

Using Access 2000:

Trying to write and update query where it will automatically update my
status field. It looks at the 3 options of New, Changed, or Existing. If the
entry date is over 30 days it changes it to Existing. The only exception is
if the status is currently change it will leave it at change till the 30
days is up.

Formula:

=IIF(AND([status]="Changed",[entrydate]<=Date()-30),"Existing",IIF(AND([stat
us]="New",[entrydate]<=date()-30),"Existing",IIF(AND([status]="Existing",[en
trydate]<=date()-30),"Existing",[status])))

It doesn't work. A variation of it works in excel where I first created it.
Then I changed cell disignations to field names and changed TODAY() to
DATE().

Any help will be appreciated.

Pete Provencher
 
Not sure if I got all the ()s and logic correct but try:
=IIF([status]="Changed" AND
[entrydate]<=Date()-30,"Existing",IIF([status]="New" AND
[entrydate]<=date()-30,"Existing",IIF([status]="Existing" AND
[entrydate]<=date()-30,"Existing",[status])))

--
Duane Hookom
MS Access MVP


Pete Provencher said:
Using Access 2000:

Trying to write and update query where it will automatically update my
status field. It looks at the 3 options of New, Changed, or Existing. If the
entry date is over 30 days it changes it to Existing. The only exception is
if the status is currently change it will leave it at change till the 30
days is up.

Formula:

=IIF(AND([status]="Changed",[entrydate]<=Date()-30),"Existing",IIF(AND([status]="New",[entrydate]<=date()-30),"Existing",IIF(AND([status]="Existing",[en
trydate]<=date()-30),"Existing",[status])))

It doesn't work. A variation of it works in excel where I first created it.
Then I changed cell disignations to field names and changed TODAY() to
DATE().

Any help will be appreciated.

Pete Provencher
Click to expand...
 
Pete Provencher said:
Using Access 2000:

Trying to write and update query where it will automatically update my
status field. It looks at the 3 options of New, Changed, or Existing. If the
entry date is over 30 days it changes it to Existing. The only exception is
if the status is currently change it will leave it at change till the 30
days is up.

Formula:

=IIF(AND([status]="Changed",[entrydate]<=Date()-30),"Existing",IIF(AND([stat
us]="New",[entrydate]<=date()-30),"Existing",IIF(AND([status]="Existing",[en
trydate]<=date()-30),"Existing",[status])))

It doesn't work. A variation of it works in excel where I first created it.
Then I changed cell disignations to field names and changed TODAY() to
DATE().
Click to expand...
Hi Pete,

The Access version of your Excel formula would be

=IIF(([status]="Changed") AND ([entrydate]<=Date()-30), "Existing",
IIF(([status]="New") AND ([entrydate]<=Date()-30), "Existing", [status]))

but I don't "see" the "only exception" you talk about.

The above could be written as:

=IIF([entrydate]<=Date()-30, "Existing",[status])

Sorry to be so dense...

Gary Walter
 
Plus....if this *is* the case

You could just start with a SELECT
query with a criteria on [entrydate] of

<=Date() - 30

Then change to update query
and update [status] field to "Existing"

(no IIF needed)

Again, apologies for "denseness"

Gary Walter said:
Pete Provencher said:
Using Access 2000:

Trying to write and update query where it will automatically update my
status field. It looks at the 3 options of New, Changed, or Existing. If the
entry date is over 30 days it changes it to Existing. The only exception is
if the status is currently change it will leave it at change till the 30
days is up.

Formula:

=IIF(AND([status]="Changed",[entrydate]<=Date()-30),"Existing",IIF(AND([stat
us]="New",[entrydate]<=date()-30),"Existing",IIF(AND([status]="Existing",[en
trydate]<=date()-30),"Existing",[status])))

It doesn't work. A variation of it works in excel where I first created it.
Then I changed cell disignations to field names and changed TODAY() to
DATE().
Click to expand...
Hi Pete,

The Access version of your Excel formula would be

=IIF(([status]="Changed") AND ([entrydate]<=Date()-30), "Existing",
IIF(([status]="New") AND ([entrydate]<=Date()-30), "Existing", [status]))

but I don't "see" the "only exception" you talk about.

The above could be written as:

=IIF([entrydate]<=Date()-30, "Existing",[status])

Sorry to be so dense...

Gary Walter
Click to expand...
 
Your example worked. Thanks.

Pete Provencher
Gary Walter said:
Pete Provencher said:
Using Access 2000:

Trying to write and update query where it will automatically update my
status field. It looks at the 3 options of New, Changed, or Existing. If the
entry date is over 30 days it changes it to Existing. The only exception is
if the status is currently change it will leave it at change till the 30
days is up.

Formula:

=IIF(AND([status]="Changed",[entrydate]<=Date()-30),"Existing",IIF(AND([stat
us]="New",[entrydate]<=date()-30),"Existing",IIF(AND([status]="Existing",[en
trydate]<=date()-30),"Existing",[status])))

It doesn't work. A variation of it works in excel where I first created it.
Then I changed cell disignations to field names and changed TODAY() to
DATE().
Click to expand...
Hi Pete,

The Access version of your Excel formula would be

=IIF(([status]="Changed") AND ([entrydate]<=Date()-30), "Existing",
IIF(([status]="New") AND ([entrydate]<=Date()-30), "Existing", [status]))

but I don't "see" the "only exception" you talk about.

The above could be written as:

=IIF([entrydate]<=Date()-30, "Existing",[status])

Sorry to be so dense...

Gary Walter
Click to expand...
 
Back
Top