IIf statement in Access 2003 query

[Pat B]

I’m trying to write an IIf statement in an Access query that I’m having
problems with. I want to calculate the number of days between a Date
Received and Date Left. If the Date Left is null, then I want to calculate
the number of days between Date Received and date query is run. To calculate
the number of days where both fields have a value, the following works:
Total DaysateDiff(“dâ€,[Date Recd],[Date Left].
I tried the following to incorporate instances where Date Left is blank,but
it says it contains invalid syntax.
Total Days:IIf(isNull[DateLeft],DateDiff(“dâ€,[Date
Recd,[Date()],DateDiff(“dâ€,[Date Recd],[Date Left])))
Help will really be appreciated.

 

[fredg]

Iÿm trying to write an IIf statement in an Access query that Iÿm having
problems with. I want to calculate the number of days between a Date
Received and Date Left. If the Date Left is null, then I want to calculate
the number of days between Date Received and date query is run. To calculate
the number of days where both fields have a value, the following works:
Total DaysateDiff(´d¡,[Date Recd],[Date Left].
I tried the following to incorporate instances where Date Left is blank,but
it says it contains invalid syntax.
Total Days:IIf(isNull[DateLeft],DateDiff(´d¡,[Date
Recd,[Date()],DateDiff(´d¡,[Date Recd],[Date Left])))
Help will really be appreciated.

Well you're close. Just a question of incorrect placement of a ")" and
extra un-needed "[]"'s

1) The function IsNull must have it's criteria field enclosed
directly with ), not at the end of the IIf function parenthesis,
i.e. IsNull([FieldName]).

2) Date() is a function name not a field name, so do NOT enclose it
within brackets [].

Try:

Total Days:IIf(isNull([Date Left]),DateDiff(´d¡,[Date
Recd],Date()),DateDiff(´d¡,[Date Recd],[Date Left]))

 

[John Spencer MVP]

Close but remove the square brackets around Date(). You want to use the
Date() function and you are telling the query to use a field named Date() when
you surround it with square brackets.

Total Days:IIf(isNull[Date Left]
,DateDiff("d",[Date Recd,Date()
,DateDiff("d",[Date Recd],[Date Left])))

Or you could use the NZ function and simplify the expression:
Total Days: DateDiff("d",[Date Recd],Nz([Date Left],Date()))

Also, test "DateLeft", but refer elsewhere to "Date Left" (at least in your
posting).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Pat B]

When I type in your suggestion it asks me for a parameter value for "d"?

I’m trying to write an IIf statement in an Access query that I’m having
problems with. I want to calculate the number of days between a Date
Received and Date Left. If the Date Left is null, then I want to calculate
the number of days between Date Received and date query is run. To calculate
the number of days where both fields have a value, the following works:
Total DaysateDiff(“dâ€,[Date Recd],[Date Left].
I tried the following to incorporate instances where Date Left is blank,but
it says it contains invalid syntax.
Total Days:IIf(isNull[DateLeft],DateDiff(“dâ€,[Date
Recd,[Date()],DateDiff(“dâ€,[Date Recd],[Date Left])))
Help will really be appreciated.

Well you're close. Just a question of incorrect placement of a ")" and
extra un-needed "[]"'s

1) The function IsNull must have it's criteria field enclosed
directly with ), not at the end of the IIf function parenthesis,
i.e. IsNull([FieldName]).

2) Date() is a function name not a field name, so do NOT enclose it
within brackets [].

Try:

Total Days:IIf(isNull([Date Left]),DateDiff(“dâ€,[Date
Recd],Date()),DateDiff(“dâ€,[Date Recd],[Date Left]))

 

[Pat B]

John, the Nz expression worked great! And it's so much simpler. Thanks so
very much and have a great weekend. Of course, I don't understand the
expression--what does Nz stand for?

Close but remove the square brackets around Date(). You want to use the
Date() function and you are telling the query to use a field named Date() when
you surround it with square brackets.

Total Days:IIf(isNull[Date Left]
,DateDiff("d",[Date Recd,Date()
,DateDiff("d",[Date Recd],[Date Left])))

Or you could use the NZ function and simplify the expression:
Total Days: DateDiff("d",[Date Recd],Nz([Date Left],Date()))

Also, test "DateLeft", but refer elsewhere to "Date Left" (at least in your
posting).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I’m trying to write an IIf statement in an Access query that I’m having
problems with. I want to calculate the number of days between a Date
Received and Date Left. If the Date Left is null, then I want to calculate
the number of days between Date Received and date query is run. To calculate
the number of days where both fields have a value, the following works:
Total DaysateDiff(“dâ€,[Date Recd],[Date Left].
I tried the following to incorporate instances where Date Left is blank,but
it says it contains invalid syntax.
Total Days:IIf(isNull[DateLeft],DateDiff(“dâ€,[Date
Recd,[Date()],DateDiff(“dâ€,[Date Recd],[Date Left])))
Help will really be appreciated.

 

[John Spencer MVP]

NZ (Null to Zero)

Returns the value of the first argument unless it is null, then it returns the
value of the second argument.

VBA Help has a fuller explanation and examples of how it can be used.

You can do the same thing using an IIF statement.
In a query you would use
IIF(Date Left] is NOT NULL,[Date Left],Date())

Elsewhere (also works in a query)you can use the following, but note the
reversal of the responses.
IIF(IsNull([Date Left]),Date(),[Date Left])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John, the Nz expression worked great! And it's so much simpler. Thanks so
very much and have a great weekend. Of course, I don't understand the
expression--what does Nz stand for?

Close but remove the square brackets around Date(). You want to use the
Date() function and you are telling the query to use a field named Date() when
you surround it with square brackets.

Total Days:IIf(isNull[Date Left]
,DateDiff("d",[Date Recd,Date()
,DateDiff("d",[Date Recd],[Date Left])))

Or you could use the NZ function and simplify the expression:
Total Days: DateDiff("d",[Date Recd],Nz([Date Left],Date()))

Also, test "DateLeft", but refer elsewhere to "Date Left" (at least in your
posting).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I’m trying to write an IIf statement in an Access query that I’m having
problems with. I want to calculate the number of days between a Date
Received and Date Left. If the Date Left is null, then I want to calculate
the number of days between Date Received and date query is run. To calculate
the number of days where both fields have a value, the following works:
Total DaysateDiff(“dâ€,[Date Recd],[Date Left].
I tried the following to incorporate instances where Date Left is blank,but
it says it contains invalid syntax.
Total Days:IIf(isNull[DateLeft],DateDiff(“dâ€,[Date
Recd,[Date()],DateDiff(“dâ€,[Date Recd],[Date Left])))
Help will really be appreciated.

 

[Pat B]

Since I now have to figure the number of workdays only for this query, I had
written expressions based on taking out the weekends, which worked fine for
those that had values in both the Date Recd and Date Left Fields. Now that I
need to address those with a null value in the Date Left field, I have to
change this expression. This is what I had: Weekends: DateDiff("ww",[Date
Recd],[Date Left]. Then to get the weekdays I used: TotalWeekdays:[Total
Days]-([Weekends]*2)
Since you are such a wiz at this, can you help me again with an IIf
statement to figure out weekends during this same period whether the Date
Left field is populated or not? I can then do the final expression to minus
out the weekends.

NZ (Null to Zero)

Returns the value of the first argument unless it is null, then it returns the
value of the second argument.

VBA Help has a fuller explanation and examples of how it can be used.

You can do the same thing using an IIF statement.
In a query you would use
IIF(Date Left] is NOT NULL,[Date Left],Date())

Elsewhere (also works in a query)you can use the following, but note the
reversal of the responses.
IIF(IsNull([Date Left]),Date(),[Date Left])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John, the Nz expression worked great! And it's so much simpler. Thanks so
very much and have a great weekend. Of course, I don't understand the
expression--what does Nz stand for?

Close but remove the square brackets around Date(). You want to use the
Date() function and you are telling the query to use a field named Date() when
you surround it with square brackets.

Total Days:IIf(isNull[Date Left]
,DateDiff("d",[Date Recd,Date()
,DateDiff("d",[Date Recd],[Date Left])))

Or you could use the NZ function and simplify the expression:
Total Days: DateDiff("d",[Date Recd],Nz([Date Left],Date()))

Also, test "DateLeft", but refer elsewhere to "Date Left" (at least in your
posting).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Pat B wrote:
I’m trying to write an IIf statement in an Access query that I’m having
problems with. I want to calculate the number of days between a Date
Received and Date Left. If the Date Left is null, then I want to calculate
the number of days between Date Received and date query is run. To calculate
the number of days where both fields have a value, the following works:
Total DaysateDiff(“dâ€,[Date Recd],[Date Left].
I tried the following to incorporate instances where Date Left is blank,but
it says it contains invalid syntax.
Total Days:IIf(isNull[DateLeft],DateDiff(“dâ€,[Date
Recd,[Date()],DateDiff(“dâ€,[Date Recd],[Date Left])))
Help will really be appreciated.

 

[Pat B]

I still need help with the last portion of this query if John of Fred can help?

Since I now have to figure the number of workdays only for this query, I had
written expressions based on taking out the weekends, which worked fine for
those that had values in both the Date Recd and Date Left Fields. Now that I
need to address those with a null value in the Date Left field, I have to
change this expression. This is what I had: Weekends: DateDiff("ww",[Date
Recd],[Date Left]. Then to get the weekdays I used: TotalWeekdays:[Total
Days]-([Weekends]*2)
Since you are such a wiz at this, can you help me again with an IIf
statement to figure out weekends during this same period whether the Date
Left field is populated or not? I can then do the final expression to minus
out the weekends.

NZ (Null to Zero)

Returns the value of the first argument unless it is null, then it returns the
value of the second argument.

VBA Help has a fuller explanation and examples of how it can be used.

You can do the same thing using an IIF statement.
In a query you would use
IIF(Date Left] is NOT NULL,[Date Left],Date())

Elsewhere (also works in a query)you can use the following, but note the
reversal of the responses.
IIF(IsNull([Date Left]),Date(),[Date Left])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John, the Nz expression worked great! And it's so much simpler. Thanks so
very much and have a great weekend. Of course, I don't understand the
expression--what does Nz stand for?

:

Close but remove the square brackets around Date(). You want to use the
Date() function and you are telling the query to use a field named Date() when
you surround it with square brackets.

Total Days:IIf(isNull[Date Left]
,DateDiff("d",[Date Recd,Date()
,DateDiff("d",[Date Recd],[Date Left])))

Or you could use the NZ function and simplify the expression:
Total Days: DateDiff("d",[Date Recd],Nz([Date Left],Date()))

Also, test "DateLeft", but refer elsewhere to "Date Left" (at least in your
posting).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Pat B wrote:
I’m trying to write an IIf statement in an Access query that I’m having
problems with. I want to calculate the number of days between a Date
Received and Date Left. If the Date Left is null, then I want to calculate
the number of days between Date Received and date query is run. To calculate
the number of days where both fields have a value, the following works:
Total DaysateDiff(“dâ€,[Date Recd],[Date Left].
I tried the following to incorporate instances where Date Left is blank,but
it says it contains invalid syntax.
Total Days:IIf(isNull[DateLeft],DateDiff(“dâ€,[Date
Recd,[Date()],DateDiff(“dâ€,[Date Recd],[Date Left])))
Help will really be appreciated.

 

[John Spencer]

Can you use the same thing in your weekends calculation?

Weekends: DateDiff("ww",[Date Recd],NZ([Date Left],Date())

You might find that you need to do the following, since at times Access
won't recognize a calculated field within the same query.

Total WeekDays: DateDiff("d",[Date Recd],Nz([Date Left],Date())) - (2 *
DateDiff("ww",[Date Recd],NZ([Date Left],Date()))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Since I now have to figure the number of workdays only for this query, I had
written expressions based on taking out the weekends, which worked fine for
those that had values in both the Date Recd and Date Left Fields. Now that I
need to address those with a null value in the Date Left field, I have to
change this expression. This is what I had: Weekends: DateDiff("ww",[Date
Recd],[Date Left]. Then to get the weekdays I used: TotalWeekdays:[Total
Days]-([Weekends]*2)
Since you are such a wiz at this, can you help me again with an IIf
statement to figure out weekends during this same period whether the Date
Left field is populated or not? I can then do the final expression to minus
out the weekends.

NZ (Null to Zero)

Returns the value of the first argument unless it is null, then it returns the
value of the second argument.

VBA Help has a fuller explanation and examples of how it can be used.

You can do the same thing using an IIF statement.
In a query you would use
IIF(Date Left] is NOT NULL,[Date Left],Date())

Elsewhere (also works in a query)you can use the following, but note the
reversal of the responses.
IIF(IsNull([Date Left]),Date(),[Date Left])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John, the Nz expression worked great! And it's so much simpler. Thanks so
very much and have a great weekend. Of course, I don't understand the
expression--what does Nz stand for?

:

Close but remove the square brackets around Date(). You want to use the
Date() function and you are telling the query to use a field named Date() when
you surround it with square brackets.

Total Days:IIf(isNull[Date Left]
,DateDiff("d",[Date Recd,Date()
,DateDiff("d",[Date Recd],[Date Left])))

Or you could use the NZ function and simplify the expression:
Total Days: DateDiff("d",[Date Recd],Nz([Date Left],Date()))

Also, test "DateLeft", but refer elsewhere to "Date Left" (at least in your
posting).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Pat B wrote:
I’m trying to write an IIf statement in an Access query that I’m having
problems with. I want to calculate the number of days between a Date
Received and Date Left. If the Date Left is null, then I want to calculate
the number of days between Date Received and date query is run. To calculate
the number of days where both fields have a value, the following works:
Total DaysateDiff(“dâ€,[Date Recd],[Date Left].
I tried the following to incorporate instances where Date Left is blank,but
it says it contains invalid syntax.
Total Days:IIf(isNull[DateLeft],DateDiff(“dâ€,[Date
Recd,[Date()],DateDiff(“dâ€,[Date Recd],[Date Left])))
Help will really be appreciated.

 

[Pat B]

John, once again you have been a tremendous help to me. In a department of
1,000 here I have no Access support, so you have saved me so much time in
getting me through this problem. This resource is just a lifesaver. I was
able to successfully use the calculated field that was created in the query
(found that if I closed the file, and then reopened, the calculated fields
appeared in the pull down list). So my data is all correct now and can be
shared with others with the confidence that it is correct! Thanks and have a
great day!

Can you use the same thing in your weekends calculation?

Weekends: DateDiff("ww",[Date Recd],NZ([Date Left],Date())

You might find that you need to do the following, since at times Access
won't recognize a calculated field within the same query.

Total WeekDays: DateDiff("d",[Date Recd],Nz([Date Left],Date())) - (2 *
DateDiff("ww",[Date Recd],NZ([Date Left],Date()))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Since I now have to figure the number of workdays only for this query, I had
written expressions based on taking out the weekends, which worked fine for
those that had values in both the Date Recd and Date Left Fields. Now that I
need to address those with a null value in the Date Left field, I have to
change this expression. This is what I had: Weekends: DateDiff("ww",[Date
Recd],[Date Left]. Then to get the weekdays I used: TotalWeekdays:[Total
Days]-([Weekends]*2)
Since you are such a wiz at this, can you help me again with an IIf
statement to figure out weekends during this same period whether the Date
Left field is populated or not? I can then do the final expression to minus
out the weekends.

NZ (Null to Zero)

Returns the value of the first argument unless it is null, then it returns the
value of the second argument.

VBA Help has a fuller explanation and examples of how it can be used.

You can do the same thing using an IIF statement.
In a query you would use
IIF(Date Left] is NOT NULL,[Date Left],Date())

Elsewhere (also works in a query)you can use the following, but note the
reversal of the responses.
IIF(IsNull([Date Left]),Date(),[Date Left])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Pat B wrote:
John, the Nz expression worked great! And it's so much simpler. Thanks so
very much and have a great weekend. Of course, I don't understand the
expression--what does Nz stand for?

:

Close but remove the square brackets around Date(). You want to use the
Date() function and you are telling the query to use a field named Date() when
you surround it with square brackets.

Total Days:IIf(isNull[Date Left]
,DateDiff("d",[Date Recd,Date()
,DateDiff("d",[Date Recd],[Date Left])))

Or you could use the NZ function and simplify the expression:
Total Days: DateDiff("d",[Date Recd],Nz([Date Left],Date()))

Also, test "DateLeft", but refer elsewhere to "Date Left" (at least in your
posting).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Pat B wrote:
I’m trying to write an IIf statement in an Access query that I’m having
problems with. I want to calculate the number of days between a Date
Received and Date Left. If the Date Left is null, then I want to calculate
the number of days between Date Received and date query is run. To calculate
the number of days where both fields have a value, the following works:
Total DaysateDiff(“dâ€,[Date Recd],[Date Left].
I tried the following to incorporate instances where Date Left is blank,but
it says it contains invalid syntax.
Total Days:IIf(isNull[DateLeft],DateDiff(“dâ€,[Date
Recd,[Date()],DateDiff(“dâ€,[Date Recd],[Date Left])))
Help will really be appreciated.