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

Access Building a IIF expression in Access 0
IIF statement...what is wrong w/ this expression? 5
Update Value in Table based on to that of Another Field 1
Using DateAdd and IIF conditions in a Query 2
How to automate this...? 3
Return Maximum Value When Field is Duplicate 4
Dave Validation Expression 2
Clear a date field using a macro 12

Top