ACCESS FEED: RE: [MS_AccessPros] Code does not find dates as expected.

Arrrgggh.

SELECT qTRF.ClubName, Sum(qTRF.Amount) AS SumOfAmount, qTRF.Month
FROM qTRF
WHERE IIf(Month(Date()) >= 7,
qTRF.Month Between DateSerial(Year(Date()),7,1) And
DateSerial(Year(Date())+1,6,30),
qTRF.Month Between DateSerial(Year(Date())-1, 7, 1) And
DateSerial(Year(Date()), 6, 30))
GROUP BY qTRF.ClubName, qTRF.Month

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
Sent: Thursday, July 28, 2011 1:16 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Code does not find dates as expected.

John,

At 28/07/2011 04:48 PM, you wrote:

>So, in May of this year, the "current" year was July 1, 2010 - June
>30, 2011 --
>correct?

That is correct

>Looks like I missed a close paren:
>
>SELECT qTRF.ClubName, Sum(qTRF.Amount) AS SumOfAmount, qTRF.Month
>FROM qTRF
>WHERE IIf(Month(Date() >= 7,
>qTRF.Month Between DateSerial(Year(Date()),7,1) And
>DateSerial(Year(Date())+1,6,30),
>qTRF.Month Between DateSerial(Year(Date())-1, 7, 1) And
>DateSerial(Year(Date()), 6, 30))
>GROUP BY qTRF.ClubName, qTRF.Month

That SQL gives me this error message:

http://www.rotary9790.org.au/test/test.asp

and the GROUP is selected when I close the error message.

Robin

>-----Original Message-----
>From: MS_Access_Professionals@yahoogroups.com
>[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
>Sent: Thursday, July 28, 2011 8:22 AM
>To: MS_Access_Professionals@yahoogroups.com
>Subject: RE: [MS_AccessPros] Code does not find dates as expected.
>
>Thanks John,
>
>That SQL reports syntax error, missing operator and settles with
>'GROUP' selected.
>
>My reference is to "This Rotary Year" means 1st July in the current
>year when the date is before 1st January in the following year, to
>the 30th June in the next year.
>
>Robin
>
>
>At 28/07/2011 04:04 PM, you wrote:
> >Robin-
> >
> >Ah, "this" year. The expression I gave you works for "this" year
> because it's
> >after July 1 right now. To make it universal, you need:
> >
> >SELECT qTRF.ClubName, Sum(qTRF.Amount) AS SumOfAmount, qTRF.Month
> >FROM qTRF
> >WHERE IIf(Month(Date() >= 7,
> >qTRF.Month Between DateSerial(Year(Date()),7,1) And
> >DateSerial(Year(Date())+1,6,30),
> >qTRF.Month Between DateSerial(Year(Date())-1, 7, 1) And
> >DateSerial(Year(Date()),
> >6, 30)
> >GROUP BY qTRF.ClubName, qTRF.Month
> >
> >I assume the "Month" field is a full date/time with a date in it, not just a
> >month number. Don't you want to GROUP BY Month(qTRF.Month) ??
> >
> >John Viescas, author
> >Microsoft Office Access 2010 Inside Out
> >Microsoft Office Access 2007 Inside Out
> >Building Microsoft Access Applications
> >Microsoft Office Access 2003 Inside Out
> >SQL Queries for Mere Mortals
> >http://www.viescas.com/
> >(Paris, France)
> >
> >
> >
> >
> >-----Original Message-----
> >From: MS_Access_Professionals@yahoogroups.com
> >[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
> >Sent: Thursday, July 28, 2011 3:23 AM
> >To: MS_Access_Professionals@yahoogroups.com
> >Subject: RE: [MS_AccessPros] Code does not find dates as expected.
> >
> >John,
> >
> >The Hong Kong working day has just started from the time I post
> this message.
> >
> >Is there code to say "Any date after 30th June ""This Rotary Uear"""?
> >
> >Cheers,
> >
> >Robin
> >
> >~~~~~~~~~~~~~~~~~~~~~~~~~~~ Original message ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >
> >Not a clue John. It closes the application! It really is upset!
> >
> >I have asked the help desk at the company. They always answer on the
> >same working Hong Kong day.
> >
> >Robin
> >
> >At 27/07/2011 09:26 PM, you wrote:
> > >Robin-
> > >
> > >Any clue what it doesn't like? I'm not using any functions that
> you weren't
> > >using previously.
> > >
> > >John Viescas, author
> > >Microsoft Office Access 2010 Inside Out
> > >Microsoft Office Access 2007 Inside Out
> > >Building Microsoft Access Applications
> > >Microsoft Office Access 2003 Inside Out
> > >SQL Queries for Mere Mortals
> > >http://www.viescas.com/
> > >(Paris, France)
> > >
> > >
> > >
> > >-----Original Message-----
> > >From: MS_Access_Professionals@yahoogroups.com
> > >[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> Robin Chapple
> > >Sent: Wednesday, July 27, 2011 1:10 PM
> > >To: MS_Access_Professionals@yahoogroups.com
> > >Subject: RE: [MS_AccessPros] Code does not find dates as expected.
> > >
> > >
> > >That comment was as a result of testing the SQL in Access. The code
> > >generator does not accept this alternative. Is there another way to
> > >get the same result?
> > >
> > >Thanks,
> > >
> > >Robin Chapple
> > >
> > >
> > >At 27/07/2011 07:23 PM, you wrote:
> > >
> > > >Thanks again John,
> > > >
> > > >That is just what the doctor ordered. I still have my version 2
> > > >manuals and I have found the function and will add it to my
> list to study.
> > > >
> > > >Regards,
> > > >
> > > >Robin Chapple
> > > >
> > > >
> > > >At 27/07/2011 05:53 PM, you wrote:
> > > > >Robin-
> > > > >
> > > > >WHERE RecordDate BETWEEN DateSerial(Year(Date()), 7, 1) And
> > > > >DateSerial(Year(Date())+1, 6, 30)
> > > > >
> > > > >John Viescas, author
> > > > >Microsoft Office Access 2010 Inside Out
> > > > >Microsoft Office Access 2007 Inside Out
> > > > >Building Microsoft Access Applications
> > > > >Microsoft Office Access 2003 Inside Out
> > > > >SQL Queries for Mere Mortals
> > > > >http://www.viescas.com/
> > > > >(Paris, France)
> > > > >
> > > > >
> > > > >-----Original Message-----
> > > > >From: MS_Access_Professionals@yahoogroups.com
> > > > >[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > > Robin Chapple
> > > > >Sent: Wednesday, July 27, 2011 9:29 AM
> > > > >To: MS_Access_Professionals@yahoogroups.com
> > > > >Subject: [MS_AccessPros] Code does not find dates as expected.
> > > > >
> > > > >
> > > > >My organisation has a year which starts on July 1st. I have been
> > > > >using this code:
> > > > >
> > > > >IIf(Month(Date())>=7,DateSerial(Year(Date()),7,1),DateSerial(Year(D
> > > > ate())-1,7,1)
> > > > >)
> > > > >
> > > > >to decide if an entry is "This Organisational Year". The original
> > > > >need was to identify post holders in the year, and appointments are
> > > > >from 1st July.
> > > > >
> > > > >I have recently applied it to a table where the day part of the date
> > > > >is variable and when the date is not 1st and the month is not July
> > > > >and records are missed.
> > > > >
> > > > >How do I edit the code to include any date between 1st July, this
> > > > >year up to 31st December and from 1st January to 30th June in
> > > the next year?
> > > > >
> > > > >I have no idea!
> > > > >
> > > > >Many thanks,
> > > > >
> > > > >Robin Chapple
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >------------------------------------
> > > > >
> > > > >Yahoo! Groups Links
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >------------------------------------
> > > > >
> > > > >Yahoo! Groups Links
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > > >
> > > >------------------------------------
> > > >
> > > >Yahoo! Groups Links
> > > >
> > > >
> > > >
> > >
> > >
> > >
> > >
> > >------------------------------------
> > >
> > >Yahoo! Groups Links
> > >
> > >
> > >
> > >
> > >
> > >------------------------------------
> > >
> > >Yahoo! Groups Links
> > >
> > >
> > >
> >
> >
> >
> >
> >------------------------------------
> >
> >Yahoo! Groups Links
> >
> >
> >
> >
> >
> >------------------------------------
> >
> >Yahoo! Groups Links
> >
> >
> >
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>

------------------------------------

Yahoo! Groups Links