report to show workdays on a schedule

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

Guest

I have an Excel file that has SEVERAL calculated fields from one date input.
I have someone input the start day of a project and then it shows when each
task needs to be completed by to meet our target date.

I would like for this to be in Access however can't figure out how to put
those dates in the report. I have the report which reflects the fields....
however being new to Access I can't tell the report to calculate the days.
So I will have 27 different fields all showing results from one date input.

Would this be easier to do with fields? Should I just make a bunch of
redundent fields, do the calculation and then put those fields in the report?

Thanx for your help!
 
This is not that difficult. Each date on the report is based on the single
date in the table, I assume. If so, you can create text box controls on the
report for the other dates. Each of those dates has to be calculated based
on some interval of time. I assume that is either stored in your table, or
is a fixed value. When you want to calculate a date, use a text box with a
control source property set to calculate the date. For example, lets say
SomeDay is always 9 days from StartDate and StartDate is in a control on the
report named txtStartDate. So to present the correct date for SomeDay, it's
control would be like:
=DateAdd("d", 9, [txtStartDate])

Now, if the time interval is in a field in the form's record source, but you
don't want it visible on the report, you will still want to put it in a text
box so the DateAdd function can find it. To hide it, set its Visible
propertry to No. The the same technique applies:
=DateAdd("d", [txtSomeNumberOfDays], [txtStartDate])
 
Still stuck...

In Excel I am using the function =workday(FIELD,DAYS)
{=workday, A3,2)} and this would add two days to the date in A3.

This works great.. however now I need it in a database report.

Sorry if this seems basic... I am new to Access. I have used Alpha Software
in the past and could do this in moments however our office wants to use
Access if possible.

Thanx again!


Klatuu said:
This is not that difficult. Each date on the report is based on the single
date in the table, I assume. If so, you can create text box controls on the
report for the other dates. Each of those dates has to be calculated based
on some interval of time. I assume that is either stored in your table, or
is a fixed value. When you want to calculate a date, use a text box with a
control source property set to calculate the date. For example, lets say
SomeDay is always 9 days from StartDate and StartDate is in a control on the
report named txtStartDate. So to present the correct date for SomeDay, it's
control would be like:
=DateAdd("d", 9, [txtStartDate])

Now, if the time interval is in a field in the form's record source, but you
don't want it visible on the report, you will still want to put it in a text
box so the DateAdd function can find it. To hide it, set its Visible
propertry to No. The the same technique applies:
=DateAdd("d", [txtSomeNumberOfDays], [txtStartDate])


--
Dave Hargis, Microsoft Access MVP


Charles said:
I have an Excel file that has SEVERAL calculated fields from one date input.
I have someone input the start day of a project and then it shows when each
task needs to be completed by to meet our target date.

I would like for this to be in Access however can't figure out how to put
those dates in the report. I have the report which reflects the fields....
however being new to Access I can't tell the report to calculate the days.
So I will have 27 different fields all showing results from one date input.

Would this be easier to do with fields? Should I just make a bunch of
redundent fields, do the calculation and then put those fields in the report?

Thanx for your help!
Click to expand...
Click to expand...
 
What's an Alpha.
Don't whine about Access. It is probably the best desktop database system
available.
Look in VBA Help for the DateAdd function. It is similar to what you do in
your Excel example. In Access it would be =DateAdd("2", 9, [txtStartDate])
--
Dave Hargis, Microsoft Access MVP


Charles said:
Still stuck...

In Excel I am using the function =workday(FIELD,DAYS)
{=workday, A3,2)} and this would add two days to the date in A3.

This works great.. however now I need it in a database report.

Sorry if this seems basic... I am new to Access. I have used Alpha Software
in the past and could do this in moments however our office wants to use
Access if possible.

Thanx again!


Klatuu said:
This is not that difficult. Each date on the report is based on the single
date in the table, I assume. If so, you can create text box controls on the
report for the other dates. Each of those dates has to be calculated based
on some interval of time. I assume that is either stored in your table, or
is a fixed value. When you want to calculate a date, use a text box with a
control source property set to calculate the date. For example, lets say
SomeDay is always 9 days from StartDate and StartDate is in a control on the
report named txtStartDate. So to present the correct date for SomeDay, it's
control would be like:
=DateAdd("d", 9, [txtStartDate])

Now, if the time interval is in a field in the form's record source, but you
don't want it visible on the report, you will still want to put it in a text
box so the DateAdd function can find it. To hide it, set its Visible
propertry to No. The the same technique applies:
=DateAdd("d", [txtSomeNumberOfDays], [txtStartDate])


--
Dave Hargis, Microsoft Access MVP


Charles said:
I have an Excel file that has SEVERAL calculated fields from one date input.
I have someone input the start day of a project and then it shows when each
task needs to be completed by to meet our target date.

I would like for this to be in Access however can't figure out how to put
those dates in the report. I have the report which reflects the fields....
however being new to Access I can't tell the report to calculate the days.
So I will have 27 different fields all showing results from one date input.

Would this be easier to do with fields? Should I just make a bunch of
redundent fields, do the calculation and then put those fields in the report?

Thanx for your help!
Click to expand...
Click to expand...
Click to expand...
 
Actually, the Excel Workday function ignores weekends and any dates
identified as holidays, so Charles will likely need
http://www.mvps.org/access/datetime/date0012.htm from "The Access Web", or
my September, 2004 "Access Answers" column in Pinnacle Publication's "Smart
Access". (The column and sample database can be downloaded for free at
http://www.mvps.org/access/datetime/date0012.htm)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Klatuu said:
What's an Alpha.
Don't whine about Access. It is probably the best desktop database system
available.
Look in VBA Help for the DateAdd function. It is similar to what you do
in
your Excel example. In Access it would be =DateAdd("2", 9,
[txtStartDate])
--
Dave Hargis, Microsoft Access MVP


Charles said:
Still stuck...

In Excel I am using the function =workday(FIELD,DAYS)
{=workday, A3,2)} and this would add two days to the date in A3.

This works great.. however now I need it in a database report.

Sorry if this seems basic... I am new to Access. I have used Alpha
Software
in the past and could do this in moments however our office wants to use
Access if possible.

Thanx again!


Klatuu said:
This is not that difficult. Each date on the report is based on the
single
date in the table, I assume. If so, you can create text box controls
on the
report for the other dates. Each of those dates has to be calculated
based
on some interval of time. I assume that is either stored in your
table, or
is a fixed value. When you want to calculate a date, use a text box
with a
control source property set to calculate the date. For example, lets
say
SomeDay is always 9 days from StartDate and StartDate is in a control
on the
report named txtStartDate. So to present the correct date for SomeDay,
it's
control would be like:
=DateAdd("d", 9, [txtStartDate])

Now, if the time interval is in a field in the form's record source,
but you
don't want it visible on the report, you will still want to put it in a
text
box so the DateAdd function can find it. To hide it, set its Visible
propertry to No. The the same technique applies:
=DateAdd("d", [txtSomeNumberOfDays], [txtStartDate])


--
Dave Hargis, Microsoft Access MVP


:

I have an Excel file that has SEVERAL calculated fields from one date
input.
I have someone input the start day of a project and then it shows
when each
task needs to be completed by to meet our target date.

I would like for this to be in Access however can't figure out how to
put
those dates in the report. I have the report which reflects the
fields....
however being new to Access I can't tell the report to calculate the
days.
So I will have 27 different fields all showing results from one date
input.

Would this be easier to do with fields? Should I just make a bunch
of
redundent fields, do the calculation and then put those fields in the
report?

Thanx for your help!
Click to expand...
Click to expand...
Click to expand...
 
Still stuck...

In Excel I am using the function =workday(FIELD,DAYS)
{=workday, A3,2)} and this would add two days to the date in A3.

This works great.. however now I need it in a database report.

Sorry if this seems basic... I am new to Access. I have used Alpha Software
in the past and could do this in moments however our office wants to use
Access if possible.
Click to expand...

Unfortunately, Access' builtin date functions don't know about
workdays. You will need a table of holidays, and some VBA code; for an
example see

http://www.mvps.org/access/datetime/date0012.htm

John W. Vinson[MVP]
 
Back
Top