calculate days using values from the form in query

[Guest]

Hi,
I have a unbound text box in a form where user can enter date. I have a
query that lists invoice # and invoice Date. Is there a way to calculate the
days using invoice date and the value of the date in the form? I have tried
using :

(qryinvoicedate.invoicedate - forms!frmdate!txtdate) but it is not working

Purpose of this is that sometimes we want to know how many days the invoice
is past due at that certain date.

Thank you!

 

[Guest]

Try this
Select invoicedate , DateDiff("d",qryinvoicedate.invoicedate ,
cvdate(forms!frmdate!txtdate)) as MyDateDiff From qryinvoicedate

 

[John Vinson]

Hi,
I have a unbound text box in a form where user can enter date. I have a
query that lists invoice # and invoice Date. Is there a way to calculate the
days using invoice date and the value of the date in the form? I have tried
using :

(qryinvoicedate.invoicedate - forms!frmdate!txtdate) but it is not working

Purpose of this is that sometimes we want to know how many days the invoice
is past due at that certain date.

Thank you!

Use

= DateDiff("d", [InvoiceDate], [txtdate])

as the Control Source for a textbox on the Form.

John W. Vinson[MVP]

 

[Guest]

Thank you so much!! works perfectly!!

Try this
Select invoicedate , DateDiff("d",qryinvoicedate.invoicedate ,
cvdate(forms!frmdate!txtdate)) as MyDateDiff From qryinvoicedate

 

[Guest]

Any time, I'm glad I could help

 

[Guest]

well, I came cross another problem. the Query is working great but when i
try to build a crosstab query off from that query, it is saying that jet
database engine does not recognize [forms]![frmdate]![txtdate] as a valid
field name or expression. How do I go about fixing this?

Thank you so much for your assistance

 

[Guest]

here is the sql for the crosstab query
TRANSFORM CCur(nz(Sum([Balance]),0)) AS Amount
SELECT qryAging.[CSC CODE], CCur(nz(Sum([Balance]),0)) AS Total
FROM tblCSC_CODE RIGHT JOIN qryAging ON tblCSC_CODE.CSC_CODE = qryAging.[CSC
CODE]
GROUP BY tblCSC_CODE.ID, qryAging.[CSC CODE]
ORDER BY tblCSC_CODE.ID
PIVOT qryAging.[Aging Bucket];

 

[Guest]

In crosstab query you must declare the parameters
Select Query->Parameters and enter:
forms!frmdate!txtdate


--
I hope that helped
Good luck

here is the sql for the crosstab query
TRANSFORM CCur(nz(Sum([Balance]),0)) AS Amount
SELECT qryAging.[CSC CODE], CCur(nz(Sum([Balance]),0)) AS Total
FROM tblCSC_CODE RIGHT JOIN qryAging ON tblCSC_CODE.CSC_CODE = qryAging.[CSC
CODE]
GROUP BY tblCSC_CODE.ID, qryAging.[CSC CODE]
ORDER BY tblCSC_CODE.ID
PIVOT qryAging.[Aging Bucket];

Any time, I'm glad I could help