Re: [AccessDevelopers] Re: row number for query result + Limit

[A.D.Tejpal]

Rajesh,       It will be desirable not to have the calculated field for accounting period in the table itself. It can be generated through a query, directly from the values held by FirstDate & LastDate fields.       Sample query is given below. AcPeriod represents accounting period. T_Accounts is the assumed name of table. Best wishes, A.D.Tejpal   Sample query for getting accounting period (Based upon Financial Year - April to March) ================================== SELECT T_Accounts.*, Year([FirstDate])-IIf(Month([FirstDate])<=3,1,0) & "-" & Right(Year([LastDate])+IIf(Month([LastDate])>3,1,0),2) AS AcPeriod  FROM T_Accounts; ==================================   ----- Original Message ----- From: RAJESH DESAI To: AccessDevelopers@yahoogroups.com ; [EMAIL PROTECTED] Sent: Wednesday, September 28, 2005 10:42 Subject: Re: [AccessDevelopers] Re: row number for query result + Limit Hi   I am new to Access and databases. I have a table in which there are three columns FirstDate, LastDate and AccountingPriod. The AccountingPriod field is depenent on FistDate and LastDate values. The following table will show the dependancy. (Ac period is Apr to Mar)   FirstDate           LastDate         Ac Priod 01/04/2004        31/03/2005      2004-05 01/01/2001        31/03/2001      2000-01 01/12/2000        28/02/2001      2000-01   I know how to calculate the Ac Period from given two dates through a formula. I want to know whether it is possible to set the data entry of the ACYear filed on formula something on the lines of autonumbers. I will enter FirstDate and LastDate in my table. The movement I select the ACYear field in Table it should give me the result of my formula in it instead of me typing the acyear for every entry. Awaiting your reply.   Regards,   Rajesh Desai "A.D.Tejpal" <[EMAIL PROTECTED]> wrote:     You are most welcome Jeanie! A.D.Tejpal   ----- Original Message ----- From: Jeanie S To: AccessDevelopers@yahoogroups.com Sent: Tuesday, September 27, 2005 07:18 Subject: [AccessDevelopers] Re: row number for query result + Limit A.D. Your solution was EXACTLY what I needed.  THANK YOU !!!! I tried it immediately, and IT WORKS! Gratefully,  -- Jeanie --- In AccessDevelopers@yahoogroups.com, "A.D.Tejpal" <[EMAIL PROTECTED]...> wrote: > Jeanie, > >     Following sub-routine in Detail_Format event of the report should be able to get you the desired results. > >     TxtGrpCount is the name of text box serving as counter for the group in question (i.e. _expression_ as =1 and running sum property as "over group"). > > Best wishes, > A.D.Tejpal > > ==================================== > Private Sub Detail_Format(Cancel As Integer, _ >                                         FormatCount As Integer) >     If TxtGrpCount > 4 Then >         Detail.Visible = False >     Else >         Detail.Visible = True >     End If > End Sub > ==================================== > >   ----- Original Message ----- >   From: Jeanie S >   To: AccessDevelopers@yahoogroups.com >   Sent: Monday, September 26, 2005 17:50 >   Subject: [AccessDevelopers] Re: row number for query result + Limit > > >   Thanks, Luis.  The only problem with that is that I need the top 4 in >   EACH class and the class is only separated from within the Report.  >   The qryResults finds all of the boats in the event that have >   finished, then the Report separates by Division and then Class within >   the Division. >       Event >       .. Division 1 >          ... Class A >          ... Class B >       .. Division 2 >          ... Class A >          ... Class B >          ... Class C >          ... Class D >   I need the top 4 in each Class, within the Divisions.  Selecting the >   Top 4, ascending from within the query gives me 4 records only.  In >   this case, it was the first place in Div 1, Class A; and the first >   place in Div 2 Classes A, C & D. > > & nbsp; There must be something in the driver code for the report that I can >   adjust, but I can't find anywhere to do that. > >   Anyone? > >   -- Jeanie Please zip all files prior to uploading to Files section. SPONSORED LINKS Microsoft access developer Microsoft access help Microsoft access database Microsoft access training Microsoft access training course Microsoft access programming YAHOO! GROUPS LINKS  Visit your group "AccessDevelopers" on the web.    To unsubscribe from this group, send an email to:  [EMAIL PROTECTED]    Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.