Grouping by month

  • Thread starter Thread starter tod
  • Start date Start date
T

tod

I have a query that uses a View. Among the fields in the view are:

Date_Arrive
Date_Depart
Num_Party

The first two fields are Date/Time fields. The third is a number
field. My query needs to sum Num_Party and group it by month using
Date_Arrive. So I have a query like this:

Month Arrive: DateValue(Month(Date_Arrive)&"/1/"&Year(Date_Arrive))
Number in Party: Sum(Num_Party)

It looks goofy, but it always worked so I didn't question it. Now I
need to modify the query to use Date_Depart. I thought I could simply
replace Date_Arrive with Date_Depart and be done. However, Date_Depart
does not always have a value. So when I do the replacement I get a
type mismatch error.

How should I have been doing this in the first place? I just want to
sum all Num_Party values when grouped by month/year.

tod
 
Hi Tod,

Why not just use IIF function:

IIF(IsNull(Date_Depart),null,
DateValue(Month(Date_Depart)&"/1/"&Year(Date_Depart)))

Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights

--------------------
| From: (e-mail address removed) (tod)
| Newsgroups: microsoft.public.access.queries
| Subject: Grouping by month
| Date: 24 Mar 2004 12:29:19 -0800
| Organization: http://groups.google.com
| Lines: 23
| Message-ID: <[email protected]>
| NNTP-Posting-Host: 204.149.20.50
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1080160165 22883 127.0.0.1 (24 Mar 2004
20:29:25 GMT)
| X-Complaints-To: (e-mail address removed)
| NNTP-Posting-Date: Wed, 24 Mar 2004 20:29:25 +0000 (UTC)
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!news.glorb.com!postnews2.google.com!not-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:195030
| X-Tomcat-NG: microsoft.public.access.queries
|
| I have a query that uses a View. Among the fields in the view are:
|
| Date_Arrive
| Date_Depart
| Num_Party
|
| The first two fields are Date/Time fields. The third is a number
| field. My query needs to sum Num_Party and group it by month using
| Date_Arrive. So I have a query like this:
|
| Month Arrive: DateValue(Month(Date_Arrive)&"/1/"&Year(Date_Arrive))
| Number in Party: Sum(Num_Party)
|
| It looks goofy, but it always worked so I didn't question it. Now I
| need to modify the query to use Date_Depart. I thought I could simply
| replace Date_Arrive with Date_Depart and be done. However, Date_Depart
| does not always have a value. So when I do the replacement I get a
| type mismatch error.
|
| How should I have been doing this in the first place? I just want to
| sum all Num_Party values when grouped by month/year.
|
| tod
|
 
Back
Top