Changing hard coded SQL to always get current year - within IIf Statem

[lmn]

I'm using the below statement to see if a record exists, and if it does - to mark it with an x. The first problem is that it's not marking it with an x - but Null or -1. I can work with this - all I want it to do is distinguish between the two.

The real problem is that I hardcoded this report for specific date ranges, but I want when it turns 2004 for the query to automatically change to 2004. My boyfriend, who codes in Lotus Notes, thought I could put {2/28/2003} but that went nowhere - fast. Because I have to do 12 Iif statements (one for each month) - how can I hardcode the SQL within each to only pull the date range specified for the active year???????



Feb: IIf([MinOfConst Start]<=#2/1/2003# And [MaxOfConst End]>=#2/1/2003#,&quot;x&quot;,Null) Or IIf([MinOfConst Start]<=#2/28/2003# And [MaxOfConst End]>=#2/28/2003#,&quot;x&quot;,Null) Or IIf([MaxofConst End]<=#2/28/2003# And [MinOfConst Start]>=#2/1/2003#,&quot;x&quot;,Null)

 

[jdegeorge]

Year(Date()) will always return the current year.

Jim DeGeorge

 

[lmn]

Yes, I know that - thanks for responding.

The issue is that you can't put that in an Iif statement - for instance - I can't put....

Feb: IIf([MinOfConst Start]<=#2/1/Year(Now())# And [MaxOfConst End]>=#2/1/Year(Now()))#,&quot;x&quot;,Null)

Am I making more sense???

~L

 

[jdegeorge]

[tt]Sorry... you would have to add it like:

Feb: IIf([MinOfConst Start]<=&quot;#2/1/&quot;&Year(Now())&&quot;#&quot; And [MaxOfConst End]>=&quot;#2/1/&quot;&Year(Now())&&quot;#&quot;,&quot;x&quot;,Null)

Depending on if this is in a Query, SQL or in a form, sometimes the syntax is different. I didn't try this but it's on the right track. Sorry that it doesn't come with a guarantee...I didn't have the time to try it.
[/tt]

Jim DeGeorge

 

[lmn]

Thanks for the assistance - doesn't work - says it has an INVALID DATE VALUE - but it gives me a start....I was thinking I may have to concatenate something - I just have to figure out how it likes it...

Thx