Dynamic range of report

F

Frank Situmorang

Hello,

To know outstanding amounts of supplier Invoice for Year End, I have a query
and attached to the report. To know Year End outstandding of 2007 I have 1
report and 1 query. For Year End 2008 I modifyied the Query and attached ot
YE 2008 REport.

How can we make it dynamic, this is the WHERE of the query that I must
change the year every year end, if we want tot know the outstanding amounts
of Supplier's invoices.

WHERE ((([Supplier Invoices Query].InvoiceDate)<#1/1/2009#) AND (([Supplier
Invoices Query].DateInputbyPurch)<#1/1/2009#) AND (([Supplier Invoices
Query].InvBalance)>0)) OR ((([Supplier Invoices
Query].InvoiceDate)<#1/1/2009#) AND (([Supplier Invoices
Query].InvBalance)=0) AND (([Supplier Invoices Query].PayDate)>#12/31/2008#));

Please help.
..
 
J

John Spencer (MVP)

One way would be to use the DateSerial and Date() function to calculate the
date based on the current system date.

DateSerial(Year(Date()),1,1) = Jan 1 of current year
DateSerial(Year(Date())-1,12,31) = Dec 31 of prior year

And your where clause would probably end up looking something like
WHERE ((([Supplier Invoices Query].InvoiceDate)<DateSerial(Year(Date()),1,1))
AND (([Supplier Invoices Query].DateInputbyPurch)< DateSerial(Year(Date()),1,1))
AND (([Supplier Invoices Query].InvBalance)>0))
OR ((([Supplier Invoices Query].InvoiceDate)< DateSerial(Year(Date()),1,1))
AND (([Supplier Invoices Query].InvBalance)=0)
AND (([Supplier Invoices Query].PayDate)>=DateSerial(Year(Date())-1,12,31)));

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
F

Frank Situmorang

John:

I still do not understand. How can we make it for the report Supplier
Balance for Year 2007, then 2008...and so forth?.

How can we make it just to have a prompt asking us which Year End?, then we
just type: 2008...2009 etc.

Thanks for your help.

--
H. Frank Situmorang


John Spencer (MVP) said:
One way would be to use the DateSerial and Date() function to calculate the
date based on the current system date.

DateSerial(Year(Date()),1,1) = Jan 1 of current year
DateSerial(Year(Date())-1,12,31) = Dec 31 of prior year

And your where clause would probably end up looking something like
WHERE ((([Supplier Invoices Query].InvoiceDate)<DateSerial(Year(Date()),1,1))
AND (([Supplier Invoices Query].DateInputbyPurch)< DateSerial(Year(Date()),1,1))
AND (([Supplier Invoices Query].InvBalance)>0))
OR ((([Supplier Invoices Query].InvoiceDate)< DateSerial(Year(Date()),1,1))
AND (([Supplier Invoices Query].InvBalance)=0)
AND (([Supplier Invoices Query].PayDate)>=DateSerial(Year(Date())-1,12,31)));

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Frank said:
Hello,

To know outstanding amounts of supplier Invoice for Year End, I have a query
and attached to the report. To know Year End outstandding of 2007 I have 1
report and 1 query. For Year End 2008 I modifyied the Query and attached ot
YE 2008 REport.

How can we make it dynamic, this is the WHERE of the query that I must
change the year every year end, if we want tot know the outstanding amounts
of Supplier's invoices.

WHERE ((([Supplier Invoices Query].InvoiceDate)<#1/1/2009#) AND (([Supplier
Invoices Query].DateInputbyPurch)<#1/1/2009#) AND (([Supplier Invoices
Query].InvBalance)>0)) OR ((([Supplier Invoices
Query].InvoiceDate)<#1/1/2009#) AND (([Supplier Invoices
Query].InvBalance)=0) AND (([Supplier Invoices Query].PayDate)>#12/31/2008#));

Please help.
.
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

If you want to be prompted for the year then use the date serial command with
a prompt

DateSerial([Enter Year Number],1,1)
DateSerial([Enter Year Number]-1,12,31)

WHERE ((([Supplier Invoices Query].InvoiceDate) <
DateSerial([Enter Year Number],1,1))
AND (([Supplier Invoices Query].DateInputbyPurch) <
DateSerial([Enter Year Number],1,1))
AND (([Supplier Invoices Query].InvBalance)>0))
OR ((([Supplier Invoices Query].InvoiceDate) <
DateSerial([Enter Year Number],1,1))
AND (([Supplier Invoices Query].InvBalance)=0)
AND (([Supplier Invoices Query].PayDate) >=
DateSerial([Enter Year Number]-1,12,31)))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
F

Frank Situmorang

John:

Thank you very much John, it works perfectly, the only thing I need to make
is that the number of Year needs to be shown on the report header. I
appreciate if could help me make it.

Thanks in advance
--
H. Frank Situmorang


John Spencer (MVP) said:
If you want to be prompted for the year then use the date serial command with
a prompt

DateSerial([Enter Year Number],1,1)
DateSerial([Enter Year Number]-1,12,31)

WHERE ((([Supplier Invoices Query].InvoiceDate) <
DateSerial([Enter Year Number],1,1))
AND (([Supplier Invoices Query].DateInputbyPurch) <
DateSerial([Enter Year Number],1,1))
AND (([Supplier Invoices Query].InvBalance)>0))
OR ((([Supplier Invoices Query].InvoiceDate) <
DateSerial([Enter Year Number],1,1))
AND (([Supplier Invoices Query].InvBalance)=0)
AND (([Supplier Invoices Query].PayDate) >=
DateSerial([Enter Year Number]-1,12,31)))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Frank said:
John:

I still do not understand. How can we make it for the report Supplier
Balance for Year 2007, then 2008...and so forth?.

How can we make it just to have a prompt asking us which Year End?, then we
just type: 2008...2009 etc.

Thanks for your help.
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

In the header of the report add a control and set its source to
[Enter Year Number]

IF you want to include text along with the year number then set the source to
= "Data for " & [Enter Year Number]

[Enter Year Number] should be exactly the same as the parameter prompt you are
using in the query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
F

Frank Situmorang

Thanks very much John, it works perfectly, by your goodness in the advanced
coutries we are benefited.

With many thanks,

--
H. Frank Situmorang


John Spencer (MVP) said:
In the header of the report add a control and set its source to
[Enter Year Number]

IF you want to include text along with the year number then set the source to
= "Data for " & [Enter Year Number]

[Enter Year Number] should be exactly the same as the parameter prompt you are
using in the query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Frank said:
John:

Thank you very much John, it works perfectly, the only thing I need to make
is that the number of Year needs to be shown on the report header. I
appreciate if could help me make it.

Thanks in advance
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Debtors Ledger 3
To attach Query to our report 1
Two Report Issues: Please Help! 1
Calculations within a report 1
Example of a crosstab report with dynamic column headings 1
Dynamic Crosstab Report help 0
Sum field in report footer not working right 6
Combining Data for a Quarterly Report 1

Top