Query Criteria

[ladybug via AccessMonster.com]

I need criteria for a date field in my query. I want only the dates returned
that are older than 5 business days.

Thanks!

 

[Marshall Barton]

I need criteria for a date field in my query. I want only the dates returned
that are older than 5 business days.

That might be possible if you can provide a very detailed
explanation of how your database knows what a "business day"
is. Just saing that 5 business days is a week is rarely
sufficient, but if that's all you mean, use a criteria like:

DateAdd("d", -7, Date())

Any other definition of "business day" will require a table
or a complex Function that can be used to calculate the
earliest date you want to see in the query's result.

 

[ladybug via AccessMonster.com]

The code you gave is perfect except it does not remove weekends. Can you
help me with the function you were referring to? I know very little about
code so if you can help I may need A LOT of instruction. Thank you for your
time.

 

[KARL DEWEY]

Here is one way. If you know subqueries then this can be done as a sub.
Create a table named CountNumber with integer field named CountNUM
containing numbers 0 (zero) through 99. Add this query in your query design
view and use criteria on your date field -------- <[Dates]

SELECT TOP 5 DateAdd("d",-[CountNUM],Date()) AS Dates
FROM CountNumber
WHERE (((Weekday(DateAdd("d",-[CountNUM],Date()))) Between 2 And 6))
ORDER BY DateAdd("d",-[CountNUM],Date()) DESC;

 

[Marshall Barton]

Sorry, but I can't do much of anything useful until you
provide a very detailed explanation of how your database
knows what a "business day" is. The least you must do is
define "business day" is very specific terms.

 

[raskew via AccessMonster.com]

Re "Complex Functions" -- here's another that allows the user to calculate
workdays, excluding any days of the week (not limited to Saturday(7) and
Sunday(1):

Function DateDiffExclude2(pstartdte As Date, _
penddte As Date, _
pexclude As String) As Integer
'*********************************************
'Purpose: Return number of days between two
' dates, excluding weekdays(Sun = 1
' thru Sat = 7) specified by user
'Coded by: raskew
'Inputs: from debug (immediate) window:
' -excludes Saturday (7) and Sunday (1) from count
' ? DateDiffExclude2(#2/10/06#, #7/13/06#, "17")
'Output: 110
'*********************************************

Dim WeekHold As String
Dim WeekKeep As String
Dim FullWeek As Integer
Dim OddDays As Integer
Dim n As Integer

WeekHold = "1234567123456"
'get # of full weeks (7 days) & convert to # of days
FullWeek = Int((penddte - pstartdte + 1) / 7) * (7 - Len(pexclude))
'get # of days remaining after FullWeek is determined
OddDays = (penddte - pstartdte + 1) Mod 7
'string representation of the weekdays contained in OddDays
WeekKeep = Mid(WeekHold, WeekDay(pstartdte), OddDays)
'use boolean statement to reduce OddDays by 1 for each
'pexclude weekday found in WeekKeep
For n = 1 To Len(pexclude)
OddDays = OddDays + (InStr(WeekKeep, Mid(pexclude, n, 1)) > 0)
Next n

DateDiffExclude2 = FullWeek + OddDays

End Function

Here is one way. If you know subqueries then this can be done as a sub.
Create a table named CountNumber with integer field named CountNUM
containing numbers 0 (zero) through 99. Add this query in your query design
view and use criteria on your date field -------- <[Dates]

SELECT TOP 5 DateAdd("d",-[CountNUM],Date()) AS Dates
FROM CountNumber
WHERE (((Weekday(DateAdd("d",-[CountNUM],Date()))) Between 2 And 6))
ORDER BY DateAdd("d",-[CountNUM],Date()) DESC;

I need criteria for a date field in my query. I want only the dates returned
that are older than 5 business days.

Thanks!

 

[KARL DEWEY]

After I posted I realized there needed to be one more query step. Use the
posted query in a second query --
SELECT Min([Dates-5_Bus_Days].Dates) AS MinOfDates
FROM [Dates-5_Bus_Days];

This results in the earlist date in the output. Use it in criteria
<[MinOfDates]
--
KARL DEWEY
Build a little - Test a little

Here is one way. If you know subqueries then this can be done as a sub.
Create a table named CountNumber with integer field named CountNUM
containing numbers 0 (zero) through 99. Add this query in your query design
view and use criteria on your date field -------- <[Dates]

SELECT TOP 5 DateAdd("d",-[CountNUM],Date()) AS Dates
FROM CountNumber
WHERE (((Weekday(DateAdd("d",-[CountNUM],Date()))) Between 2 And 6))
ORDER BY DateAdd("d",-[CountNUM],Date()) DESC;

--
KARL DEWEY
Build a little - Test a little

I need criteria for a date field in my query. I want only the dates returned
that are older than 5 business days.

Thanks!

 

[ladybug via AccessMonster.com]

I do not have anything in the database that defines "business day." What I
mean by business day are days Monday through Friday (excluding holidays). So
if I run my query today, I only want entries with dates 4/23 and older. Does
that help?

Sorry, but I can't do much of anything useful until you
provide a very detailed explanation of how your database
knows what a "business day" is. The least you must do is
define "business day" is very specific terms.

The code you gave is perfect except it does not remove weekends. Can you
help me with the function you were referring to? I know very little about

[quoted text clipped - 13 lines]

 

[ladybug via AccessMonster.com]

Ok, I am so sorry...I am just not following.
Here is the code for the query that I have now:
SELECT tbl_status_of_lif.number AS [Lifecycle #], tbl_status_of_lif.status_dt
AS [Status Date], tbl_status_of_lif.status AS Status, tbl_status_of_lif.
sub_by AS [Submitted by]
FROM tbl_status_of_lif
WHERE (((tbl_status_of_lif.status)<>"lc closed") AND ((tbl_status_of_lif.
status_id) In (SELECT Max(Status_ID) as MaxID
FROM tbl_status_of_lif INNER JOIN
(SELECT number, Max(Status_Dt) as LastDate
FROM tbl_status_of_lif
GROUP BY number) as Tmp
ON tbl_status_of_lif.number = Tmp.number
and tbl_status_of_lif.Status_dt = Tmp.LastDate
GROUP BY tbl_status_of_lif.number, tbl_status_of_lif.Status_dt)))
ORDER BY tbl_status_of_lif.status_dt DESC;

I built the table you told me to. Now where do I put what you told me? Do I
add it to this query or do I create a new query. Sorry that I am not getting
it, but I really do appreciate your help!

After I posted I realized there needed to be one more query step. Use the
posted query in a second query --
SELECT Min([Dates-5_Bus_Days].Dates) AS MinOfDates
FROM [Dates-5_Bus_Days];

This results in the earlist date in the output. Use it in criteria
<[MinOfDates]

Here is one way. If you know subqueries then this can be done as a sub.
Create a table named CountNumber with integer field named CountNUM

[quoted text clipped - 10 lines]

 

[Marshall Barton]

You will have to think this through in depth. At this point
your database does not even "know" what a holiday is.

Since holidays can be different in different areas, in
different years and for different people, just defining
holidays can be a complex issue. Your database will need a
table of at least the holidays, but may also have to include
locations and people.

Excluding Sat. and Sun. from the set of "business days" is
very common, but this simple rule is often violated when
crunch time comes around.

Think about all this and come up a set of definitive
rules/data that can be used to calculate "business days" in
**all** situations.
--
Marsh
MVP [MS Access]

I do not have anything in the database that defines "business day." What I
mean by business day are days Monday through Friday (excluding holidays). So
if I run my query today, I only want entries with dates 4/23 and older. Does
that help?

Sorry, but I can't do much of anything useful until you
provide a very detailed explanation of how your database
knows what a "business day" is. The least you must do is
define "business day" is very specific terms.

The code you gave is perfect except it does not remove weekends. Can you
help me with the function you were referring to? I know very little about

[quoted text clipped - 13 lines]

or a complex Function that can be used to calculate the
earliest date you want to see in the query's result.

 

[KARL DEWEY]

Name the first query Dates-5_Bus_Days
SELECT TOP 5 DateAdd("d",-[CountNUM],Date()) AS Dates
FROM CountNumber
WHERE (((Weekday(DateAdd("d",-[CountNUM],Date()))) Between 2 And 6))
ORDER BY DateAdd("d",-[CountNUM],Date()) DESC;

Name the second query Dates-5_Bus_Days-1
SELECT Min([Dates-5_Bus_Days].Dates) AS MinOfDates
FROM [Dates-5_Bus_Days];

Then here is your query edited --
SELECT tbl_status_of_lif.number AS [Lifecycle #],
tbl_status_of_lif.status_dt AS [Status Date], tbl_status_of_lif.status AS
Status, tbl_status_of_lif.sub_by AS [Submitted by]
FROM tbl_status_of_lif, [Dates-5_Bus_Days-1]
WHERE (((tbl_status_of_lif.status_dt)<[MinOfDates]) AND
((tbl_status_of_lif.status)<>"lc closed") AND ((tbl_status_of_lif.status_id)
In (SELECT Max(Status_ID) as MaxID
FROM tbl_status_of_lif INNER JOIN
(SELECT number, Max(Status_Dt) as LastDate
FROM tbl_status_of_lif
GROUP BY number) as Tmp ON tbl_status_of_lif.number = Tmp.number
and tbl_status_of_lif.Status_dt = Tmp.LastDate
GROUP BY tbl_status_of_lif.number, tbl_status_of_lif.Status_dt)))
ORDER BY tbl_status_of_lif.status_dt DESC;

--
KARL DEWEY
Build a little - Test a little

Ok, I am so sorry...I am just not following.
Here is the code for the query that I have now:
SELECT tbl_status_of_lif.number AS [Lifecycle #], tbl_status_of_lif.status_dt
AS [Status Date], tbl_status_of_lif.status AS Status, tbl_status_of_lif.
sub_by AS [Submitted by]
FROM tbl_status_of_lif
WHERE (((tbl_status_of_lif.status)<>"lc closed") AND ((tbl_status_of_lif.
status_id) In (SELECT Max(Status_ID) as MaxID
FROM tbl_status_of_lif INNER JOIN
(SELECT number, Max(Status_Dt) as LastDate
FROM tbl_status_of_lif
GROUP BY number) as Tmp
ON tbl_status_of_lif.number = Tmp.number
and tbl_status_of_lif.Status_dt = Tmp.LastDate
GROUP BY tbl_status_of_lif.number, tbl_status_of_lif.Status_dt)))
ORDER BY tbl_status_of_lif.status_dt DESC;

I built the table you told me to. Now where do I put what you told me? Do I
add it to this query or do I create a new query. Sorry that I am not getting
it, but I really do appreciate your help!

After I posted I realized there needed to be one more query step. Use the
posted query in a second query --
SELECT Min([Dates-5_Bus_Days].Dates) AS MinOfDates
FROM [Dates-5_Bus_Days];

This results in the earlist date in the output. Use it in criteria
<[MinOfDates]

Here is one way. If you know subqueries then this can be done as a sub.
Create a table named CountNumber with integer field named CountNUM

[quoted text clipped - 10 lines]

Thanks!

 

[KARL DEWEY]

If you want to omit holidays then build a table with DateTime field Holiday.
Use this as first query --
SELECT TOP 5 DateAdd("d",-[CountNUM],Date()) AS Dates
FROM CountNumber, Holiday
WHERE ((Weekday(DateAdd("d",-[CountNUM],Date()))) Between 2 And 6) AND
DateAdd("d",-[CountNUM],Date()) <> [Holiday].[Holiday]
ORDER BY DateAdd("d",-[CountNUM],Date()) DESC;

--
KARL DEWEY
Build a little - Test a little

Name the first query Dates-5_Bus_Days
SELECT TOP 5 DateAdd("d",-[CountNUM],Date()) AS Dates
FROM CountNumber
WHERE (((Weekday(DateAdd("d",-[CountNUM],Date()))) Between 2 And 6))
ORDER BY DateAdd("d",-[CountNUM],Date()) DESC;

Name the second query Dates-5_Bus_Days-1
SELECT Min([Dates-5_Bus_Days].Dates) AS MinOfDates
FROM [Dates-5_Bus_Days];

Then here is your query edited --
SELECT tbl_status_of_lif.number AS [Lifecycle #],
tbl_status_of_lif.status_dt AS [Status Date], tbl_status_of_lif.status AS
Status, tbl_status_of_lif.sub_by AS [Submitted by]
FROM tbl_status_of_lif, [Dates-5_Bus_Days-1]
WHERE (((tbl_status_of_lif.status_dt)<[MinOfDates]) AND
((tbl_status_of_lif.status)<>"lc closed") AND ((tbl_status_of_lif.status_id)
In (SELECT Max(Status_ID) as MaxID
FROM tbl_status_of_lif INNER JOIN
(SELECT number, Max(Status_Dt) as LastDate
FROM tbl_status_of_lif
GROUP BY number) as Tmp ON tbl_status_of_lif.number = Tmp.number
and tbl_status_of_lif.Status_dt = Tmp.LastDate
GROUP BY tbl_status_of_lif.number, tbl_status_of_lif.Status_dt)))
ORDER BY tbl_status_of_lif.status_dt DESC;

--
KARL DEWEY
Build a little - Test a little

Ok, I am so sorry...I am just not following.
Here is the code for the query that I have now:
SELECT tbl_status_of_lif.number AS [Lifecycle #], tbl_status_of_lif.status_dt
AS [Status Date], tbl_status_of_lif.status AS Status, tbl_status_of_lif.
sub_by AS [Submitted by]
FROM tbl_status_of_lif
WHERE (((tbl_status_of_lif.status)<>"lc closed") AND ((tbl_status_of_lif.
status_id) In (SELECT Max(Status_ID) as MaxID
FROM tbl_status_of_lif INNER JOIN
(SELECT number, Max(Status_Dt) as LastDate
FROM tbl_status_of_lif
GROUP BY number) as Tmp
ON tbl_status_of_lif.number = Tmp.number
and tbl_status_of_lif.Status_dt = Tmp.LastDate
GROUP BY tbl_status_of_lif.number, tbl_status_of_lif.Status_dt)))
ORDER BY tbl_status_of_lif.status_dt DESC;

I built the table you told me to. Now where do I put what you told me? Do I
add it to this query or do I create a new query. Sorry that I am not getting
it, but I really do appreciate your help!

After I posted I realized there needed to be one more query step. Use the
posted query in a second query --
SELECT Min([Dates-5_Bus_Days].Dates) AS MinOfDates
FROM [Dates-5_Bus_Days];

This results in the earlist date in the output. Use it in criteria
<[MinOfDates]
Here is one way. If you know subqueries then this can be done as a sub.
Create a table named CountNumber with integer field named CountNUM
[quoted text clipped - 10 lines]

Thanks!

 

[ladybug via AccessMonster.com]

THANK YOU SOOOO MUCH!!!!!!!!!!!

If you want to omit holidays then build a table with DateTime field Holiday.
Use this as first query --
SELECT TOP 5 DateAdd("d",-[CountNUM],Date()) AS Dates
FROM CountNumber, Holiday
WHERE ((Weekday(DateAdd("d",-[CountNUM],Date()))) Between 2 And 6) AND

Name the first query Dates-5_Bus_Days
SELECT TOP 5 DateAdd("d",-[CountNUM],Date()) AS Dates

[quoted text clipped - 55 lines]