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

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])
 
S

sashabaz

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...
 
S

sashabaz

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...
 
T

Tom Lake

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
 
S

sashabaz

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using DateAdd and IIF conditions in a Query 2
ActualFinishDate being set to the same date as FinishDate when markedas complete, regardless of the 0
Update Value in Table based on to that of Another Field 2
Send email to a email address 7 days before the due date 1
IIF statement...what is wrong w/ this expression? 5
IIF in Crosstab Expression 0
Macro to send email 5 days before a date mentioned in Cell 1
Return Maximum Value When Field is Duplicate 4

Top