elapsed days in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field in query entitled "Invoice Date". I want an additional field
in my query - Aging which represents Invoice Date minus Current Date.

It keeps giving a date error. What would be the correct format?
Thanks
d
 
I'm assuming that a) the data type of the InvoiceDate field is date/time,
and that b) it is a required field - if it isn't you'll need to decide how
you want to handle records with no InvoiceDate ...

SELECT tblTest.InvoiceDate, DateDiff("d",[InvoiceDate],Date()) AS Aging
FROM tblTest;
 
I have a field in query entitled "Invoice Date". I want an additional field
in my query - Aging which represents Invoice Date minus Current Date.

It keeps giving a date error. What would be the correct format?
Thanks
d
Click to expand...

To calculate a duration in days, don't just subtract - use the
DateDiff function. In a vacant Field cell type

Aging: DateDiff("d", [Invoice Date], Date())

to get the number of days between the field and today's date. If you
have a field in your table named [Current Date] use it in place of
Date().

John W. Vinson[MVP]
 
Hi Brendan
tHANKS here is further info

a.
Invoice date is mm/dd/yyyy
b. it Is a required field - it is input on all records

My current expression is:
=DATEDIFF,("D",[iNVOICE DATE],DATE())
IT KEEPS GIVING ME THE MESSAGE THAT "DATE IS NOT DEFINED."

Brendan Reynolds said:
I'm assuming that a) the data type of the InvoiceDate field is date/time,
and that b) it is a required field - if it isn't you'll need to decide how
you want to handle records with no InvoiceDate ...

SELECT tblTest.InvoiceDate, DateDiff("d",[InvoiceDate],Date()) AS Aging
FROM tblTest;

--
Brendan Reynolds

Diana said:
I have a field in query entitled "Invoice Date". I want an additional
field
in my query - Aging which represents Invoice Date minus Current Date.

It keeps giving a date error. What would be the correct format?
Thanks
d
Click to expand...
Click to expand...
 
Hi JOhn ,
I cannot get a column with Date() = says it is not defined
I tried to use the same formula as you stated but it says again date is not
defined.

John Vinson said:
I have a field in query entitled "Invoice Date". I want an additional field
in my query - Aging which represents Invoice Date minus Current Date.

It keeps giving a date error. What would be the correct format?
Thanks
d
Click to expand...

To calculate a duration in days, don't just subtract - use the
DateDiff function. In a vacant Field cell type

Aging: DateDiff("d", [Invoice Date], Date())

to get the number of days between the field and today's date. If you
have a field in your table named [Current Date] use it in place of
Date().

John W. Vinson[MVP]
Click to expand...
 
Hi JOhn ,
I cannot get a column with Date() = says it is not defined
I tried to use the same formula as you stated but it says again date is not
defined.
Click to expand...

Two possibilities:

1. You may have a Field or a Control named Date, and Access is getting
confused; if so, rename this object.

2. You may be facing the very common References bug. Open any
module in design view, or open the VBA editor by typing
Ctrl-G. Select Tools... References from the menu. One of the
..DLL files required by Access will probably be marked
MISSING. Uncheck it, recheck it, close and open Access.

If none are MISSING, check any reference; close and open
Access; then uncheck it again. This will force Access to
relink the libraries.

John W. Vinson[MVP]
 
See John Vinson's posts elsewhere in this thread - as John says, this may be
caused by a field or control named 'Date' or by a missing reference.

--
Brendan Reynolds

Diana said:
Hi Brendan
tHANKS here is further info

a.
Invoice date is mm/dd/yyyy
b. it Is a required field - it is input on all records

My current expression is:
=DATEDIFF,("D",[iNVOICE DATE],DATE())
IT KEEPS GIVING ME THE MESSAGE THAT "DATE IS NOT DEFINED."

Brendan Reynolds said:
I'm assuming that a) the data type of the InvoiceDate field is date/time,
and that b) it is a required field - if it isn't you'll need to decide
how
you want to handle records with no InvoiceDate ...

SELECT tblTest.InvoiceDate, DateDiff("d",[InvoiceDate],Date()) AS Aging
FROM tblTest;

--
Brendan Reynolds

Diana said:
I have a field in query entitled "Invoice Date". I want an additional
field
in my query - Aging which represents Invoice Date minus Current Date.

It keeps giving a date error. What would be the correct format?
Thanks
d
Click to expand...
Click to expand...
Click to expand...
 
Back
Top