Work Days

[Guest]

Hi,

I have a query which uses a critiera
Between [reviewdate] And [reviewdate]+[ReviewDays]-1
to plot a weekly itinerary, a crosstab query is then created from this to
print out a report showing all our staff locations Mon-Frid each week.

This works perfectly (almost!). The problem I have is reviewdays is the
number of days the activity took place. If the days are not consecutive
though or fall with a weekend inbetween the results are incorrect.

Does anyone know how I can get round this without creating a temporary table
and making manual amendments?

eg If there is an activity entered skip this date and use the next one, same
applies to if its a Saturday or Sunday. The query has several tables linked
to get all the data for the report - not sure how to produce this sort of
recordset / crosstab query by code.

Anyhelp would be most appreciated! Been trying to overcome this for a while
now...

Sue

 

[Douglas J Steele]

Take a look in the Date/Time section of "The Access Web"
http://www.mvps.org/access/

There are a couple of different approaches there.

 

[Arvin Meyer]

In addition to Doug's post, take a look at the GetBusinessDay function which
may help:

http://www.datastrat.com/Code/GetBusinessDay.txt
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Thank you both for your posts. The problem I have is I do not know how to
apply a similar function to my scenario. Can you use a function call from
within a query in the criteria? Maybe it will help if I show you what I am
trying to achieve. 3 tables joined to produce all the fields needed on report:

Table: Itinerary

SpecialistID
Activity
DealerCode
ReviewDate
ReviewDays

Table: Zonelist

DealerCode
DealerName

Table: Specialists

SpecialistID
Specialist

ReviewDays is the number of days the activity lasted (between 1 and 5
normally). Currently I use a table tblDates which holds records for all
weekday dates in 2005 in a field TheDate, this table is also added to the
query with criteria in TheDate
Between [reviewdate] And [reviewdate]+[ReviewDays]-1
to get the query to produce virtual records containing an entry per day of
each activity based on the length of the activity (reviewdays). This works
until the activity is split by a weekend or another activity, then the
virtual records span across the wrong dates.

Can anyone help me some more on this please? Thanks...

Sue

In addition to Doug's post, take a look at the GetBusinessDay function which
may help:

http://www.datastrat.com/Code/GetBusinessDay.txt
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi,

I have a query which uses a critiera
Between [reviewdate] And [reviewdate]+[ReviewDays]-1
to plot a weekly itinerary, a crosstab query is then created from this to
print out a report showing all our staff locations Mon-Frid each week.

This works perfectly (almost!). The problem I have is reviewdays is the
number of days the activity took place. If the days are not consecutive
though or fall with a weekend inbetween the results are incorrect.

Does anyone know how I can get round this without creating a temporary table
and making manual amendments?

eg If there is an activity entered skip this date and use the next one, same
applies to if its a Saturday or Sunday. The query has several tables linked
to get all the data for the report - not sure how to produce this sort of
recordset / crosstab query by code.

Anyhelp would be most appreciated! Been trying to overcome this for a while
now...

Sue

 

[Arvin Meyer]

Yes, in Access/JET you can use a VBA function like this:

Select Field1, MyFunction([MyField] As Expr1 From MyTable;
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

Thank you both for your posts. The problem I have is I do not know how to
apply a similar function to my scenario. Can you use a function call from
within a query in the criteria? Maybe it will help if I show you what I am
trying to achieve. 3 tables joined to produce all the fields needed on report:

Table: Itinerary

SpecialistID
Activity
DealerCode
ReviewDate
ReviewDays

Table: Zonelist

DealerCode
DealerName

Table: Specialists

SpecialistID
Specialist

ReviewDays is the number of days the activity lasted (between 1 and 5
normally). Currently I use a table tblDates which holds records for all
weekday dates in 2005 in a field TheDate, this table is also added to the
query with criteria in TheDate
Between [reviewdate] And [reviewdate]+[ReviewDays]-1
to get the query to produce virtual records containing an entry per day of
each activity based on the length of the activity (reviewdays). This works
until the activity is split by a weekend or another activity, then the
virtual records span across the wrong dates.

Can anyone help me some more on this please? Thanks...

Sue

In addition to Doug's post, take a look at the GetBusinessDay function which
may help:

http://www.datastrat.com/Code/GetBusinessDay.txt
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi,

I have a query which uses a critiera
Between [reviewdate] And [reviewdate]+[ReviewDays]-1
to plot a weekly itinerary, a crosstab query is then created from this to
print out a report showing all our staff locations Mon-Frid each week.

This works perfectly (almost!). The problem I have is reviewdays is the
number of days the activity took place. If the days are not consecutive
though or fall with a weekend inbetween the results are incorrect.

Does anyone know how I can get round this without creating a temporary table
and making manual amendments?

eg If there is an activity entered skip this date and use the next

one,
same

applies to if its a Saturday or Sunday. The query has several tables linked
to get all the data for the report - not sure how to produce this sort of
recordset / crosstab query by code.

Anyhelp would be most appreciated! Been trying to overcome this for a while
now...

Sue

 

[Guest]

I still can not get my head round how I can achieve this. The only way I can
think of is to write the results to a temporary table maybe. I would need to
open my recordset and run through each record creating a new record for each
day based on the length of days (reviewdays) taking into account (skipping)
any existing activity or weekend. I have a query which contains the recordset
made up of data from three tables, can I open a recordset from an existing
query in code? My vb skills are VERY basic...

Thanks in advance for any help.
Sue

Yes, in Access/JET you can use a VBA function like this:

Select Field1, MyFunction([MyField] As Expr1 From MyTable;
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

Thank you both for your posts. The problem I have is I do not know how to
apply a similar function to my scenario. Can you use a function call from
within a query in the criteria? Maybe it will help if I show you what I am
trying to achieve. 3 tables joined to produce all the fields needed on report:

Table: Itinerary

SpecialistID
Activity
DealerCode
ReviewDate
ReviewDays

Table: Zonelist

DealerCode
DealerName

Table: Specialists

SpecialistID
Specialist

ReviewDays is the number of days the activity lasted (between 1 and 5
normally). Currently I use a table tblDates which holds records for all
weekday dates in 2005 in a field TheDate, this table is also added to the
query with criteria in TheDate
Between [reviewdate] And [reviewdate]+[ReviewDays]-1
to get the query to produce virtual records containing an entry per day of
each activity based on the length of the activity (reviewdays). This works
until the activity is split by a weekend or another activity, then the
virtual records span across the wrong dates.

Can anyone help me some more on this please? Thanks...

Sue

In addition to Doug's post, take a look at the GetBusinessDay function which
may help:

http://www.datastrat.com/Code/GetBusinessDay.txt
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi,

I have a query which uses a critiera
Between [reviewdate] And [reviewdate]+[ReviewDays]-1
to plot a weekly itinerary, a crosstab query is then created from this to
print out a report showing all our staff locations Mon-Frid each week.

This works perfectly (almost!). The problem I have is reviewdays is the
number of days the activity took place. If the days are not consecutive
though or fall with a weekend inbetween the results are incorrect.

Does anyone know how I can get round this without creating a temporary
table
and making manual amendments?

eg If there is an activity entered skip this date and use the next one,
same
applies to if its a Saturday or Sunday. The query has several tables
linked
to get all the data for the report - not sure how to produce this sort of
recordset / crosstab query by code.

Anyhelp would be most appreciated! Been trying to overcome this for a
while
now...

Sue