Using IIf on a date field to return the result of an expression

  • Thread starter Thread starter sashabaz
  • Start date Start date
S

sashabaz

I am currently trying to create a field that looks up the contents of another
field returning the result of an expression depending on what is in the
target field.

The three fields being used are the Revised Date field, the Contract Daete
field, and the Approval Period field.

So, if there is a revised date entered, then the output is the result of the
contract date plus the approval period. If there is a revised date, then the
value returned is the revised date plus the approval period. I have tried to
do it by leaving the revised date field blank and by setting a default.
Neither seems to work!! the following is the current expression I am using,
with the default value of 01/01/2001.

IIf ([Revised Date] = 01/01/2001, = ([Contract Date] + [Approval Period]),
=[Revised Date] + [Approval Period])
 
I had a look at the DateAdd() function, but it seems to only add a specific
value to the date. I need to take that value from another field...
otherwise I would have just added 10 to the date.

I took out the "=" and it now does what I wanted!!!

Thanks to Jeff!!!
Regards,

Aaron

Jeff Boyce said:
Leave off those "=" signs, for starters.

Take a look at the DateAdd() function in Access HELP.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

sashabaz said:
I am currently trying to create a field that looks up the contents of another
field returning the result of an expression depending on what is in the
target field.

The three fields being used are the Revised Date field, the Contract Daete
field, and the Approval Period field.

So, if there is a revised date entered, then the output is the result of the
contract date plus the approval period. If there is a revised date, then the
value returned is the revised date plus the approval period. I have tried to
do it by leaving the revised date field blank and by setting a default.
Neither seems to work!! the following is the current expression I am using,
with the default value of 01/01/2001.

IIf ([Revised Date] = 01/01/2001, = ([Contract Date] + [Approval Period]),
=[Revised Date] + [Approval Period])
Click to expand...
Click to expand...
 
It seems that I have it half working..... It now takes the correct data, but
it only seems to take that data from the first record.

=IIf([Revised Date]=1/1/2009,([Contract Date]+[Approval Period]),([Revised
Date]+[Approval Period]))

this is what is being used. If there is a revised date entered, everything
works correctly. However, if only the contract date is entered it takes the
first record's value and shows the result in all records.

If I use the expression =[contract date] + [approval date] it works
perfectly. Seems strange that putting an IIf in it only takes the fir
record's data.....

Jeff Boyce said:
Leave off those "=" signs, for starters.

Take a look at the DateAdd() function in Access HELP.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

sashabaz said:
I am currently trying to create a field that looks up the contents of another
field returning the result of an expression depending on what is in the
target field.

The three fields being used are the Revised Date field, the Contract Daete
field, and the Approval Period field.

So, if there is a revised date entered, then the output is the result of the
contract date plus the approval period. If there is a revised date, then the
value returned is the revised date plus the approval period. I have tried to
do it by leaving the revised date field blank and by setting a default.
Neither seems to work!! the following is the current expression I am using,
with the default value of 01/01/2001.

IIf ([Revised Date] = 01/01/2001, = ([Contract Date] + [Approval Period]),
=[Revised Date] + [Approval Period])
Click to expand...
Click to expand...
 
sashabaz said:
It seems that I have it half working..... It now takes the correct data, but
it only seems to take that data from the first record.

=IIf([Revised Date]=1/1/2009,([Contract Date]+[Approval Period]),([Revised
Date]+[Approval Period]))

this is what is being used. If there is a revised date entered, everything
works correctly. However, if only the contract date is entered it takes the
first record's value and shows the result in all records.
Click to expand...

Does this help?

=IIf([Revised Date]=#1/1/2009#, [Contract Date] + [Approval Period], [Revised Date] +
[Approval Period])

Tom Lake
 
YES!!! Tom the MAN!!! :)

Cheers dude....

Tom Lake said:
sashabaz said:
It seems that I have it half working..... It now takes the correct data, but
it only seems to take that data from the first record.

=IIf([Revised Date]=1/1/2009,([Contract Date]+[Approval Period]),([Revised
Date]+[Approval Period]))

this is what is being used. If there is a revised date entered, everything
works correctly. However, if only the contract date is entered it takes the
first record's value and shows the result in all records.
Click to expand...

Does this help?

=IIf([Revised Date]=#1/1/2009#, [Contract Date] + [Approval Period], [Revised Date] +
[Approval Period])

Tom Lake
Click to expand...
 
Back
Top