ACCESS FEED: [MS_AccessPros] Re: Ageing Query

Sabtu, 09 Maret 2013

[MS_AccessPros] Re: Ageing Query

Thanks Duane,

let me try fixing this and shall be back to you.
Thanks

Shekar

--- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@...> wrote:
>
> Shekar
> Simply create a table named [tblCurrentDate] with a date field named [Current]. Add a single record to the table with either today's date or whatever date you want. Then add this table to your query without joining it to any other table. Add [Current] to the query grid to replace your existing Current column.
>
> You're done other than changing your complex calculations to.
>
> Days 0-30: Sum(Abs([Current]-[Date] Between 0 AND 30) * [Balance])
> Days 31-60: Sum(Abs([Current]-[Date] Between 31 AND 60) * [Balance])
>
> Duane Hookom MVP
> MS Access
>
> ----------------------------------------
> > From: tchshekar@...
> >
> > Hi John,
> >
> > This is what is mentioned in the query:=
> >
> > Current: (Date())[This Feild is not there in table, it is query addition only]
> > Diff: Sum([Current]-[Date])
> > Days 0-30: IIf([Diff]<=30,Sum([Balance]),0)
> > Days 31-60: IIf(([Diff]>=31 And [diff]<=60),Sum([Balance]),0)
> >
> >
> > I need that Current :(Date()) to be open : Some thing like Parameter, on entering through Parameter the calculation should take place and query must run.
> >
> > I Could'nt get through your answer, what you mean is I can used (EnterDate)
> > i.e.Current: (Date()) with Current: ([Enterdate]) and mention in Parameter to execute.
> >
> > PLs elaborate.
> >
> > Thanks,
> >
> > Shekar
> >
> >
> > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> > >
> > > Shekar-
> > >
> > >
> > >
> > > What is the SQL of your query? Are you using this query in a form or
> > > report?
> > >
> > >
> > >
> > > If you current query contains a reference to Date(), you can replace that
> > > with a parameter. Instead of Date(), enter something like:
> > >
> > >
> > >
> > > [Enter date:]
> > >
> > >
> > >
> > > Because Enter date: isn't likely to be a field in your tables, Access will
> > > prompt the user for the value when the query runs.
> > >
> > >
> > >
> > > If you have a form open where the user can specify or select a date value,
> > > you can create a parameter that looks like:
> > >
> > >
> > >
> > > [Forms]![MyForm]![txtDateChosen]
> > >
> > >
> > >
> > > .. where "MyForm" is the name of your form and "txtDateChosen" is the name
> > > of the control containing the date value.
> > >
> > >
> > >
> > > John Viescas, Author
> > >
> > > Microsoft Access 2010 Inside Out
> > >
> > > Microsoft Access 2007 Inside Out
> > >
> > > Microsoft Access 2003 Inside Out
> > >
> > > Building Microsoft Access Applications
> > >
> > > SQL Queries for Mere Mortals
> > >
> > > http://www.viescas.com/
> > >
> > > (Paris, France)
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of tchshekar
> > > Sent: Thursday, March 07, 2013 6:26 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] Ageing Query
> > >
> > >
> > >
> > >
> > >
> > > Hi
> > >
> > > I am looking for ageing query say 0-30, 31-60, and so on, but the problem is
> > > The Now()date / Today() should be replaced with a date which should be at
> > > our discretion, and as required.
> > >
> > > i.e we should be able to generate report on any Past date.
> > >
> > > Thanks
> > >
> > > Shekar
> > >
> > >
> > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
>