ACCESS FEED: [MS_AccessPros] Re: Ageing Query

Minggu, 10 Maret 2013

[MS_AccessPros] Re: Ageing Query

Hi Duane

It is working, but we need to change the table date every time as per our requirement before generating the query output.

I am still looking to dig further deep, to avoid the situation
something like this:-
on click the query :- it should prompt "You want to generate the report as on date " and we should be able to enter the date to query to continue to generate the report based on the date entered.

sorry, i think i troubling too much.

Thanks

Shekar

--- In MS_Access_Professionals@yahoogroups.com, "tchshekar" <tchshekar@...> wrote:
>
> 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
> > >
> > >
> > >
> >
>