Comparing dates

[scoe]

Hi, I am sure I am thinking this is more compllicated than it is btu I'm hoping soemone can point me in the right and best direction. I have a table called months that shows each month in on column, a code and the dates the month covers e.g. August, W01, 15 Aug - 14 Sep.

MthNo	MonthDesc	ILRMonth	Dates
01	January	W06	15 Dec - 14 Jan
02	February	W07	15 Jan - 14 Feb
03	March	W08	15 Feb - 14 Mar

I need to write a query to look at a column in another table [DateRec] and if it falls between the dates showing in the dates column of the months table give me the month. The previous person to do this had used the mid function [Mid([DateRec],4,2)] but obviously this just brought up everything in the month.
My instinct is just to ignore the table and find a way of writing the query to look betwee the two dates.
Would be grateful for any ideas for this, thank you.

[lfpm062010]

Need more information.

1) [DateRec]? Is it text or date type? What does it look like? (ie, 12012013, Dec 01, 2013, .... or etc)

2) Can you show the query that you are currently using?

If it is not too late to change the design, I would break up the [Dates] Column into 2 columns to [BeginDate] and [EndDate]. That way, it will make the comparing easier. For example,

'This query will tell you if Now() will fall into which ILRMonth based on the [BeginDate] and [EndDate]
SELECT [ILRMonth] FROM Tablemonths where Now() BETWEEN [BeginDate] AND [EndDate];

[scoe]

Thank you
The [DateRec] is in short date format. The present query is:
[SELECT DISTINCT [Post Table].Partner, Months.ILRMonth, Months.MonthDesc
FROM [Post Table], Months
WHERE ((([Post Table].PostType) Like "E*" Or ([Post Table].PostType) Like "W*" Or ([Post Table].PostType) Like "C*") And ((Mid([DateRec],4,2))=Months.MthNo))
GROUP BY [Post Table].Partner, Months.ILRMonth, Months.MonthDesc, [Post Table].DateRec
HAVING ((([Post Table].DateRec)>=#8/1/2013#));]

This only brings up the full month from the First until the thirtieth ot thirtyfirst not as they want it 15th til the 14th of each month. The table doesn't have the year because that way it does not need to be renewed every year.

I have, at present gone the route you have suggested and split it to two rows and have made it work, each row with a full date and date format, however that will mean it will need to be changed every year which would be best avoided.

[lfpm062010]

?[DateRec] is store in MM/DD/YYYY format

If [DateRec] is shortdate DATE/TIME data type, then the Mid([DateRec], 4, 2) will not give you the correct month.
Use Format([DateRec], "MM") will give you the month value.

[scoe]

The Mid function worked but that isn't what is needed, the dates need to fall between those shown in the table e.g. 15th Jun - 14th Jul and the table shows it as text just to complicate things.

[lfpm062010]

Sorry for misunderstood your question.

This is one way solving your issue.
1) Build a query to show the [BeginDate] and [EndDate].
SELECT months.MthNo, months.MonthDesc, months.ILRMonth, months.Dates, CDate(Mid([months]![Dates],1,InStr(1,[months]![Dates],"-")-1)) AS BeginDate, CDate(Mid([months]![Dates],InStr(1,[months]![Dates],"-")+1)) AS EndDate
FROM months;

2) Create a query on [Post Table] with the Month using Mid([DateRec], 4, 2)
SELECT DISTINCT [Post Table].Partner, [Post Table].PostType, [Post Table].DateRec, Mid([DateRec],4,2) AS PostMthNo
FROM [Post Table]
WHERE [Post Table].PostType Like "E*" Or [Post Table].PostType Like "W*" Or [Post Table].PostType Like "C*"

3) Then join those 2 query using MthNo and PostMthNo
SELECT Query2.Partner, Query1.MthNo, Query1.MonthDesc
FROM Query1 INNER JOIN Query2 ON Query1.MthNo = Query2.PostMthNo
WHERE (((CDate(Query2.[DateRec])) Between [Query1].[BeginDate] And [Query1].[EndDate]));

[scoe]

Thank you so much, I'll give it a go.

[lfpm062010]

I just realized that the dates are year over year. The default year appended to it is current year (2013). So, I have made some adjustment. If the [BeginDate] is greater than [EndDate], it will be prior year. If not, it will be current year. I hope this will work for all the cases. You have to have year added to the [Dates] (ie, 15 Dec - 14 Jan change to 15 Dec, 2012 - 14 Jan, 2013..... etc) for it to work correctly.

The new query will be as below.

1)
SELECT months.MthNo, months.MonthDesc, months.ILRMonth, months.Dates, IIf(CDate(Mid([months]![Dates],1,InStr(1,[months]![Dates],"-")-1))>CDate(Mid([months]![Dates],InStr(1,[months]![Dates],"-")+1)),DateAdd("yyyy",-1,CDate(Mid([months]![Dates],1,InStr(1,[months]![Dates],"-")-1))),CDate(Mid([months]![Dates],1,InStr(1,[months]![Dates],"-")-1))) AS BeginDate, CDate(Mid([months]![Dates],InStr(1,[months]![Dates],"-")+1)) AS EndDate
FROM months;

2)
SELECT DISTINCT [Post Table].Partner, [Post Table].PostType, [Post Table].DateRec, Format([DateRec],"mm") AS PostMthNo
FROM [Post Table]
WHERE ((([Post Table].PostType) Like "E*" Or ([Post Table].PostType) Like "W*" Or ([Post Table].PostType) Like "C*"));

3)
SELECT Query15.Partner, Query14.MthNo, Query14.MonthDesc, CDate([Query15].[DateRec]) AS Expr1, Query14.BeginDate, Query14.EndDate
FROM Query14 INNER JOIN Query15 ON Query14.MthNo = Query15.PostMthNo
WHERE (((CDate([Query15].[DateRec])) Between [Query14].[BeginDate] And [Query14].[EndDate]));

[scoe]

Thank you so much for your help, this is great.