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?

Ivan Pavlov <(E-Mail Removed)> wrote in news:126a4e41-4e75-4cfb-
ba98-(E-Mail Removed):

> 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


--
Bob Q.
PA is y I've altered my address.

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 wrote:
> 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?

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...


On Oct 20, 7:33*pm, John Spencer <JSPEN...@Hilltop.umbc> wrote:
> 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 wrote:
> > 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?

On Sat, 23 Oct 2010 13:53:03 -0700 (PDT), Ivan Pavlov <(E-Mail Removed)>
wrote:

>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/For...-US/accessdev/
http://social.answers.microsoft.com/.../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

John W. Vinson wrote:
> On Sat, 23 Oct 2010 13:53:03 -0700 (PDT), Ivan Pavlov <(E-Mail Removed)>
> wrote:
>
>> 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.

On Oct 24, 3:16*pm, John Spencer <JSPEN...@Hilltop.umbc> wrote:
> 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
>
> John W. Vinson wrote:
> > On Sat, 23 Oct 2010 13:53:03 -0700 (PDT), Ivan Pavlov <ivan.pav...@gmail.com>
> > wrote:
>
> >> 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.

On Mon, 25 Oct 2010 06:38:18 -0700 (PDT), Ivan Pavlov <(E-Mail Removed)>
wrote:

>On Oct 24, 3:16*pm, John Spencer <JSPEN...@Hilltop.umbc> wrote:
>> 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
>>
>> John W. Vinson wrote:
>> > On Sat, 23 Oct 2010 13:53:03 -0700 (PDT), Ivan Pavlov <ivan.pav...@gmail.com>
>> > wrote:
>>
>> >> 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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com