Two logical tests in IIF statement

[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

 

[Duane Hookom]

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

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

 

[Gary Walter]

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().

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

 

[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"

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().

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

 

[Pete Provencher]

Your example worked. Thanks.

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().

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