showing extracted date as date, not string 2

[THWatson]

Using Access 2003 (2000 format)

Following is the SQL for a query

SELECT tblAttendance.MeetingDate, tblAttendance.MeetingTypeID, Format([MeetingDate],"mmmm  yyyy") AS MonthYear
FROM tblAttendance
GROUP BY tblAttendance.MeetingDate, tblAttendance.MeetingTypeID, [b]Format([MeetingDate],"mmmm  yyyy")[/b]
HAVING (((tblAttendance.MeetingTypeID)=1));

The problem is the piece of code in Bold print. I want to pull just the Month and Year from the MeetingDate field, and show that as a Date value, but it extracts as String.

I have also tried #" & Format(MeetingDate, "mmmm yyyy") & "# but that results in "the expression entered has invalid date value."

Suggestions would be appreciated.

Tom

 

[PWise]

Can you give some examples please

 

[THWatson]

PWise
Examples...

MeetingDate	MeetingTypeID	MonthYear
10/6/2005	1	October  2005
10/13/2005	1	October  2005
10/20/2005	1	October  2005
10/27/2005	1	October  2005
11/3/2005	1	November  2005
11/10/2005	1	November  2005
11/17/2005	1	November  2005
11/24/2005	1	November  2005

The MonthYear values - October 2005, etc. - are all Strings.

Tom

Tom

 

[lespaul]

What are you trying to do with the "date" values of Month and Year? You understand that the value of the date is this:

7/7/2010 7:51 am

40366.32761

where 40366 is the number of days since Dec 30 1899 and the decimal 32761 is the time of day.



Leslie

 

[dhookom]

A Date data type value is a point in time that includes more than just year and month. Format() returns a string. If you want a date, you must include the day of the month. If you aren't concerned about which day in the month, use:

YearMonth: DateSerial(Year(MeetingDate),Month(MeetingDate),1)

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

What I am trying to do by pulling the dates into "Month Year" is then count the number of weekly Meetings per month. It all has to do with calculating...down the line from this query...perfect attendance for club members.

Here's the SQL for the next query in line

SELECT qryRegularMeetingsByMonthAndYear.MonthYear, Count(qryRegularMeetingsByMonthAndYear.MonthYear) AS CountOfMonthYear
FROM qryRegularMeetingsByMonthAndYear
GROUP BY qryRegularMeetingsByMonthAndYear.MonthYear;

This provides values as follows

MonthYear	CountOfMonthYear
April  2006	4
April  2007	4
April  2008	4
April  2009	5
April  2010	5
August  2006	5   ...  etc.

However, I need to be able to add a parameter to this and pull just a particular time period. As soon as a parameter is added, no records show.

Tom

 

[lespaul]

so you have a table Attendance and you want to count the number of meetings in each month:

SELECT Format([MeetingDate],"mmmm  yyyy") AS MonthYear, Count(tblAttendance.MeetingTypeID) As NumberOfMeetings 
FROM tblAttendance
WHERE tblAttendance.MeetingTypeID=1 And MeetingDate Between #2010/01/01# AND #2010/12/31#
GROUP BY Format([MeetingDate],"mmmm  yyyy")

Now I'm guessing you have a table that tracks the users that attended each meeting and you want to see if that person attended all the meetings? What is the structure of that table?

Leslie

 

[THWatson]

Thanks to everyone for their assistance.

Using Duane Hookom's solution, I have things working. Here are the query SQLs

First query: provides the meetings per month

SELECT tblAttendance.MeetingDate, tblAttendance.MeetingTypeID, DateSerial(Year([MeetingDate]),Month([MeetingDate]),1) AS MonthYear
FROM tblAttendance
GROUP BY tblAttendance.MeetingDate, tblAttendance.MeetingTypeID, DateSerial(Year([MeetingDate]),Month([MeetingDate]),1)
HAVING (((tblAttendance.MeetingTypeID)=1));

Second query: provides the Count of meetings per month

SELECT qryRegularMeetingsByMonthAndYear.MonthYear, Count(qryRegularMeetingsByMonthAndYear.MonthYear) AS Required
FROM qryRegularMeetingsByMonthAndYear
GROUP BY qryRegularMeetingsByMonthAndYear.MonthYear;

Leslie
Thanks for your interest in this. You are correct in your guess...as far as it goes...but it goes further. There is a table that holds the Attendance at Regular meetings. BUT calculating Perfect Attendance takes into consideration the fact that a member could have missed a Regular meeting but could do a Makeup through participation in various activities. There is a complicated set of rules that I don't want to go into here (they are posted several months earlier on this site).

Prior to recently, the club met only on Thursdays so I had a table that held the # of Thursdays, excluding Christmas and New Years, in each month and year, so could match the Member's attendance against that table to see whether or not the Perfect requirements were met. BUT now they done gone and changed things! Now they want to hold the "occasional" Regular meeting on a different day of the week, and also want to be able to cancel meetings at the prerogative of the president of the club. SO I can no longer use my carefully constructed table; rather I have to go by a query that reads the actual # of Regular meetings held in a month. This has meant some modifications, and the two queries above are part of that.

Tom