Looking up a date by month and year

[DLSE]

I have a list of dates in a table. They are in the short date format (ie. 5/16/03). I would like to look these dates up by month and year, and return only the month and year to the query. I cannot use criteria in the "date" field of the query, as the entire date, including the day, would be returned. I am trying to group data by month and year, and when unique days are recognized, it the query separates this data. I realize that this is probably fairly simple, but I am a new access user and have spent alot of time trying to figure this out, so any help is greatly appreciated.
Thanks,
Matt

 

[swampBoogie]

Your question is rather unclear but maybe this is what you want.

select distinct month(dateColum), year(dateColumn) from t

 

[PassingBy]

Create a new column in your query call it Month:datepart("m",datefieldname)

This will give you the month of your datefield

Same again for year
Year:datepart("yyyy",datefieldname)

Then in the criteria enter the Month number and Year that you require extracted.

Hope this helps
Martin

 

[DLSE]

PassingBy-
Thanks for the help, the Month column works great! However, the year column returns years different than those that are entered in the table. For example, for a date that reads "12/12/01" in the table, a year of "346" is returned to the query. Any further suggestions from anyone?
Thanks again,
Matt

 

[PassingBy]

Matt
Been away for a few days, not sure why your results are doing this, I am using UK date format (proper ones!) i.e 30/04/2003 but this should work irrespective of how the data is stored in the underlying table.

You might need to check your date settings within Access and your Regional settings to make sure they do not conflict.

This is a copy of the SQL in my query

SELECT tblSAPResult.EffectiveDate, DatePart("m",[effectivedate]) AS month, DatePart("yyyy",[effectivedate]) AS Year
FROM tblSAPResult
GROUP BY tblSAPResult.EffectiveDate, DatePart("m",[effectivedate]), DatePart("yyyy",[effectivedate]);

My results look like:
EffectiveDate month Year
30/04/02 4 2002
30/04/03 4 2003

PassingBy again...