DateAdd and Month functions

  • Thread starter Thread starter Ivan Pavlov
  • Start date Start date
I

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?
 
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?
Click to expand...
x = 8 And x = 9 = 0
try changing the And to Or
 
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
 
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



Ivan said:
Can someone please explain to me why the following SQL works in
Access:
Click to expand...
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
Click to expand...
But the following:
Click to expand...
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
Click to expand...
returns "type mismatch"?
Click to expand...
How can I include Month(DateAdd("d", [Duration], [Assigned Date])) in
where clause?
Click to expand...
Click to expand...
 
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.
Click to expand...

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
 
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.
Click to expand...

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.
Click to expand...
 
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.
Click to expand...
Click to expand...
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.
Click to expand...
Click to expand...

Thank you very much!!! Indeed this was the problem!
I simply can't get used to all Access oddities.
 
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.
Click to expand...
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.
Click to expand...
Click to expand...

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

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
 
Back
Top