Date question

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

Guest

I am trying to figure out how I can produce the following:
I need to show accounts that have (0)zero days left before they need to be
paid.
I have created the the following query:

SELECT Accts_Payable.Bill_Date, Accts_Payable.Bill_Amount,
Accts_Payable.[Date Paid], [Accts_Payable Query].Discount_Amt,
(Accts_Payable.Bill_Amount-[Accts_Payable Query].Discount_Amt) AS [New
Bill_Amt], DateDiff("d",[Accts_Payable Query].Bill_Date-[Date Paid],0) AS
Days, Date() AS [Report Date], DateDiff("d",[Report
Date]-Accts_Payable.Bill_Date,0)+6 AS [Days Left]
FROM Accts_Payable INNER JOIN [Accts_Payable Query] ON
Accts_Payable.Bill_EntryID = [Accts_Payable Query].Bill_EntryID;

I cannot get information to produce days remaining unless I put a Date in
the [Date Paid] field. I do not want to do it this way. I need to be able to
produce the report based on data already in table.
Any help appreciated.
 
Dear Confused:

I reproduce your query, edited for my reading preferences:

SELECT AP.Bill_Date, AP.Bill_Amount,
AP.[Date Paid], Q.Discount_Amt,
(AP.Bill_Amount - Q.Discount_Amt) AS [New Bill_Amt],
DateDiff("d", Q.Bill_Date - [Date Paid], 0) AS Days,
Date() AS [Report Date],
DateDiff("d", [Report Date] - AP.Bill_Date, 0) + 6 AS [Days Left]
FROM Accts_Payable AP
INNER JOIN [Accts_Payable Query] Q
ON AP.Bill_EntryID = Q.Bill_EntryID

What I see is the possibility that you have made a common error. You have a
calculation for [Days Left] (I am guessing that when your question asks
about "days remaining" it is talking about this calculated column.

In the calculation for [Days Left] you have referenced [Report Date], which
itself is a calculated column in this same query. I'm guessing that you
presume that any column you have calculated is instantly available
throughout the query to be used. This is never the case.

If my assumptions are correct, you should replace [Report Date] in the
calculation for [Days Left] with Date(), which is probably what you meant:
In other words, try this:

SELECT AP.Bill_Date, AP.Bill_Amount,
AP.[Date Paid], Q.Discount_Amt,
(AP.Bill_Amount - Q.Discount_Amt) AS [New Bill_Amt],
DateDiff("d", Q.Bill_Date - [Date Paid], 0) AS Days,
Date() AS [Report Date],
DateDiff("d", Date() - AP.Bill_Date, 0) + 6 AS [Days Left]
FROM Accts_Payable AP
INNER JOIN [Accts_Payable Query] Q
ON AP.Bill_EntryID = Q.Bill_EntryID

This is not the only problem. The DateDiff function expects you to place
two dates in the second and third parameter. It appears you have placed a
subtraction of the two dates in the second parameter and given it the value
0 for the other date. That's probably not what you intended, and it's
probably not going to give you reasonable results.

May I suggest perhaps what you wanted is:

SELECT AP.Bill_Date, AP.Bill_Amount,
AP.[Date Paid], Q.Discount_Amt,
(AP.Bill_Amount - Q.Discount_Amt) AS [New Bill_Amt],
DateDiff("d", Q.Bill_Date - [Date Paid], 0) AS Days,
Date() AS [Report Date],
DateDiff("d", Date(), AP.Bill_Date) + 6 AS [Days Left]
FROM Accts_Payable AP
INNER JOIN [Accts_Payable Query] Q
ON AP.Bill_EntryID = Q.Bill_EntryID

If the Bill_Date was 3 days before present, then DateDiff would return -3.
You than add 6. It would seem you may mean that the due date is 6 days
forward from the Bill_Date, so the -3 changes to +3, meaning there are 3
days remaining.

There's some guess work going on here, so check it out carefully before
using this.

Tom Ellison


confusedinc said:
I am trying to figure out how I can produce the following:
I need to show accounts that have (0)zero days left before they need to be
paid.
I have created the the following query:

SELECT Accts_Payable.Bill_Date, Accts_Payable.Bill_Amount,
Accts_Payable.[Date Paid], [Accts_Payable Query].Discount_Amt,
(Accts_Payable.Bill_Amount-[Accts_Payable Query].Discount_Amt) AS [New
Bill_Amt], DateDiff("d",[Accts_Payable Query].Bill_Date-[Date Paid],0) AS
Days, Date() AS [Report Date], DateDiff("d",[Report
Date]-Accts_Payable.Bill_Date,0)+6 AS [Days Left]
FROM Accts_Payable INNER JOIN [Accts_Payable Query] ON
Accts_Payable.Bill_EntryID = [Accts_Payable Query].Bill_EntryID;

I cannot get information to produce days remaining unless I put a Date in
the [Date Paid] field. I do not want to do it this way. I need to be able
to
produce the report based on data already in table.
Any help appreciated.
Click to expand...
 
Thanks for the quick reply Tom. I will try your suggestion and get back to
you. This is new frontier for me.

Tom Ellison said:
Dear Confused:

I reproduce your query, edited for my reading preferences:

SELECT AP.Bill_Date, AP.Bill_Amount,
AP.[Date Paid], Q.Discount_Amt,
(AP.Bill_Amount - Q.Discount_Amt) AS [New Bill_Amt],
DateDiff("d", Q.Bill_Date - [Date Paid], 0) AS Days,
Date() AS [Report Date],
DateDiff("d", [Report Date] - AP.Bill_Date, 0) + 6 AS [Days Left]
FROM Accts_Payable AP
INNER JOIN [Accts_Payable Query] Q
ON AP.Bill_EntryID = Q.Bill_EntryID

What I see is the possibility that you have made a common error. You have a
calculation for [Days Left] (I am guessing that when your question asks
about "days remaining" it is talking about this calculated column.

In the calculation for [Days Left] you have referenced [Report Date], which
itself is a calculated column in this same query. I'm guessing that you
presume that any column you have calculated is instantly available
throughout the query to be used. This is never the case.

If my assumptions are correct, you should replace [Report Date] in the
calculation for [Days Left] with Date(), which is probably what you meant:
In other words, try this:

SELECT AP.Bill_Date, AP.Bill_Amount,
AP.[Date Paid], Q.Discount_Amt,
(AP.Bill_Amount - Q.Discount_Amt) AS [New Bill_Amt],
DateDiff("d", Q.Bill_Date - [Date Paid], 0) AS Days,
Date() AS [Report Date],
DateDiff("d", Date() - AP.Bill_Date, 0) + 6 AS [Days Left]
FROM Accts_Payable AP
INNER JOIN [Accts_Payable Query] Q
ON AP.Bill_EntryID = Q.Bill_EntryID

This is not the only problem. The DateDiff function expects you to place
two dates in the second and third parameter. It appears you have placed a
subtraction of the two dates in the second parameter and given it the value
0 for the other date. That's probably not what you intended, and it's
probably not going to give you reasonable results.

May I suggest perhaps what you wanted is:

SELECT AP.Bill_Date, AP.Bill_Amount,
AP.[Date Paid], Q.Discount_Amt,
(AP.Bill_Amount - Q.Discount_Amt) AS [New Bill_Amt],
DateDiff("d", Q.Bill_Date - [Date Paid], 0) AS Days,
Date() AS [Report Date],
DateDiff("d", Date(), AP.Bill_Date) + 6 AS [Days Left]
FROM Accts_Payable AP
INNER JOIN [Accts_Payable Query] Q
ON AP.Bill_EntryID = Q.Bill_EntryID

If the Bill_Date was 3 days before present, then DateDiff would return -3.
You than add 6. It would seem you may mean that the due date is 6 days
forward from the Bill_Date, so the -3 changes to +3, meaning there are 3
days remaining.

There's some guess work going on here, so check it out carefully before
using this.

Tom Ellison


confusedinc said:
I am trying to figure out how I can produce the following:
I need to show accounts that have (0)zero days left before they need to be
paid.
I have created the the following query:

SELECT Accts_Payable.Bill_Date, Accts_Payable.Bill_Amount,
Accts_Payable.[Date Paid], [Accts_Payable Query].Discount_Amt,
(Accts_Payable.Bill_Amount-[Accts_Payable Query].Discount_Amt) AS [New
Bill_Amt], DateDiff("d",[Accts_Payable Query].Bill_Date-[Date Paid],0) AS
Days, Date() AS [Report Date], DateDiff("d",[Report
Date]-Accts_Payable.Bill_Date,0)+6 AS [Days Left]
FROM Accts_Payable INNER JOIN [Accts_Payable Query] ON
Accts_Payable.Bill_EntryID = [Accts_Payable Query].Bill_EntryID;

I cannot get information to produce days remaining unless I put a Date in
the [Date Paid] field. I do not want to do it this way. I need to be able
to
produce the report based on data already in table.
Any help appreciated.
Click to expand...
Click to expand...
 
Okay Tom,
Here I go,
Using Date() function doesn't produce what I need for the following criteria:
I currently have 20 records with various Bill Dates.
I have 10 that are paid on or before the 10 day discount date (In order to
receive discount)
I then have 10 records that have not been paid yet.
The dates are in range from 1/3/2006 to 3/19/2006.
Is it possible to create a query/report that will show me only the accounts
with zero days remaining to be paid? Based on present and past dates or do I
need to readjust all records to reflect only current and future dates?

Tom Ellison said:
Dear Confused:

I reproduce your query, edited for my reading preferences:

SELECT AP.Bill_Date, AP.Bill_Amount,
AP.[Date Paid], Q.Discount_Amt,
(AP.Bill_Amount - Q.Discount_Amt) AS [New Bill_Amt],
DateDiff("d", Q.Bill_Date - [Date Paid], 0) AS Days,
Date() AS [Report Date],
DateDiff("d", [Report Date] - AP.Bill_Date, 0) + 6 AS [Days Left]
FROM Accts_Payable AP
INNER JOIN [Accts_Payable Query] Q
ON AP.Bill_EntryID = Q.Bill_EntryID

What I see is the possibility that you have made a common error. You have a
calculation for [Days Left] (I am guessing that when your question asks
about "days remaining" it is talking about this calculated column.

In the calculation for [Days Left] you have referenced [Report Date], which
itself is a calculated column in this same query. I'm guessing that you
presume that any column you have calculated is instantly available
throughout the query to be used. This is never the case.

If my assumptions are correct, you should replace [Report Date] in the
calculation for [Days Left] with Date(), which is probably what you meant:
In other words, try this:

SELECT AP.Bill_Date, AP.Bill_Amount,
AP.[Date Paid], Q.Discount_Amt,
(AP.Bill_Amount - Q.Discount_Amt) AS [New Bill_Amt],
DateDiff("d", Q.Bill_Date - [Date Paid], 0) AS Days,
Date() AS [Report Date],
DateDiff("d", Date() - AP.Bill_Date, 0) + 6 AS [Days Left]
FROM Accts_Payable AP
INNER JOIN [Accts_Payable Query] Q
ON AP.Bill_EntryID = Q.Bill_EntryID

This is not the only problem. The DateDiff function expects you to place
two dates in the second and third parameter. It appears you have placed a
subtraction of the two dates in the second parameter and given it the value
0 for the other date. That's probably not what you intended, and it's
probably not going to give you reasonable results.

May I suggest perhaps what you wanted is:

SELECT AP.Bill_Date, AP.Bill_Amount,
AP.[Date Paid], Q.Discount_Amt,
(AP.Bill_Amount - Q.Discount_Amt) AS [New Bill_Amt],
DateDiff("d", Q.Bill_Date - [Date Paid], 0) AS Days,
Date() AS [Report Date],
DateDiff("d", Date(), AP.Bill_Date) + 6 AS [Days Left]
FROM Accts_Payable AP
INNER JOIN [Accts_Payable Query] Q
ON AP.Bill_EntryID = Q.Bill_EntryID

If the Bill_Date was 3 days before present, then DateDiff would return -3.
You than add 6. It would seem you may mean that the due date is 6 days
forward from the Bill_Date, so the -3 changes to +3, meaning there are 3
days remaining.

There's some guess work going on here, so check it out carefully before
using this.

Tom Ellison


confusedinc said:
I am trying to figure out how I can produce the following:
I need to show accounts that have (0)zero days left before they need to be
paid.
I have created the the following query:

SELECT Accts_Payable.Bill_Date, Accts_Payable.Bill_Amount,
Accts_Payable.[Date Paid], [Accts_Payable Query].Discount_Amt,
(Accts_Payable.Bill_Amount-[Accts_Payable Query].Discount_Amt) AS [New
Bill_Amt], DateDiff("d",[Accts_Payable Query].Bill_Date-[Date Paid],0) AS
Days, Date() AS [Report Date], DateDiff("d",[Report
Date]-Accts_Payable.Bill_Date,0)+6 AS [Days Left]
FROM Accts_Payable INNER JOIN [Accts_Payable Query] ON
Accts_Payable.Bill_EntryID = [Accts_Payable Query].Bill_EntryID;

I cannot get information to produce days remaining unless I put a Date in
the [Date Paid] field. I do not want to do it this way. I need to be able
to
produce the report based on data already in table.
Any help appreciated.
Click to expand...
Click to expand...
 
Dear Confused:

Your original query did nothing to try to filter the results. We can now
add that:

SELECT AP.Bill_Date, AP.Bill_Amount,
AP.[Date Paid], Q.Discount_Amt,
(AP.Bill_Amount - Q.Discount_Amt) AS [New Bill_Amt],
DateDiff("d", Q.Bill_Date - [Date Paid], 0) AS Days,
Date() AS [Report Date],
DateDiff("d", Date(), AP.Bill_Date) + 6 AS [Days Left]
FROM Accts_Payable AP
INNER JOIN [Accts_Payable Query] Q
ON AP.Bill_EntryID = Q.Bill_EntryID
WHERE [Date Paid] IS NULL

Depending on what you have in [Date Paid] for those not yet paid, this may
or may not be what you need. Am I right to think that the test for whether
a Bill is paid would be to look at [Date Paid]? What is in [Date Paid] if
it is not paid?

Now about those which, in addition to not having been paid, are already due.
I take it these are ones where the Bill_Date is within 6 days of today, and
which are not already paid (using the above test, or whatever it takes to
make that work:

SELECT AP.Bill_Date, AP.Bill_Amount,
AP.[Date Paid], Q.Discount_Amt,
(AP.Bill_Amount - Q.Discount_Amt) AS [New Bill_Amt],
DateDiff("d", Q.Bill_Date - [Date Paid], 0) AS Days,
Date() AS [Report Date],
DateDiff("d", Date(), AP.Bill_Date) + 6 AS [Days Left]
FROM Accts_Payable AP
INNER JOIN [Accts_Payable Query] Q
ON AP.Bill_EntryID = Q.Bill_EntryID
WHERE [Date Paid] IS NULL
AND DateDiff("d", Date(), AP.Bill_Date) > 6

Test this carefully to see if it is what you need. Be very certain about
the filter on 6 days.

Tom Ellison


confusedinc said:
Okay Tom,
Here I go,
Using Date() function doesn't produce what I need for the following
criteria:
I currently have 20 records with various Bill Dates.
I have 10 that are paid on or before the 10 day discount date (In order to
receive discount)
I then have 10 records that have not been paid yet.
The dates are in range from 1/3/2006 to 3/19/2006.
Is it possible to create a query/report that will show me only the
accounts
with zero days remaining to be paid? Based on present and past dates or do
I
need to readjust all records to reflect only current and future dates?

Tom Ellison said:
Dear Confused:

I reproduce your query, edited for my reading preferences:

SELECT AP.Bill_Date, AP.Bill_Amount,
AP.[Date Paid], Q.Discount_Amt,
(AP.Bill_Amount - Q.Discount_Amt) AS [New Bill_Amt],
DateDiff("d", Q.Bill_Date - [Date Paid], 0) AS Days,
Date() AS [Report Date],
DateDiff("d", [Report Date] - AP.Bill_Date, 0) + 6 AS [Days Left]
FROM Accts_Payable AP
INNER JOIN [Accts_Payable Query] Q
ON AP.Bill_EntryID = Q.Bill_EntryID

What I see is the possibility that you have made a common error. You
have a
calculation for [Days Left] (I am guessing that when your question asks
about "days remaining" it is talking about this calculated column.

In the calculation for [Days Left] you have referenced [Report Date],
which
itself is a calculated column in this same query. I'm guessing that you
presume that any column you have calculated is instantly available
throughout the query to be used. This is never the case.

If my assumptions are correct, you should replace [Report Date] in the
calculation for [Days Left] with Date(), which is probably what you
meant:
In other words, try this:

SELECT AP.Bill_Date, AP.Bill_Amount,
AP.[Date Paid], Q.Discount_Amt,
(AP.Bill_Amount - Q.Discount_Amt) AS [New Bill_Amt],
DateDiff("d", Q.Bill_Date - [Date Paid], 0) AS Days,
Date() AS [Report Date],
DateDiff("d", Date() - AP.Bill_Date, 0) + 6 AS [Days Left]
FROM Accts_Payable AP
INNER JOIN [Accts_Payable Query] Q
ON AP.Bill_EntryID = Q.Bill_EntryID

This is not the only problem. The DateDiff function expects you to place
two dates in the second and third parameter. It appears you have placed
a
subtraction of the two dates in the second parameter and given it the
value
0 for the other date. That's probably not what you intended, and it's
probably not going to give you reasonable results.

May I suggest perhaps what you wanted is:

SELECT AP.Bill_Date, AP.Bill_Amount,
AP.[Date Paid], Q.Discount_Amt,
(AP.Bill_Amount - Q.Discount_Amt) AS [New Bill_Amt],
DateDiff("d", Q.Bill_Date - [Date Paid], 0) AS Days,
Date() AS [Report Date],
DateDiff("d", Date(), AP.Bill_Date) + 6 AS [Days Left]
FROM Accts_Payable AP
INNER JOIN [Accts_Payable Query] Q
ON AP.Bill_EntryID = Q.Bill_EntryID

If the Bill_Date was 3 days before present, then DateDiff would
return -3.
You than add 6. It would seem you may mean that the due date is 6 days
forward from the Bill_Date, so the -3 changes to +3, meaning there are 3
days remaining.

There's some guess work going on here, so check it out carefully before
using this.

Tom Ellison


confusedinc said:
I am trying to figure out how I can produce the following:
I need to show accounts that have (0)zero days left before they need to
be
paid.
I have created the the following query:

SELECT Accts_Payable.Bill_Date, Accts_Payable.Bill_Amount,
Accts_Payable.[Date Paid], [Accts_Payable Query].Discount_Amt,
(Accts_Payable.Bill_Amount-[Accts_Payable Query].Discount_Amt) AS [New
Bill_Amt], DateDiff("d",[Accts_Payable Query].Bill_Date-[Date Paid],0)
AS
Days, Date() AS [Report Date], DateDiff("d",[Report
Date]-Accts_Payable.Bill_Date,0)+6 AS [Days Left]
FROM Accts_Payable INNER JOIN [Accts_Payable Query] ON
Accts_Payable.Bill_EntryID = [Accts_Payable Query].Bill_EntryID;

I cannot get information to produce days remaining unless I put a Date
in
the [Date Paid] field. I do not want to do it this way. I need to be
able
to
produce the report based on data already in table.
Any help appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top