Solved

Urgent Date Question

Posted on 2001-06-23
2
176 Views
Last Modified: 2008-02-01
Hi,
I need to write the following query in Access:

I have a table called Loan,
I need to total the number of installments owing for a particular month or year where the balance is greater than zero, my problem being I don't know how to use the date functionality in Access the query needs to work in both 97 and 2000 but more importantly in 97.
I have the query I wrote in Oracle just quickly to get the results for June so that I could compare my Access results to ensure when I get the query in Access going my results are correct:

select sum(installment) Total from loan
where nextpaymntdate > last_day ('01-MAY-01')
and nextpaymntdate <= last_day('01-JUN-01')
and balance > 0;

I would like to get the user to enter the month or year they need the data from and the query to bring up just that month or year the user has chosen.  The date in Access format is e.g. 1-6-01.

If you can help a busy person it would be most appreciated
0
Comment
Question by:stressball
2 Comments
 

Accepted Solution

by:
Mikeh earned 50 total points
ID: 6222618
this asks the user for two parameters startdate and endate and gives the total for installments

SELECT Sum(loan.Installments) AS SumOfInstallments
FROM loan
WHERE ((loan.NextPaymentDate>[startdate] And loan.NextPaymentDate<[enddate]) AND (loan.Balance>0));
0
 

Author Comment

by:stressball
ID: 6222991
Thank you, thats the conclusion I came to but I am not getting any results returned, and I know I should as my Oracle query returns the correct results.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access Runtime 2010 Error 17 32
addressing a subform frim a subform inside it 8 22
Format a Field AFTER UPDATE 5 29
tabctrl with page click event 9 7
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now