Datatype mismatch in criteria expression

[Nati323]

Hey I wrote this query:

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#101094]SELECT[/COLOR][COLOR=#303336] dayar 
[/COLOR][COLOR=#101094]FROM[/COLOR][COLOR=#303336]   dayarim 
[/COLOR][COLOR=#101094]WHERE[/COLOR][COLOR=#303336]  [/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336] Year[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]enter_month[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Year[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Now[/COLOR][COLOR=#303336]())[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] 
    [/COLOR][COLOR=#101094]OR[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336] Year[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]enter_month[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]<[/COLOR][COLOR=#303336] Year[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Now[/COLOR][COLOR=#303336]())[/COLOR][COLOR=#303336] 
         [/COLOR][COLOR=#101094]AND[/COLOR][COLOR=#303336] Year[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]left_month[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]>=[/COLOR][COLOR=#303336] Year[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Now[/COLOR][COLOR=#303336]())[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] 
       [/COLOR][COLOR=#101094]AND[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336] Datediff[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]'m'[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] Iif[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Year[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]enter_month[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Year[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Now[/COLOR][COLOR=#303336]()),[/COLOR][COLOR=#303336] enter_month[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] 
                                 [/COLOR][COLOR=#7D2727]'1/1/'[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]+[/COLOR][COLOR=#303336] Year[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Now[/COLOR][COLOR=#303336]())),[/COLOR][COLOR=#303336] Now[/COLOR][COLOR=#303336]())[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]*[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]50[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]<[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336] 
           Datediff[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]'m'[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]'1/1/'[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]+[/COLOR][COLOR=#303336] Year[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Now[/COLOR][COLOR=#303336]()),[/COLOR][COLOR=#303336] Now[/COLOR][COLOR=#303336]())[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]*[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]50[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR]</code>

And I get this error:

Datatype mismatch in criteria expression

My table structure:

My table content:

I red that this error means that I try to compare some data type with another data type
but I dont understand where I do it

 

[Nati323]

I can't edit, but the correct query is:

[FONT=&quot]SELECT[/FONT][FONT=&quot] [/FONT][FONT=&quot]dayar[/FONT][FONT=&quot] [/FONT]
[FONT=&quot]FROM[/FONT][FONT=&quot]   [/FONT][FONT=&quot]dayarim[/FONT][FONT=&quot] [/FONT]
[FONT=&quot]WHERE[/FONT][FONT=&quot]  [/FONT][FONT=&quot]([/FONT][FONT=&quot] [/FONT][FONT=&quot]Year[/FONT][FONT=&quot]([/FONT][FONT=&quot]enter_month[/FONT][FONT=&quot])[/FONT][FONT=&quot] [/FONT][FONT=&quot]=[/FONT][FONT=&quot] [/FONT][FONT=&quot]Year[/FONT][FONT=&quot]([/FONT][FONT=&quot]Now[/FONT][FONT=&quot]([/FONT][FONT=&quot])[/FONT][FONT=&quot])[/FONT][FONT=&quot] [/FONT][FONT=&quot])[/FONT][FONT=&quot] [/FONT]
[FONT=&quot]        [/FONT][FONT=&quot]OR[/FONT][FONT=&quot] [/FONT][FONT=&quot]([/FONT][FONT=&quot] [/FONT][FONT=&quot]Year[/FONT][FONT=&quot]([/FONT][FONT=&quot]enter_month[/FONT][FONT=&quot])[/FONT][FONT=&quot] [/FONT][FONT=&quot]<[/FONT][FONT=&quot] [/FONT][FONT=&quot]Year[/FONT][FONT=&quot]([/FONT][FONT=&quot]Now[/FONT][FONT=&quot]([/FONT][FONT=&quot])[/FONT][FONT=&quot])[/FONT][FONT=&quot] [/FONT]
[FONT=&quot]             [/FONT][FONT=&quot]AND[/FONT][FONT=&quot] [/FONT][FONT=&quot]Year[/FONT][FONT=&quot]([/FONT][FONT=&quot]left_month[/FONT][FONT=&quot])[/FONT][FONT=&quot] [/FONT][FONT=&quot]>=[/FONT][FONT=&quot] [/FONT][FONT=&quot]Year[/FONT][FONT=&quot]([/FONT][FONT=&quot]Now[/FONT][FONT=&quot]([/FONT][FONT=&quot])[/FONT][FONT=&quot])[/FONT][FONT=&quot] [/FONT][FONT=&quot])[/FONT][FONT=&quot] [/FONT]
[FONT=&quot]           [/FONT][FONT=&quot]AND[/FONT][FONT=&quot] [/FONT][FONT=&quot]([/FONT][FONT=&quot]SELECT[/FONT][FONT=&quot] [/FONT][FONT=&quot]Sum[/FONT][FONT=&quot]([/FONT][FONT=&quot]payments[/FONT][FONT=&quot].[/FONT][FONT=&quot]sum[/FONT][FONT=&quot])[/FONT][FONT=&quot] [/FONT]
[FONT=&quot]                [/FONT][FONT=&quot]FROM[/FONT][FONT=&quot]   [/FONT][FONT=&quot]payments[/FONT][FONT=&quot] [/FONT]
[FONT=&quot]                [/FONT][FONT=&quot]WHERE[/FONT][FONT=&quot]  [/FONT][FONT=&quot]payments[/FONT][FONT=&quot].[/FONT][FONT=&quot]paymentdate[/FONT][FONT=&quot] [/FONT][FONT=&quot]>[/FONT][FONT=&quot] [/FONT][FONT=&quot]'1/1'[/FONT][FONT=&quot] [/FONT][FONT=&quot]+[/FONT][FONT=&quot] [/FONT][FONT=&quot]Year[/FONT][FONT=&quot]([/FONT][FONT=&quot]Now[/FONT][FONT=&quot]([/FONT][FONT=&quot])[/FONT][FONT=&quot])[/FONT][FONT=&quot] [/FONT]
[FONT=&quot]                       [/FONT][FONT=&quot]AND[/FONT][FONT=&quot] [/FONT][FONT=&quot]paymentdate[/FONT][FONT=&quot] [/FONT][FONT=&quot]<=[/FONT][FONT=&quot] [/FONT][FONT=&quot]Now[/FONT][FONT=&quot]([/FONT][FONT=&quot])[/FONT][FONT=&quot] [/FONT]
[FONT=&quot]                       [/FONT][FONT=&quot]AND[/FONT][FONT=&quot] [/FONT][FONT=&quot]payerid[/FONT][FONT=&quot] [/FONT][FONT=&quot]=[/FONT][FONT=&quot] [/FONT][FONT=&quot]dayarim[/FONT][FONT=&quot].[/FONT][FONT=&quot]id[/FONT][FONT=&quot])[/FONT][FONT=&quot] [/FONT][FONT=&quot]<[/FONT][FONT=&quot] [/FONT][FONT=&quot]([/FONT][FONT=&quot] [/FONT]
[FONT=&quot]               [/FONT][FONT=&quot]Datediff[/FONT][FONT=&quot]([/FONT][FONT=&quot]'m'[/FONT][FONT=&quot],[/FONT][FONT=&quot] [/FONT][FONT=&quot]'1/1/'[/FONT][FONT=&quot] [/FONT][FONT=&quot]+[/FONT][FONT=&quot] [/FONT][FONT=&quot]Year[/FONT][FONT=&quot]([/FONT][FONT=&quot]Now[/FONT][FONT=&quot]([/FONT][FONT=&quot])[/FONT][FONT=&quot])[/FONT][FONT=&quot],[/FONT][FONT=&quot] [/FONT][FONT=&quot]Now[/FONT][FONT=&quot]([/FONT][FONT=&quot])[/FONT][FONT=&quot])[/FONT][FONT=&quot] [/FONT][FONT=&quot]*[/FONT][FONT=&quot] [/FONT][FONT=&quot]50[/FONT][FONT=&quot] [/FONT][FONT=&quot])[/FONT][FONT=&quot];[/FONT][FONT=&quot] 

[/FONT]

 

[Joe4]

Welcome to the Board!

So, does this mean it has been resolved, or is it still a problem?

If it is still a problem, I suspect the issue is here with these caclulations:

[COLOR=red]'1/1'[/COLOR][COLOR=silver]+[/COLOR][COLOR=fuchsia][I]Year[/I][/COLOR][COLOR=maroon]([/COLOR][COLOR=#FF0080][B]Now[/B][/COLOR][COLOR=maroon]([/COLOR][COLOR=maroon])[/COLOR][COLOR=maroon])[/COLOR]

I would build the date like this:

DateSerial(Year(Now()),1,1)

I see two places where you would need to change that.

 

[Nati323]

Hey, thanks its works

 

[Joe4]

You are welcome!

The issue was that the way you were trying to build your date builds a string, so you were trying to compare a date to a string (hence, your datatype mismatch).

 

[Nati323]

thanks for the explain, I assume it's something like that but I don't understand where

 

[james_lankford]

according to your image
left_month
is blank

yet you use that field in a calculation
AND Year(left_month)>=Year(Now()))

so that should fail
so I don't understand why its working now