ACCESS FEED: RE: [MS_AccessPros] Ageing Query

Jumat, 08 Maret 2013

RE: [MS_AccessPros] Ageing Query

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@yahoo.com
>
> 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
>
>
>