DateAdd and Month functions

[Ivan Pavlov]

Can someone please explain to me why the following SQL works in
Access:

SELECT JIRA,Title,Duration, [Assigned Date],Month(DateAdd("d",
[Duration], [Assigned Date]))
FROM Issues
WHERE Month([Assigned Date])=8
ORDER by Month(DateAdd("d", [Duration], [Assigned Date])) desc

But the following:

SELECT JIRA,Title,Duration, [Assigned Date],Month(DateAdd("d",
[Duration], [Assigned Date]))
FROM Issues
WHERE Month([Assigned Date])=8 And Month(DateAdd("d", [Duration],
[Assigned Date]))=9

returns "type mismatch"?

How can I include Month(DateAdd("d", [Duration], [Assigned Date])) in
where clause?

 

[Bob Quintal]

Can someone please explain to me why the following SQL works in
Access:

SELECT JIRA,Title,Duration, [Assigned Date],Month(DateAdd("d",
[Duration], [Assigned Date]))
FROM Issues
WHERE Month([Assigned Date])=8
ORDER by Month(DateAdd("d", [Duration], [Assigned Date])) desc

But the following:

SELECT JIRA,Title,Duration, [Assigned Date],Month(DateAdd("d",
[Duration], [Assigned Date]))
FROM Issues
WHERE Month([Assigned Date])=8 And Month(DateAdd("d", [Duration],
[Assigned Date]))=9

returns "type mismatch"?

How can I include Month(DateAdd("d", [Duration], [Assigned Date])) in
where clause?

x = 8 And x = 9 = 0
try changing the And to Or

 

[John Spencer]

If duration is ever blank (null or zero length string) or not a number then
your expression will generate an error - invalid use of null or type mismatch.

I suspect that is the cause of your problem.

You might be able to use something like the following to avoid the error. I
don't know if it will return what you want when duration is not a number value.
DateAdd("D",IIF(IsNumeric([Duration]),[Duration],0),[Assigned Date]) = 9


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

 

[Ivan Pavlov]

Thanks for the answer. The problem however is not in the calculation
of Month(DateAdd("d", [Duration], [Assigned Date])). This is fine as
you can see because it is in the SELECT-ed expressions. The problem
comes when I try to compare it with an integer - 9 in my case - then I
get a type mismatch. Even if I use Cint on the expression it still
produces the same error.

It is really frustrating...

If duration is ever blank (null or zero length string) or not a number then
your expression will generate an error - invalid use of null or type mismatch.

I suspect that is the cause of your problem.

You might be able to use something like the following to avoid the error. I
don't know if it will return what you want when duration is not a number value.
DateAdd("D",IIF(IsNumeric([Duration]),[Duration],0),[Assigned Date]) = 9

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

Can someone please explain to me why the following SQL works in
Access:

SELECT JIRA,Title,Duration, [Assigned Date],Month(DateAdd("d",
[Duration], [Assigned Date]))
FROM Issues
WHERE Month([Assigned Date])=8
ORDER by Month(DateAdd("d", [Duration], [Assigned Date])) desc

But the following:

SELECT JIRA,Title,Duration, [Assigned Date],Month(DateAdd("d",
[Duration], [Assigned Date]))
FROM Issues
WHERE Month([Assigned Date])=8 And Month(DateAdd("d", [Duration],
[Assigned Date]))=9

returns "type mismatch"?

How can I include Month(DateAdd("d", [Duration], [Assigned Date])) in
where clause?

 

[John W. Vinson]

Thanks for the answer. The problem however is not in the calculation
of Month(DateAdd("d", [Duration], [Assigned Date])). This is fine as
you can see because it is in the SELECT-ed expressions. The problem
comes when I try to compare it with an integer - 9 in my case - then I
get a type mismatch. Even if I use Cint on the expression it still
produces the same error.

One thing to check: do you have a private function named Month, or a field or
a control on a form? It's possible that Access isn't in fact hitting the
builtin Month() function.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[John Spencer]

Is DURATION ever Null? If so, the DateAdd function will return an error.
This is not necessarily a problem in a select clause if no record is returned
where duration is null.

In a where clause, every record may be checked and if even one record returns
an error, then the data engine will return a mismatch error.

So you need to change the where clause. You can try
WHERE Month([Assigned Date])=8
And IIF(Duration is Null,0,Month(DateAdd("d", [Duration],[Assigned Date])))=9

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

Thanks for the answer. The problem however is not in the calculation
of Month(DateAdd("d", [Duration], [Assigned Date])). This is fine as
you can see because it is in the SELECT-ed expressions. The problem
comes when I try to compare it with an integer - 9 in my case - then I
get a type mismatch. Even if I use Cint on the expression it still
produces the same error.

One thing to check: do you have a private function named Month, or a field or
a control on a form? It's possible that Access isn't in fact hitting the
builtin Month() function.

 

[Ivan Pavlov]

Is DURATION ever Null?  If so, the DateAdd function will return an error.
This is not necessarily a problem in a select clause if no record is returned
where duration is null.

In a where clause, every record may be checked and if even one record returns
an error, then the data engine will return a mismatch error.

So you need to change the where clause. You can try
WHERE Month([Assigned Date])=8
And IIF(Duration is Null,0,Month(DateAdd("d", [Duration],[Assigned Date])))=9

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

Thanks for the answer. The problem however is not in the calculation
of Month(DateAdd("d", [Duration], [Assigned Date])). This is fine as
you can see because it is in the SELECT-ed expressions. The problem
comes when I try to compare it with an integer - 9 in my case - then I
get a type mismatch. Even if I use Cint on the expression it still
produces the same error.

One thing to check: do you have a private function named Month, or a field or
a control on a form? It's possible that Access isn't in fact hitting the
builtin Month() function.

Thank you very much!!! Indeed this was the problem!
I simply can't get used to all Access oddities.

 

[John W. Vinson]

Is DURATION ever Null?  If so, the DateAdd function will return an error.
This is not necessarily a problem in a select clause if no record is returned
where duration is null.

In a where clause, every record may be checked and if even one record returns
an error, then the data engine will return a mismatch error.

So you need to change the where clause. You can try
WHERE Month([Assigned Date])=8
And IIF(Duration is Null,0,Month(DateAdd("d", [Duration],[Assigned Date])))=9

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

Thanks for the answer. The problem however is not in the calculation
of Month(DateAdd("d", [Duration], [Assigned Date])). This is fine as
you can see because it is in the SELECT-ed expressions. The problem
comes when I try to compare it with an integer - 9 in my case - then I
get a type mismatch. Even if I use Cint on the expression it still
produces the same error.

One thing to check: do you have a private function named Month, or a field or
a control on a form? It's possible that Access isn't in fact hitting the
builtin Month() function.

Thank you very much!!! Indeed this was the problem!
I simply can't get used to all Access oddities.

Well, I don't really see that as an "Access oddity" - unless you can tell me
the exact date of <mmpf> months from today. <g>
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com