Aha! DateSerial takes three numeric arguments - year, month and day. If a
number is outside the "acceptable" range then it is manipulated
arithmetically. For example, month=13 gives January in the following year
and month=0 gives December in the previous year.
Likewise, day=0 gives the last day of the previous month, so:
DateSerial( Year( Date() ), Month( Date() ), 0 )
gives the last day of the month prior to the current one.
You could also use:
DateSerial( Year( Date() ), Month( Date() ) - 1, 1 )
which would give you the first day last month.
Since you are then using Format( d, "mmmm yyyy" ) it doesn't really matter
about the day.
Cheers,
Graham
> -----Original Message-----
> From: Robin Chapple [mailto:robinski@mymail.net.au]
> Sent: Thursday, 1 September 2011 15:36
> To: Graham Mandeno
> Subject: RE: [MS_AccessPros] Missing records with 'nil returns'.
>
>
> Graham,
>
> I have now studied your code and I cannot understand how it finds the
> previous month. We are already in September in Australia and my report
> shows results for August as expected, but how?
>
> What is the secret?
>
> Regards,
>
> Robin
>
> At 1/09/2011 11:56 AM, you wrote:
> >Hi Robin
> >
> >How about this:
> >ReportMonth=Format(DateSerial(Year(Date()),Month(Date()),0), "mmmm
> >yyyy")
> >
> >Cheers,
> >Graham
> >
> > > -----Original Message-----
> > > From: Robin Chapple [mailto:robinski@mymail.net.au]
> > > Sent: Thursday, 1 September 2011 13:44
> > > To: Graham Mandeno
> > > Subject: RE: [MS_AccessPros] Missing records with 'nil returns'.
> > >
> > >
> > > Graham,
> > >
> > > One way to work around my ASP problem would be if I had code that
> > > produced "mmm yyyy" for the previous month every month. That would
> > > allow the report to show the July 2011 in August 2011 and August in
> > > September for instance.
> > >
> > > Robin
> > >
> > >
> > > At 1/09/2011 09:42 AM, you wrote:
> > > >Hi Robin
> > > >
> > > >Getting my head around this slowly :-)
> > > >
> > > >So, the filtered qYearStart (or qYearStartTy) has 61 records, but 3
> > > >of them have no ClubID. Why? (That's just an aside)
> > > >
> > > >The problem here is that a LEFT join will only be "frustrated" if
> > > >there are no matching records in the right table. In your case,
> > > >there ARE matching records for the recalcitrant clubs, but not ones
> > > >that fulfill the ReportMonth="july 2011" filter.
> > > >
> > > >What you need to do is filter the query BEFORE you make the join.
> > > >You can do this with a subquery.
> > > >
> > > >Try this:
> > > >
> > > >SELECT
> > > >ys.ClubID, ys.ClubName, ys.Members AS [Year Start], tm.Members,
> > > >tm!Members-ys!Members AS Change, tm.MeetingsHeld,
> > > tm.MonthPerCent,
> > > >tm.ReportMonth, ys.YearStart FROM qYearStart as ys LEFT JOIN
> > > >(select * from qAttThisMonth where ReportMonth="july 2011") as tm
> > > >ON ys.ClubID
> > > =
> > > >tm.ClubID WHERE ys.YearStart=IIf(Month(Date())>=7,
> > > > DateSerial(Year(Date()),7,1),
> > > > DateSerial(Year(Date())-1,7,1)) ORDER BY ys.ClubName;
> > > >
> > > >Cheers,
> > > >Graham
> > > >
> > > > > -----Original Message-----
> > > > > From: Robin Chapple [mailto:robinski@mymail.net.au]
> > > > > Sent: Thursday, 1 September 2011 09:36
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > Subject: RE: [MS_AccessPros] Missing records with 'nil returns'.
> > > > >
> > > > >
> > > > > G'day Graham,
> > > > >
> > > > > Some of this is my ignorance and misuse of terms.
> > > > >
> > > > > At 1/09/2011 07:23 AM, you wrote:
> > > > >
> > > > > >There are two things that are puzzling me:
> > > > > >
> > > > > >In your original post you said: "The query "qYearStart" has 61
> > > > > >entries." If so, why do you need to filter on
> > > > > >qYearStart.YearStart at all? The query will return all 61
> > > > > >records with or without the
> >filter.
> > > > >
> > > > > I should have said 'when filtered. I have now removed that
> > > > > problem by
> > > >using
> > > > > "qYearStartTy" which delivers records for "This Year" without a
> >filter.
> > > > >
> > > > > >Also, does qAttThisMonth include any records which are NOT for
> > > > > >the current year, and can it have multiple records per ClubID?
> > > > >
> > > > > "qAttThisMonth" includes records for many years. There are no
> > > > > multiple records.
> > > > >
> > > > > >Note that there is another unrelated problem with this
expression:
> > > > > >[qAttThisMonth!Members]-[qYearStart!Members] AS Change
> > > > > >
> > > > > >It should be:
> > > > > >[qAttThisMonth]![Members]-[qYearStart]![Members] AS Change
> > > > > >
> > > > > >Or, if you prefer, leave out the square brackets altogether.
> > > > >
> > > > > I will correct that error.
> > > > >
> > > > > Many thanks,
> > > > >
> > > > > Regards,
> > > > >
> > > > > Robin
> > > > >
> > > > >
> > > > >
> > > > > >Best regards,
> > > > > >Graham
> > > > > >
> > > > > >From: MS_Access_Professionals@yahoogroups.com
> > > > > >[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > > Robin
> > > > > >Chapple
> > > > > >Sent: Thursday, 1 September 2011 00:25
> > > > > >To: MS_Access_Professionals@yahoogroups.com
> > > > > >Subject: RE: [MS_AccessPros] Missing records with 'nil returns'.
> > > > > >Graham,
> > > > > >
> > > > > >That delivers 61 records, 3 without ClubID.
> > > > > >
> > > > > >Robin
> > > > > >
> > > > > >At 31/08/2011 08:07 PM, you wrote:
> > > > > > >Robin,
> > > > > > >
> > > > > > >As a matter of interest, how many records does this give you?
> > > > > > >
> > > > > > >SELECT * FROM qYearStart WHERE (
> > > > > > >qYearStart.YearStart=IIf(Month(Date())>=7,
> > > > > > >DateSerial(Year(Date()),7,1),
> > > > > > >DateSerial(Year(Date())-1,7,1)) );
> > > > > > >--
> > > > > > >
> > > > > > >Graham
> > > > > >
> > > > > >
> > > > > >
> > > > > >------------------------------------
> > > > > >
> > > > > >Yahoo! Groups Links
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > >
> > >
>
>
