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

Rabu, 27 Juli 2011

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