"IF" statements in calculated fields

[Jennifer Cali]

I have two fields in my table dtmDateVacant & dtmDateFilled, and one
calculation on the form that calculates the difference between those two
dates (field: txtTimeToFill). I want the calculation to be such that if there
is a date in the date filled then the calculated box will show the difference
- which it does - but if there is NO date in the dtmDateFilled field then it
will subtract using TODAY's date. How can I set this up? I can do it in Excel
like this: =IF(L8>0,L8-K8,IF(K8>0,TODAY()-K8,"")) - how do I do this in
Access?

 

[Douglas J. Steele]

IIf(IsNull(Me.dtmDateFilled), DateDiff("d", Me.dtmDateVacant, Date()),
DateDiff("d", Me,dtmDateVacant, Me.dtmDateFilled))

 

[Jennifer Cali]

Doug, you have been my knight in shining armor lately! Thank you for
answering my questions so well.

I was playing around with the VB and had the value calc'd upon field update
and it went something like this (see below). It worked if there was a date in
the filled date but not if it was blank. Was I close?

If dtmDateFilled = Null Then
clcTimeToFill = Date - dtmDateVacant
ElseIf dtmDateFilled <> 0 Then
clcTimeToFill = dtmDateFilled - dtmDateVacant
End If

 

[Jennifer Cali]

Uh-oh, I did something wrong. I pasted your formula into the Control Source,
but I'm getting the #Name? error. Do I need to be substituting any variables
into the formula? What does the "d" signify?

 

[Jennifer Cali]

Hey, this works!

If IsNull(dtmDateFilled) Then
clcTimeToFill = Date - dtmDateVacant
Else
clcTimeToFill = dtmDateFilled - dtmDateVacant
End If

 

[Steve Schapel]

Jennifer,

One possibility here is that the name of the textbox is the same as the
name as a field in the form's Record Source table/query.

More likely cause of the trouble, though, is that there is a typo in
Doug's expression, there is a , instead of a . and also the use of the
Me is not correct in the case of an expression going into the Control
Source of a textbox. Also you would need a = in front of the expression.

To translate Doug's idea, then, try it like this...

=IIf(IsNull([dtmDateFilled]),DateDiff("d",[dtmDateVacant],Date()),DateDiff("d",[dtmDateVacant],[dtmDateFilled]))

Another way of looking at the same thing would be...
=Nz([dtmDateFilled],Date())-[dtmDateVacant]

 

[Douglas J. Steele]

Thanks, Steve.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Jennifer,

One possibility here is that the name of the textbox is the same as the
name as a field in the form's Record Source table/query.

More likely cause of the trouble, though, is that there is a typo in
Doug's expression, there is a , instead of a . and also the use of the Me
is not correct in the case of an expression going into the Control Source
of a textbox. Also you would need a = in front of the expression.

To translate Doug's idea, then, try it like this...

=IIf(IsNull([dtmDateFilled]),DateDiff("d",[dtmDateVacant],Date()),DateDiff("d",[dtmDateVacant],[dtmDateFilled]))

Another way of looking at the same thing would be...
=Nz([dtmDateFilled],Date())-[dtmDateVacant]

--
Steve Schapel, Microsoft Access MVP

Uh-oh, I did something wrong. I pasted your formula into the Control
Source, but I'm getting the #Name? error. Do I need to be substituting
any variables into the formula? What does the "d" signify?

 

[Jennifer Cali]

Thanks - that works great. Now to add another level: I'm trying to track the
type of positions we have at work and associate that with the reason the
position is vacant. For example, we have cashiers, managers, and clerks. Each
can be vacant for one of four reasons (quit, fired, on leave, in training). I
need to come up with some way to do a count of each vacancy reason for each
position, and then combine that data into one report. I've been able to
create queries (16 of them) that each do a count but now I can't combine
them. What would you suggest? I'm at a loss on this one.
--
Thank you! - Jennifer

Jennifer,

One possibility here is that the name of the textbox is the same as the
name as a field in the form's Record Source table/query.

More likely cause of the trouble, though, is that there is a typo in
Doug's expression, there is a , instead of a . and also the use of the
Me is not correct in the case of an expression going into the Control
Source of a textbox. Also you would need a = in front of the expression.

To translate Doug's idea, then, try it like this...

=IIf(IsNull([dtmDateFilled]),DateDiff("d",[dtmDateVacant],Date()),DateDiff("d",[dtmDateVacant],[dtmDateFilled]))

Another way of looking at the same thing would be...
=Nz([dtmDateFilled],Date())-[dtmDateVacant]

--
Steve Schapel, Microsoft Access MVP

Uh-oh, I did something wrong. I pasted your formula into the Control Source,
but I'm getting the #Name? error. Do I need to be substituting any variables
into the formula? What does the "d" signify?

 

[Pieter Wijnen]

Something Like
SELECT A.POSITION, A.REASON, COUNT(*) AS CNT FROM POSITIONS A
WHERE dtmDateFilled IS NULL
GROUP BY A.POSITION, A.REASON

HTH

Pieter

Thanks - that works great. Now to add another level: I'm trying to track
the
type of positions we have at work and associate that with the reason the
position is vacant. For example, we have cashiers, managers, and clerks.
Each
can be vacant for one of four reasons (quit, fired, on leave, in
training). I
need to come up with some way to do a count of each vacancy reason for
each
position, and then combine that data into one report. I've been able to
create queries (16 of them) that each do a count but now I can't combine
them. What would you suggest? I'm at a loss on this one.
--
Thank you! - Jennifer

Jennifer,

One possibility here is that the name of the textbox is the same as the
name as a field in the form's Record Source table/query.

More likely cause of the trouble, though, is that there is a typo in
Doug's expression, there is a , instead of a . and also the use of the
Me is not correct in the case of an expression going into the Control
Source of a textbox. Also you would need a = in front of the expression.

To translate Doug's idea, then, try it like this...

=IIf(IsNull([dtmDateFilled]),DateDiff("d",[dtmDateVacant],Date()),DateDiff("d",[dtmDateVacant],[dtmDateFilled]))

Another way of looking at the same thing would be...
=Nz([dtmDateFilled],Date())-[dtmDateVacant]

--
Steve Schapel, Microsoft Access MVP

Uh-oh, I did something wrong. I pasted your formula into the Control
Source,
but I'm getting the #Name? error. Do I need to be substituting any
variables
into the formula? What does the "d" signify?

 

[Pieter Wijnen]

Something Like
SELECT A.POSITION, A.REASON, COUNT(*) AS CNT FROM POSITIONS A
WHERE dtmDateFilled IS NULL
GROUP BY A.POSITION, A.REASON

HTH

Pieter

Thanks - that works great. Now to add another level: I'm trying to track
the
type of positions we have at work and associate that with the reason the
position is vacant. For example, we have cashiers, managers, and clerks.
Each
can be vacant for one of four reasons (quit, fired, on leave, in
training). I
need to come up with some way to do a count of each vacancy reason for
each
position, and then combine that data into one report. I've been able to
create queries (16 of them) that each do a count but now I can't combine
them. What would you suggest? I'm at a loss on this one.
--
Thank you! - Jennifer

Jennifer,

One possibility here is that the name of the textbox is the same as the
name as a field in the form's Record Source table/query.

More likely cause of the trouble, though, is that there is a typo in
Doug's expression, there is a , instead of a . and also the use of the
Me is not correct in the case of an expression going into the Control
Source of a textbox. Also you would need a = in front of the expression.

To translate Doug's idea, then, try it like this...

=IIf(IsNull([dtmDateFilled]),DateDiff("d",[dtmDateVacant],Date()),DateDiff("d",[dtmDateVacant],[dtmDateFilled]))

Another way of looking at the same thing would be...
=Nz([dtmDateFilled],Date())-[dtmDateVacant]

--
Steve Schapel, Microsoft Access MVP

Uh-oh, I did something wrong. I pasted your formula into the Control
Source,
but I'm getting the #Name? error. Do I need to be substituting any
variables
into the formula? What does the "d" signify?

--

 

[Douglas J. Steele]

DateDiff can calculate the the difference between two dates in a variety
of
formats. The "d" tells it to do the calculation as the number of days. It
can
do it in weeks, months and years as well - just replace with the relevant
first letter.

<picky>
To get the difference in the number of years, you need "yyyy", not "y", and
to get the difference in number of weeks, you need "ww", not "w"
</picky>

 

[Don]

I am trying to use this logic in a similar situation that I have where I need
to calculate two separate dates, one for "late" and one for "overdue". In my
scenario I have a control in a form that looks at several fields [beginDate],
[completionDate], and [dueDate]. All projects have a 60 day timeframe for
required completion, so the [dueDate] is a control set to [beginDate]+60.
That's the easy part. Now I need to indicate how many days a project is
overdue e.g. the[completionDate]Is Null and it has been greater than 60 days,
and another control needs to indicate how many days late the project was
completed, e.g the [completionDate]-[BeginDate]>60.

I have tried the following respectively but they don't seem to work properly
Overdue: IIf(([CompletionDate] Is Null) AND
(date()-[BeginDate]))>60,Sum(date()-[BeginDate])-60,"")

Late:
=IIf(([CompletionDate]-[BeginDate])>60,Sum([CompletionDate]-[BeginDate])-60,"")

Any ideas?

 

[storrboy]

I am trying to use this logic in a similar situation that I have where I need
to calculate two separate dates, one for "late" and one for "overdue". In my
scenario I have a control in a form that looks at several fields [beginDate],
[completionDate], and [dueDate]. All projects have a 60 day timeframe for
required completion, so the [dueDate] is a control set to [beginDate]+60.
That's the easy part. Now I need to indicate how many days a project is
overdue e.g. the[completionDate]Is Null and it has been greater than 60 days,
and another control needs to indicate how many days late the project was
completed, e.g the [completionDate]-[BeginDate]>60.

I have tried the following respectively but they don't seem to work properly
Overdue: IIf(([CompletionDate] Is Null) AND
(date()-[BeginDate]))>60,Sum(date()-[BeginDate])-60,"")

Late:
=IIf(([CompletionDate]-[BeginDate])>60,Sum([CompletionDate]-[BeginDate])-60,"")

Any ideas?

Overdue:
Is Null is only used in SQL. Use the IsNull() function in controls and
procedures ie. IsNull([CompletionDate]). Try using
=IIf((IsNull([CompletionDate]),"",IIF((date()-
[BeginDate])>60,Sum(date()-[BeginDate])-60,""))

Second you may find more reliable results by using the various date
functions (DateDiff, DateAdd etc.) instead of straight math.

 

[Don]

As recommende, I used the expression

=IIf(IsNull([CompletionDate]),"",IIF((date()-
[BeginDate])>60,Sum(date()-[BeginDate])-60,""))

Unfortunately this does not work for what I am after. This expression is
stating that if the completion date is null, then leave my return value null,
otherwise give me the difference between todays date and the [begin date]
less 60 days. For the "overdue" control, this calculation is to occur if the
[completion date] is null AND todays date is greater than 60 days past the
[begin date].

IIf(([begin date] + date ()) > 60 AND IIf(IsNull([CompletionDate]))),
Sum(date()-[BeginDate])-60, Ҡ(something is wrong with this expression so
it doesn't work)

If I could get the above expression to work, then I would be able to
calculate those items that are overdue past the 60 day deadline and indicate
the number of days that it is overdue.

For late items I need to show those items that do have a completion date
entered but where the [begin date]-[completion date] >60 so as to indicate
how many days over the 60 they were late.
--
Thanks!

I am trying to use this logic in a similar situation that I have where I need
to calculate two separate dates, one for "late" and one for "overdue". In my
scenario I have a control in a form that looks at several fields [beginDate],
[completionDate], and [dueDate]. All projects have a 60 day timeframe for
required completion, so the [dueDate] is a control set to [beginDate]+60.
That's the easy part. Now I need to indicate how many days a project is
overdue e.g. the[completionDate]Is Null and it has been greater than 60 days,
and another control needs to indicate how many days late the project was
completed, e.g the [completionDate]-[BeginDate]>60.

I have tried the following respectively but they don't seem to work properly
Overdue: IIf(([CompletionDate] Is Null) AND
(date()-[BeginDate]))>60,Sum(date()-[BeginDate])-60,"")

Late:
=IIf(([CompletionDate]-[BeginDate])>60,Sum([CompletionDate]-[BeginDate])-60,"")

Any ideas?

Overdue:
Is Null is only used in SQL. Use the IsNull() function in controls and
procedures ie. IsNull([CompletionDate]). Try using
=IIf((IsNull([CompletionDate]),"",IIF((date()-
[BeginDate])>60,Sum(date()-[BeginDate])-60,""))

Second you may find more reliable results by using the various date
functions (DateDiff, DateAdd etc.) instead of straight math.

 

[George Nicholson]

You can't use AND within an iif. Try a Nested Iif.

Overdue: =IIf(IsNull([CompletionDate]),
IIf((date()-[BeginDate])>60,(date()-[BeginDate])-60,""),"")

HTH,

As recommende, I used the expression

=IIf(IsNull([CompletionDate]),"",IIF((date()-
[BeginDate])>60,Sum(date()-[BeginDate])-60,""))

Unfortunately this does not work for what I am after. This expression is
stating that if the completion date is null, then leave my return value
null,
otherwise give me the difference between todays date and the [begin date]
less 60 days. For the "overdue" control, this calculation is to occur if
the
[completion date] is null AND todays date is greater than 60 days past the
[begin date].

IIf(([begin date] + date ()) > 60 AND IIf(IsNull([CompletionDate]))),
Sum(date()-[BeginDate])-60, "" (something is wrong with this expression
so
it doesn't work)

If I could get the above expression to work, then I would be able to
calculate those items that are overdue past the 60 day deadline and
indicate
the number of days that it is overdue.

For late items I need to show those items that do have a completion date
entered but where the [begin date]-[completion date] >60 so as to indicate
how many days over the 60 they were late.
--
Thanks!

I am trying to use this logic in a similar situation that I have where
I need
to calculate two separate dates, one for "late" and one for "overdue".
In my
scenario I have a control in a form that looks at several fields
[beginDate],
[completionDate], and [dueDate]. All projects have a 60 day timeframe
for
required completion, so the [dueDate] is a control set to
[beginDate]+60.
That's the easy part. Now I need to indicate how many days a project
is
overdue e.g. the[completionDate]Is Null and it has been greater than 60
days,
and another control needs to indicate how many days late the project
was
completed, e.g the [completionDate]-[BeginDate]>60.

I have tried the following respectively but they don't seem to work
properly
Overdue: IIf(([CompletionDate] Is Null) AND
(date()-[BeginDate]))>60,Sum(date()-[BeginDate])-60,"")

Late:
=IIf(([CompletionDate]-[BeginDate])>60,Sum([CompletionDate]-[BeginDate])-60,"")

Any ideas?

Overdue:
Is Null is only used in SQL. Use the IsNull() function in controls and
procedures ie. IsNull([CompletionDate]). Try using
=IIf((IsNull([CompletionDate]),"",IIF((date()-
[BeginDate])>60,Sum(date()-[BeginDate])-60,""))

Second you may find more reliable results by using the various date
functions (DateDiff, DateAdd etc.) instead of straight math.

 

[ANGY]

hola esto es solo una prueba

As recommende, I used the expression

=IIf(IsNull([CompletionDate]),"",IIF((date()-
[BeginDate])>60,Sum(date()-[BeginDate])-60,""))

Unfortunately this does not work for what I am after. This expression is
stating that if the completion date is null, then leave my return value
null,
otherwise give me the difference between todays date and the [begin date]
less 60 days. For the "overdue" control, this calculation is to occur if
the
[completion date] is null AND todays date is greater than 60 days past the
[begin date].

IIf(([begin date] + date ()) > 60 AND IIf(IsNull([CompletionDate]))),
Sum(date()-[BeginDate])-60, "" (something is wrong with this expression
so
it doesn't work)

If I could get the above expression to work, then I would be able to
calculate those items that are overdue past the 60 day deadline and
indicate
the number of days that it is overdue.

For late items I need to show those items that do have a completion date
entered but where the [begin date]-[completion date] >60 so as to indicate
how many days over the 60 they were late.
--
Thanks!

I am trying to use this logic in a similar situation that I have where
I need
to calculate two separate dates, one for "late" and one for "overdue".
In my
scenario I have a control in a form that looks at several fields
[beginDate],
[completionDate], and [dueDate]. All projects have a 60 day timeframe
for
required completion, so the [dueDate] is a control set to
[beginDate]+60.
That's the easy part. Now I need to indicate how many days a project
is
overdue e.g. the[completionDate]Is Null and it has been greater than 60
days,
and another control needs to indicate how many days late the project
was
completed, e.g the [completionDate]-[BeginDate]>60.

I have tried the following respectively but they don't seem to work
properly
Overdue: IIf(([CompletionDate] Is Null) AND
(date()-[BeginDate]))>60,Sum(date()-[BeginDate])-60,"")

Late:
=IIf(([CompletionDate]-[BeginDate])>60,Sum([CompletionDate]-[BeginDate])-60,"")

Any ideas?

Overdue:
Is Null is only used in SQL. Use the IsNull() function in controls and
procedures ie. IsNull([CompletionDate]). Try using
=IIf((IsNull([CompletionDate]),"",IIF((date()-
[BeginDate])>60,Sum(date()-[BeginDate])-60,""))

Second you may find more reliable results by using the various date
functions (DateDiff, DateAdd etc.) instead of straight math.

 

[Don]

Works perfectly! Thanks a ton!
--
Thanks!

You can't use AND within an iif. Try a Nested Iif.

Overdue: =IIf(IsNull([CompletionDate]),
IIf((date()-[BeginDate])>60,(date()-[BeginDate])-60,""),"")

HTH,

As recommende, I used the expression

=IIf(IsNull([CompletionDate]),"",IIF((date()-
[BeginDate])>60,Sum(date()-[BeginDate])-60,""))

Unfortunately this does not work for what I am after. This expression is
stating that if the completion date is null, then leave my return value
null,
otherwise give me the difference between todays date and the [begin date]
less 60 days. For the "overdue" control, this calculation is to occur if
the
[completion date] is null AND todays date is greater than 60 days past the
[begin date].

IIf(([begin date] + date ()) > 60 AND IIf(IsNull([CompletionDate]))),
Sum(date()-[BeginDate])-60, "" (something is wrong with this expression
so
it doesn't work)

If I could get the above expression to work, then I would be able to
calculate those items that are overdue past the 60 day deadline and
indicate
the number of days that it is overdue.

For late items I need to show those items that do have a completion date
entered but where the [begin date]-[completion date] >60 so as to indicate
how many days over the 60 they were late.
--
Thanks!

I am trying to use this logic in a similar situation that I have where
I need
to calculate two separate dates, one for "late" and one for "overdue".
In my
scenario I have a control in a form that looks at several fields
[beginDate],
[completionDate], and [dueDate]. All projects have a 60 day timeframe
for
required completion, so the [dueDate] is a control set to
[beginDate]+60.
That's the easy part. Now I need to indicate how many days a project
is
overdue e.g. the[completionDate]Is Null and it has been greater than 60
days,
and another control needs to indicate how many days late the project
was
completed, e.g the [completionDate]-[BeginDate]>60.

I have tried the following respectively but they don't seem to work
properly
Overdue: IIf(([CompletionDate] Is Null) AND
(date()-[BeginDate]))>60,Sum(date()-[BeginDate])-60,"")

Late:
=IIf(([CompletionDate]-[BeginDate])>60,Sum([CompletionDate]-[BeginDate])-60,"")

Any ideas?

--
Thanks!

Overdue:
Is Null is only used in SQL. Use the IsNull() function in controls and
procedures ie. IsNull([CompletionDate]). Try using
=IIf((IsNull([CompletionDate]),"",IIF((date()-
[BeginDate])>60,Sum(date()-[BeginDate])-60,""))

Second you may find more reliable results by using the various date
functions (DateDiff, DateAdd etc.) instead of straight math.