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
>
>
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (11)

Recent Activity:

• New Members 4
• New Files 2

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___