Maturity Date Calculation

[Janna]

I want a query to calculate the total dollar amounts of investments to days
until maturity grouped into
30 Days or Less
31 - 60 Days
61 - 90 Days
91 - 120 Days
121 Days or More

I currently have the query looking at the system date to calcuate the
results, which works, for example

Within30: IIf([MatureDate] Between DateAdd("d",1,Date()) And
DateAdd("d",30,Date()),[InvAmt],0)

However, the user now wants to be able to enter a date themselves, rather
than using the system date to calculate the grouping of days until maturity.
I thought I could use an unbound form [frmDateCalc] with a text field into
which they enter the desired date [txtDateCalc]

How can I edit the above expression in my query to use the value in
txtDateCalc?

Any suggestions appreciated!

 

[Smartin]

I want a query to calculate the total dollar amounts of investments to days
until maturity grouped into
30 Days or Less
31 - 60 Days
61 - 90 Days
91 - 120 Days
121 Days or More

I currently have the query looking at the system date to calcuate the
results, which works, for example

Within30: IIf([MatureDate] Between DateAdd("d",1,Date()) And
DateAdd("d",30,Date()),[InvAmt],0)

However, the user now wants to be able to enter a date themselves, rather
than using the system date to calculate the grouping of days until maturity.
I thought I could use an unbound form [frmDateCalc] with a text field into
which they enter the desired date [txtDateCalc]

How can I edit the above expression in my query to use the value in
txtDateCalc?

Any suggestions appreciated!

Hi Janna,

Have you tried

Within30: IIf([MatureDate] Between
DateAdd("d",1,forms!formDateCalc!txtDateCalc) And
DateAdd("d",30,forms!formDateCalc!txtDateCalc),[InvAmt],0)


HTH