Solved

Urgent Date Question

Posted on 2001-06-23
2
179 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

679 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