Compare months and years in date field 2

[honeypot3]

Hi there

I need to be able to create a calculated field in a query which determines if only the month and year of a date field in one table is between between the month and year of the [start date] and [end date] fields in another table. I am OK using Between to evaluate the whole date but need something which will allow the query to process the following example:

[Start date]: 13/3/05
[End date]: 22/7/06
[Date to evaluate]: 4/3/05

In this instance I would want the Iif statement to return a true message because it would only look at the month and the year component of the [date to evaluate] field so even though technically the [date to evaluate] field is not between the [start date] and [end date], it is from a point of view of its month and year values. Is this possible?

 

[Crowley16]

you can get the day/month/year parts of a date value by simply using:

Day(dateValue)
Month(dateValue)
Year(dateValue)

--------------------
Procrastinate Now!

 

[honeypot3]

Sorry for being so slow but how would I use these together in an IIf statement such as below to evaluate whether the month and year values of date 1 were between the start and finish date fields month and year values:

IIf([Date 1] Between [start date] And [finish date]),"Yes","No")

 

[Crowley16]

iif ((month(date1) > month(dateChk1) and month(date1) > year(dateChk1)) AND (month(date1) < month(DateChk2) and year(date1) < year(DateChk2)), true, false)

--------------------
Procrastinate Now!

 

[PHV]

IIf(Format([Date 1],'yyyymm')>=Format([start date],'yyyymm') And Format([Date 1],'yyyymm')<=Format([finish date],'yyyymm'), "Yes", "No")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244