Date Calculation

[Guest]

I am working on a "Days out of Compliance" field for my reports. I need to
know after a 4 day window that the item has not been received. I would like
to put the number of days on the report that is printed out. I have done
some research and I just can't find what i need. I tried the following
calculation:

DateDiff("d",[Date],Now())

and it did not return any results. I was trying to test something to see if
I could get it to work. Can someone please help me out with this.

 

[Guest]

Post your SQL.

 

[Guest]

SELECT [Reroutes Table].ID, [Reroutes Table].Date, [Reroutes Table].[Reroute
Contacts], [Reroutes Table].[MCS Number], [Reroutes Table].[Count Of Pieces],
[Reroutes Table].[1st Attempt Follow-up Date], [Reroutes Table].[2nd Attempt
Follow-up Date], [Reroutes Table].[3rd Attempt Follow-up Date], [Reroutes
Table].[Date Received Back], [Reroutes Table].[Days Out Of Compliance]
FROM [Reroutes Table]
WHERE ((([Reroutes Table].[Date Received Back]) Is Null)) OR ((([Reroutes
Table].[Days Out Of Compliance])=DateDiff("d",[Date],Now())));

Post your SQL.

I am working on a "Days out of Compliance" field for my reports. I need to
know after a 4 day window that the item has not been received. I would like
to put the number of days on the report that is printed out. I have done
some research and I just can't find what i need. I tried the following
calculation:

DateDiff("d",[Date],Now())

and it did not return any results. I was trying to test something to see if
I could get it to work. Can someone please help me out with this.

 

[Guest]

I have copied the Work Day modules from one of the websites that I see very
ofter on this website associated with date/time. Now that I have the module
I am not quite sure what to do with it.

Post your SQL.

I am working on a "Days out of Compliance" field for my reports. I need to
know after a 4 day window that the item has not been received. I would like
to put the number of days on the report that is printed out. I have done
some research and I just can't find what i need. I tried the following
calculation:

DateDiff("d",[Date],Now())

and it did not return any results. I was trying to test something to see if
I could get it to work. Can someone please help me out with this.

 

[Guest]

Try this --
SELECT [Reroutes Table].ID, [Reroutes Table].Date, [Reroutes Table].[Reroute
Contacts], [Reroutes Table].[MCS Number], [Reroutes Table].[Count Of Pieces],
[Reroutes Table].[1st Attempt Follow-up Date], [Reroutes Table].[2nd Attempt
Follow-up Date], [Reroutes Table].[3rd Attempt Follow-up Date], [Reroutes
Table].[Date Received Back], DateDiff("d",[Date],Now()) AS [Days Out Of
Compliance]
FROM [Reroutes Table]
WHERE ((([Reroutes Table].[Date Received Back]) Is Null));

SELECT [Reroutes Table].ID, [Reroutes Table].Date, [Reroutes Table].[Reroute
Contacts], [Reroutes Table].[MCS Number], [Reroutes Table].[Count Of Pieces],
[Reroutes Table].[1st Attempt Follow-up Date], [Reroutes Table].[2nd Attempt
Follow-up Date], [Reroutes Table].[3rd Attempt Follow-up Date], [Reroutes
Table].[Date Received Back], [Reroutes Table].[Days Out Of Compliance]
FROM [Reroutes Table]
WHERE ((([Reroutes Table].[Date Received Back]) Is Null)) OR ((([Reroutes
Table].[Days Out Of Compliance])=DateDiff("d",[Date],Now())));

Post your SQL.

I am working on a "Days out of Compliance" field for my reports. I need to
know after a 4 day window that the item has not been received. I would like
to put the number of days on the report that is printed out. I have done
some research and I just can't find what i need. I tried the following
calculation:

DateDiff("d",[Date],Now())

and it did not return any results. I was trying to test something to see if
I could get it to work. Can someone please help me out with this.

 

[Guest]

That worked thank you very much. One more question - If I want to start the
count after the 4th day where do I put that? I know somehow I need to add
the 4 days to the date. I'm just not sure where. Is that correct?

Try this --
SELECT [Reroutes Table].ID, [Reroutes Table].Date, [Reroutes Table].[Reroute
Contacts], [Reroutes Table].[MCS Number], [Reroutes Table].[Count Of Pieces],
[Reroutes Table].[1st Attempt Follow-up Date], [Reroutes Table].[2nd Attempt
Follow-up Date], [Reroutes Table].[3rd Attempt Follow-up Date], [Reroutes
Table].[Date Received Back], DateDiff("d",[Date],Now()) AS [Days Out Of
Compliance]
FROM [Reroutes Table]
WHERE ((([Reroutes Table].[Date Received Back]) Is Null));

SELECT [Reroutes Table].ID, [Reroutes Table].Date, [Reroutes Table].[Reroute
Contacts], [Reroutes Table].[MCS Number], [Reroutes Table].[Count Of Pieces],
[Reroutes Table].[1st Attempt Follow-up Date], [Reroutes Table].[2nd Attempt
Follow-up Date], [Reroutes Table].[3rd Attempt Follow-up Date], [Reroutes
Table].[Date Received Back], [Reroutes Table].[Days Out Of Compliance]
FROM [Reroutes Table]
WHERE ((([Reroutes Table].[Date Received Back]) Is Null)) OR ((([Reroutes
Table].[Days Out Of Compliance])=DateDiff("d",[Date],Now())));

Post your SQL.

:

I am working on a "Days out of Compliance" field for my reports. I need to
know after a 4 day window that the item has not been received. I would like
to put the number of days on the report that is printed out. I have done
some research and I just can't find what i need. I tried the following
calculation:

DateDiff("d",[Date],Now())

and it did not return any results. I was trying to test something to see if
I could get it to work. Can someone please help me out with this.

 

[Guest]

Change the WHERE of the SQL to ---
WHERE ((([Reroutes Table].[Date Received Back]) Is Null) AND
((DateDiff("d",[Date],Now()))>4));

That worked thank you very much. One more question - If I want to start the
count after the 4th day where do I put that? I know somehow I need to add
the 4 days to the date. I'm just not sure where. Is that correct?

Try this --
SELECT [Reroutes Table].ID, [Reroutes Table].Date, [Reroutes Table].[Reroute
Contacts], [Reroutes Table].[MCS Number], [Reroutes Table].[Count Of Pieces],
[Reroutes Table].[1st Attempt Follow-up Date], [Reroutes Table].[2nd Attempt
Follow-up Date], [Reroutes Table].[3rd Attempt Follow-up Date], [Reroutes
Table].[Date Received Back], DateDiff("d",[Date],Now()) AS [Days Out Of
Compliance]
FROM [Reroutes Table]
WHERE ((([Reroutes Table].[Date Received Back]) Is Null));

SELECT [Reroutes Table].ID, [Reroutes Table].Date, [Reroutes Table].[Reroute
Contacts], [Reroutes Table].[MCS Number], [Reroutes Table].[Count Of Pieces],
[Reroutes Table].[1st Attempt Follow-up Date], [Reroutes Table].[2nd Attempt
Follow-up Date], [Reroutes Table].[3rd Attempt Follow-up Date], [Reroutes
Table].[Date Received Back], [Reroutes Table].[Days Out Of Compliance]
FROM [Reroutes Table]
WHERE ((([Reroutes Table].[Date Received Back]) Is Null)) OR ((([Reroutes
Table].[Days Out Of Compliance])=DateDiff("d",[Date],Now())));

:

Post your SQL.

:

I am working on a "Days out of Compliance" field for my reports. I need to
know after a 4 day window that the item has not been received. I would like
to put the number of days on the report that is printed out. I have done
some research and I just can't find what i need. I tried the following
calculation:

DateDiff("d",[Date],Now())

and it did not return any results. I was trying to test something to see if
I could get it to work. Can someone please help me out with this.

 

[Guest]

OK, I got it to show the information that was over 4 days old - thank you so
much - I have one other question and this may link to the other question that
I posted - How can I get it to take out the weekend days. I have the working
day modules but have no idea what to do with them or if i need to do anything
with them.

Change the WHERE of the SQL to ---
WHERE ((([Reroutes Table].[Date Received Back]) Is Null) AND
((DateDiff("d",[Date],Now()))>4));

That worked thank you very much. One more question - If I want to start the
count after the 4th day where do I put that? I know somehow I need to add
the 4 days to the date. I'm just not sure where. Is that correct?

Try this --
SELECT [Reroutes Table].ID, [Reroutes Table].Date, [Reroutes Table].[Reroute
Contacts], [Reroutes Table].[MCS Number], [Reroutes Table].[Count Of Pieces],
[Reroutes Table].[1st Attempt Follow-up Date], [Reroutes Table].[2nd Attempt
Follow-up Date], [Reroutes Table].[3rd Attempt Follow-up Date], [Reroutes
Table].[Date Received Back], DateDiff("d",[Date],Now()) AS [Days Out Of
Compliance]
FROM [Reroutes Table]
WHERE ((([Reroutes Table].[Date Received Back]) Is Null));

:

SELECT [Reroutes Table].ID, [Reroutes Table].Date, [Reroutes Table].[Reroute
Contacts], [Reroutes Table].[MCS Number], [Reroutes Table].[Count Of Pieces],
[Reroutes Table].[1st Attempt Follow-up Date], [Reroutes Table].[2nd Attempt
Follow-up Date], [Reroutes Table].[3rd Attempt Follow-up Date], [Reroutes
Table].[Date Received Back], [Reroutes Table].[Days Out Of Compliance]
FROM [Reroutes Table]
WHERE ((([Reroutes Table].[Date Received Back]) Is Null)) OR ((([Reroutes
Table].[Days Out Of Compliance])=DateDiff("d",[Date],Now())));

:

Post your SQL.

:

I am working on a "Days out of Compliance" field for my reports. I need to
know after a 4 day window that the item has not been received. I would like
to put the number of days on the report that is printed out. I have done
some research and I just can't find what i need. I tried the following
calculation:

DateDiff("d",[Date],Now())

and it did not return any results. I was trying to test something to see if
I could get it to work. Can someone please help me out with this.