Using Dates in Queries 1

[stressball]

Hi,
I am very short on time and 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.

 

[DougP]

I have a workaround for you.

Use this Function..

Public Function GetDate()
Dim retval As Variant
retval = InputBox(&quot;Enter the Month to find&quot;, &quot;Enter parameter&quot;, &quot;1&quot
GetDate = DateSerial(Year(Now), retval, Day(Now))
End Function

Then in you query call it like so
=GetDate()
So when you run the query it will ask for a date and return something the query can use.

DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[mymou]

To get the first day of (this and next) month try using in a function:

Dim FirstDate As Date

FirstDate = InputBox(&quot;Enter a date&quot
FirstDate = DateAdd(&quot;d&quot;, 1, DateSerial(Year(FirstDate), Month(FirstDate), 0))
SecondDate = DateAdd(&quot;m&quot;, 1, FirstDate)


Stew

 

[stressball]

Where in my query do I put the function call? I keep getting aggregation errors