calculate days using values from the form in query

G

GEORGIA

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!
 
O

Ofer

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

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!
Click to expand...

Use

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

as the Control Source for a textbox on the Form.

John W. Vinson[MVP]
 
G

GEORGIA

Thank you so much!! works perfectly!!

Ofer said:
Try this
Select invoicedate , DateDiff("d",qryinvoicedate.invoicedate ,
cvdate(forms!frmdate!txtdate)) as MyDateDiff From qryinvoicedate
Click to expand...
 
G

GEORGIA

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
 
G

GEORGIA

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];
 
O

Ofer

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


--
I hope that helped
Good luck


GEORGIA said:
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];




Ofer said:
Any time, I'm glad I could help
Click to expand...
Click to expand...
 
Top