IF formula

[Andrea Stimson]

Hello,

I'm trying to create a formula using a field that has calculated the number
of days between two dates, but if the number of days is greater than 365 I
want it to enter 365 in the cell. (The number of days cannot be greater than
365). Could you please send me an example formula?

Thanks for your help
Andrea

 

[BruceM]

Access does not have cells. If this is an Excel question you would do best
to ask in an Excel group. If this is an Access question, if you want to add
365 days regardless of leap year, in an unbound text box on a form or
report:
=IIf(DateDiff("d",[Date1],[Date2]) > 365,365,DateDiff("d",[Date1],[Date2]))

If you want to allow for leap year, maybe something like:
=IIf(DateAdd("yyyy",1,[Date1]) < [Date2],365,DateDiff("d",[Date1],[Date2]))

The same expression could be used in a query. At the top of a blank column
in query design view:

DateCalc: IIf(DateDiff("d",[Date1],[Date2]) > 365...etc.

It could also be used in VBA, but I will await further details, including
whether this was an Access question at all.

 

[Andrea Stimson]

Thanks for getting back to me. Yes this is definately an access question. To
give you more info, the current expression I have is:

Days: DateDiff("d",[Dateemg2activated],[Now]).

So if the outcome is more than 365 I need it to enter 365. If you could
please respond.

Thanks
Andrea
--
Andrea Stimson
Technical Administrator

Access does not have cells. If this is an Excel question you would do best
to ask in an Excel group. If this is an Access question, if you want to add
365 days regardless of leap year, in an unbound text box on a form or
report:
=IIf(DateDiff("d",[Date1],[Date2]) > 365,365,DateDiff("d",[Date1],[Date2]))

If you want to allow for leap year, maybe something like:
=IIf(DateAdd("yyyy",1,[Date1]) < [Date2],365,DateDiff("d",[Date1],[Date2]))

The same expression could be used in a query. At the top of a blank column
in query design view:

DateCalc: IIf(DateDiff("d",[Date1],[Date2]) > 365...etc.

It could also be used in VBA, but I will await further details, including
whether this was an Access question at all.

 

[golfinray]

Something like:IIF(datediff("d",[dateemg2activated],[now]
<"365","365",[dateemg2activated]))
--
Milton Purdy
ACCESS
State of Arkansas

Thanks for getting back to me. Yes this is definately an access question. To
give you more info, the current expression I have is:

Days: DateDiff("d",[Dateemg2activated],[Now]).

So if the outcome is more than 365 I need it to enter 365. If you could
please respond.

Thanks
Andrea
--
Andrea Stimson
Technical Administrator

Access does not have cells. If this is an Excel question you would do best
to ask in an Excel group. If this is an Access question, if you want to add
365 days regardless of leap year, in an unbound text box on a form or
report:
=IIf(DateDiff("d",[Date1],[Date2]) > 365,365,DateDiff("d",[Date1],[Date2]))

If you want to allow for leap year, maybe something like:
=IIf(DateAdd("yyyy",1,[Date1]) < [Date2],365,DateDiff("d",[Date1],[Date2]))

The same expression could be used in a query. At the top of a blank column
in query design view:

DateCalc: IIf(DateDiff("d",[Date1],[Date2]) > 365...etc.

It could also be used in VBA, but I will await further details, including
whether this was an Access question at all.

Hello,

I'm trying to create a formula using a field that has calculated the
number
of days between two dates, but if the number of days is greater than 365 I
want it to enter 365 in the cell. (The number of days cannot be greater
than
365). Could you please send me an example formula?

Thanks for your help
Andrea

 

[BruceM]

I did respond. See the first expression I suggested. It can be placed in a
query field instead of an unbound text box. Replace Date2 with Now(). I
did not realize that one of the two dates was the current date rather than a
value in a date field.

Thanks for getting back to me. Yes this is definately an access question.
To
give you more info, the current expression I have is:

Days: DateDiff("d",[Dateemg2activated],[Now]).

So if the outcome is more than 365 I need it to enter 365. If you could
please respond.

Thanks
Andrea
--
Andrea Stimson
Technical Administrator

Access does not have cells. If this is an Excel question you would do
best
to ask in an Excel group. If this is an Access question, if you want to
add
365 days regardless of leap year, in an unbound text box on a form or
report:
=IIf(DateDiff("d",[Date1],[Date2]) >
365,365,DateDiff("d",[Date1],[Date2]))

If you want to allow for leap year, maybe something like:
=IIf(DateAdd("yyyy",1,[Date1]) <
[Date2],365,DateDiff("d",[Date1],[Date2]))

The same expression could be used in a query. At the top of a blank
column
in query design view:

DateCalc: IIf(DateDiff("d",[Date1],[Date2]) > 365...etc.

It could also be used in VBA, but I will await further details, including
whether this was an Access question at all.

message

Hello,

I'm trying to create a formula using a field that has calculated the
number
of days between two dates, but if the number of days is greater than
365 I
want it to enter 365 in the cell. (The number of days cannot be greater
than
365). Could you please send me an example formula?

Thanks for your help
Andrea

 

[BruceM]

I saw [Now] and read it as the Now() function rather than as a field. You
are correct about the syntax if Now is indeed a field (which it should not
be), but not if Now is the Now() function, in which case the brackets will
confuse Access. Access can probably sort it out if you place the numbers in
quotes (making them text values rather than number values), but I would keep
them as numbers since that is what they are.

Something like:IIF(datediff("d",[dateemg2activated],[now]
<"365","365",[dateemg2activated]))
--
Milton Purdy
ACCESS
State of Arkansas

Thanks for getting back to me. Yes this is definately an access question.
To
give you more info, the current expression I have is:

Days: DateDiff("d",[Dateemg2activated],[Now]).

So if the outcome is more than 365 I need it to enter 365. If you could
please respond.

Thanks
Andrea
--
Andrea Stimson
Technical Administrator

Access does not have cells. If this is an Excel question you would do
best
to ask in an Excel group. If this is an Access question, if you want
to add
365 days regardless of leap year, in an unbound text box on a form or
report:
=IIf(DateDiff("d",[Date1],[Date2]) >
365,365,DateDiff("d",[Date1],[Date2]))

If you want to allow for leap year, maybe something like:
=IIf(DateAdd("yyyy",1,[Date1]) <
[Date2],365,DateDiff("d",[Date1],[Date2]))

The same expression could be used in a query. At the top of a blank
column
in query design view:

DateCalc: IIf(DateDiff("d",[Date1],[Date2]) > 365...etc.

It could also be used in VBA, but I will await further details,
including
whether this was an Access question at all.

message
Hello,

I'm trying to create a formula using a field that has calculated the
number
of days between two dates, but if the number of days is greater than
365 I
want it to enter 365 in the cell. (The number of days cannot be
greater
than
365). Could you please send me an example formula?

Thanks for your help
Andrea

 

[BruceM]

If Now is a field named Now (which it should not be, as Now is reserved as a
function name), see my response to Milton. If it is the Now function, see
my earlier response.

I did respond. See the first expression I suggested. It can be placed in
a query field instead of an unbound text box. Replace Date2 with Now(). I
did not realize that one of the two dates was the current date rather than
a value in a date field.

Thanks for getting back to me. Yes this is definately an access question.
To
give you more info, the current expression I have is:

Days: DateDiff("d",[Dateemg2activated],[Now]).

So if the outcome is more than 365 I need it to enter 365. If you could
please respond.

Thanks
Andrea
--
Andrea Stimson
Technical Administrator

Access does not have cells. If this is an Excel question you would do
best
to ask in an Excel group. If this is an Access question, if you want to
add
365 days regardless of leap year, in an unbound text box on a form or
report:
=IIf(DateDiff("d",[Date1],[Date2]) >
365,365,DateDiff("d",[Date1],[Date2]))

If you want to allow for leap year, maybe something like:
=IIf(DateAdd("yyyy",1,[Date1]) <
[Date2],365,DateDiff("d",[Date1],[Date2]))

The same expression could be used in a query. At the top of a blank
column
in query design view:

DateCalc: IIf(DateDiff("d",[Date1],[Date2]) > 365...etc.

It could also be used in VBA, but I will await further details,
including
whether this was an Access question at all.

message
Hello,

I'm trying to create a formula using a field that has calculated the
number
of days between two dates, but if the number of days is greater than
365 I
want it to enter 365 in the cell. (The number of days cannot be
greater
than
365). Could you please send me an example formula?

Thanks for your help
Andrea

 

[BruceM]

A further observation is that the OP wants the number of days if less than
365, so the last part of the IIf needs to be an expression that returns that
number, not the date value itself.

I saw [Now] and read it as the Now() function rather than as a field. You
are correct about the syntax if Now is indeed a field (which it should not
be), but not if Now is the Now() function, in which case the brackets will
confuse Access. Access can probably sort it out if you place the numbers
in quotes (making them text values rather than number values), but I would
keep them as numbers since that is what they are.

Something like:IIF(datediff("d",[dateemg2activated],[now]
<"365","365",[dateemg2activated]))
--
Milton Purdy
ACCESS
State of Arkansas

Thanks for getting back to me. Yes this is definately an access
question. To
give you more info, the current expression I have is:

Days: DateDiff("d",[Dateemg2activated],[Now]).

So if the outcome is more than 365 I need it to enter 365. If you could
please respond.

Thanks
Andrea
--
Andrea Stimson
Technical Administrator


:

Access does not have cells. If this is an Excel question you would do
best
to ask in an Excel group. If this is an Access question, if you want
to add
365 days regardless of leap year, in an unbound text box on a form or
report:
=IIf(DateDiff("d",[Date1],[Date2]) >
365,365,DateDiff("d",[Date1],[Date2]))

If you want to allow for leap year, maybe something like:
=IIf(DateAdd("yyyy",1,[Date1]) <
[Date2],365,DateDiff("d",[Date1],[Date2]))

The same expression could be used in a query. At the top of a blank
column
in query design view:

DateCalc: IIf(DateDiff("d",[Date1],[Date2]) > 365...etc.

It could also be used in VBA, but I will await further details,
including
whether this was an Access question at all.

message
Hello,

I'm trying to create a formula using a field that has calculated the
number
of days between two dates, but if the number of days is greater than
365 I
want it to enter 365 in the cell. (The number of days cannot be
greater
than
365). Could you please send me an example formula?

Thanks for your help
Andrea

 

[John W. Vinson]

Something like:IIF(datediff("d",[dateemg2activated],[now]
<"365","365",[dateemg2activated]))

In addition to the other valid concerns, you should NOT be putting numbers
(365) in quote marks. Just use < 365 instead of < "365" for instance. Note
that the text string "35899924" is in fact less than the text string "365".