START DATE: DateAdd("y",-[LEAD TIME DAYS],[SHIP DATE]) - weekend??

[Guest]

I have used the formula

START DATE: DateAdd("y",-[LEAD TIME DAYS],[SHIP DATE])

to work out the Start Date and this works.

I would like to improve this by excluding weekends.

How can I do this?

Thank you.

 

[Allen Browne]

Use "d", not "y" to add days.

To exclude weekends, you will need to work with a VBA function.
Details:
http://www.mvps.org/access/datetime/date0012.htm

 

[Guest]

Thank you.

I have already tried to use the code you have given but it still won't work.
It is ok if I want to Add days to a date excluding weekends but I am trying
to subtract days from a date excluding weekends.

Can you help?

Use "d", not "y" to add days.

To exclude weekends, you will need to work with a VBA function.
Details:
http://www.mvps.org/access/datetime/date0012.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have used the formula

START DATE: DateAdd("y",-[LEAD TIME DAYS],[SHIP DATE])

to work out the Start Date and this works.

I would like to improve this by excluding weekends.

How can I do this?

Thank you.

 

[Michael Gramelspacher]

Thank you.

I have already tried to use the code you have given but it still won't work.
It is ok if I want to Add days to a date excluding weekends but I am trying
to subtract days from a date excluding weekends.

Can you help?

Use "d", not "y" to add days.

To exclude weekends, you will need to work with a VBA function.
Details:
http://www.mvps.org/access/datetime/date0012.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have used the formula

START DATE: DateAdd("y",-[LEAD TIME DAYS],[SHIP DATE])

to work out the Start Date and this works.

I would like to improve this by excluding weekends.

How can I do this?

Thank you.

as an alternative, you could look at this:
http://www.psci.net/gramelsp/temp/Calendars.zip

It uses a calendar table and three parameter queries to get
work date.

It is no thoroughly tested, but it seems to work.

 

[Guest]

Thank you that was helpful.

I need to now adapt it for the query I am using.

Thank you.

Thank you.

I have already tried to use the code you have given but it still won't work.
It is ok if I want to Add days to a date excluding weekends but I am trying
to subtract days from a date excluding weekends.

Can you help?

Use "d", not "y" to add days.

To exclude weekends, you will need to work with a VBA function.
Details:
http://www.mvps.org/access/datetime/date0012.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have used the formula

START DATE: DateAdd("y",-[LEAD TIME DAYS],[SHIP DATE])

to work out the Start Date and this works.

I would like to improve this by excluding weekends.

How can I do this?

Thank you.

as an alternative, you could look at this:
http://www.psci.net/gramelsp/temp/Calendars.zip

It uses a calendar table and three parameter queries to get
work date.

It is no thoroughly tested, but it seems to work.

 

[Michael Gramelspacher]

(e-mail address removed)>,
(e-mail address removed) says...

Thank you that was helpful.

I need to now adapt it for the query I am using.

Thank you.

Thank you.

I have already tried to use the code you have given but it still won't work.
It is ok if I want to Add days to a date excluding weekends but I am trying
to subtract days from a date excluding weekends.

Can you help?

:

Use "d", not "y" to add days.

To exclude weekends, you will need to work with a VBA function.
Details:
http://www.mvps.org/access/datetime/date0012.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have used the formula

START DATE: DateAdd("y",-[LEAD TIME DAYS],[SHIP DATE])

to work out the Start Date and this works.

I would like to improve this by excluding weekends.

How can I do this?

Thank you.

as an alternative, you could look at this:
http://www.psci.net/gramelsp/temp/Calendars.zip

It uses a calendar table and three parameter queries to get

Referring to my Calendars.Zip. If you have now created the
calendar table in your database, imported the queries and the
module, then I am thinking this might work:

SELECT a.Product_Nbr,
a.[Ship Date],
a.[Lead Time Days],
WORKDAY([a].[Ship Date],[a].[Lead Time Days],1) AS
[Start Date]
FROM Table1 AS a;

or this: (watch line wrapping here)

SELECT a.Product_Nbr,
a.[Ship Date],
a.[Lead Time Days],
(SELECT c.calendar_date
FROM Calendar AS c
WHERE c.work_day = 1
AND c.holiday = 0
AND c.calendar_date < [a.Ship Date]
AND c.calendar_date >= DATEADD("d",-(([a.Lead
Time Days] * 2) + 14),[a.Ship Date])
AND [a.LEad Time Days] = (SELECT COUNT(* )
FROM Calendar c2
WHERE
c2.calendar_date < [a.Ship Date]
AND
c2.calendar_date >= c.calendar_date
AND c2.work_day
= 1
AND c.holiday =
0)) AS [Start Date]
FROM Table1 AS a;

 

[Guest]

Thank you once again.

I have imported the Calendar table, the queries and the module which is now
called Module 2 as I already have Module 1.

I am not an expert at Access and am in above my head.

Where would I put the coding that you sent me - SELECT a.Product_Nbr???

And how does it work with the original code I had in the query

START DATE: DateAdd("d",-[LEAD TIME DAYS],[SHIP DATE])

Thank you.

(e-mail address removed)>,
(e-mail address removed) says...

Thank you that was helpful.

I need to now adapt it for the query I am using.

Thank you.

Thank you.

I have already tried to use the code you have given but it still won't work.
It is ok if I want to Add days to a date excluding weekends but I am trying
to subtract days from a date excluding weekends.

Can you help?

:

Use "d", not "y" to add days.

To exclude weekends, you will need to work with a VBA function.
Details:
http://www.mvps.org/access/datetime/date0012.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have used the formula

START DATE: DateAdd("y",-[LEAD TIME DAYS],[SHIP DATE])

to work out the Start Date and this works.

I would like to improve this by excluding weekends.

How can I do this?

Thank you.


as an alternative, you could look at this:
http://www.psci.net/gramelsp/temp/Calendars.zip

It uses a calendar table and three parameter queries to get

Referring to my Calendars.Zip. If you have now created the
calendar table in your database, imported the queries and the
module, then I am thinking this might work:

SELECT a.Product_Nbr,
a.[Ship Date],
a.[Lead Time Days],
WORKDAY([a].[Ship Date],[a].[Lead Time Days],1) AS
[Start Date]
FROM Table1 AS a;

or this: (watch line wrapping here)

SELECT a.Product_Nbr,
a.[Ship Date],
a.[Lead Time Days],
(SELECT c.calendar_date
FROM Calendar AS c
WHERE c.work_day = 1
AND c.holiday = 0
AND c.calendar_date < [a.Ship Date]
AND c.calendar_date >= DATEADD("d",-(([a.Lead
Time Days] * 2) + 14),[a.Ship Date])
AND [a.LEad Time Days] = (SELECT COUNT(* )
FROM Calendar c2
WHERE
c2.calendar_date < [a.Ship Date]
AND
c2.calendar_date >= c.calendar_date
AND c2.work_day
= 1
AND c.holiday =
0)) AS [Start Date]
FROM Table1 AS a;

 

[Michael Gramelspacher]

Thank you once again.

I have imported the Calendar table, the queries and the module which is now
called Module 2 as I already have Module 1.

I am not an expert at Access and am in above my head.

Where would I put the coding that you sent me - SELECT a.Product_Nbr???

And how does it work with the original code I had in the query

START DATE: DateAdd("d",-[LEAD TIME DAYS],[SHIP DATE])

Thank you.

You never furnished a query; you merely furnished a column from
query design view.

Where you see: START DATE: DateAdd("d",-[LEAD TIME DAYS],[SHIP
DATE])

Substitute this: (it is all one line)
Start Date: (SELECT c.calendar_date FROM Calendar AS c WHERE
c.work_day = 1 AND c.holiday = 0 AND c.calendar_date < [a.Ship
Date] AND c.calendar_date >= DATEADD("d",-(([a.Lead Time Days]
* 2) + 14),[a.Ship Date]) AND [a.Lead Time Days] = (SELECT
COUNT(* ) FROM Calendar c2 WHERE c2.calendar_date < [a.Ship
Date] AND c2.calendar_date >= .calendar_date AND c2.work_day
= 1 AND c.holiday = 0))

In other words I substituted a subquery for your DateAdd
expression. The value of Start Date is now determined by the
subquery.

All that is needed for your query to work now is the calendar
table.

 

[Guest]

Thank you - your help has been great and I feel we are so close.

But unfortunately I have copied you code into the query (in one line) and it
has come back with a syntax error.

Where do I start to look? can you spot it.

Thanks in advance

Thank you once again.

I have imported the Calendar table, the queries and the module which is now
called Module 2 as I already have Module 1.

I am not an expert at Access and am in above my head.

Where would I put the coding that you sent me - SELECT a.Product_Nbr???

And how does it work with the original code I had in the query

START DATE: DateAdd("d",-[LEAD TIME DAYS],[SHIP DATE])

Thank you.

You never furnished a query; you merely furnished a column from
query design view.

Where you see: START DATE: DateAdd("d",-[LEAD TIME DAYS],[SHIP
DATE])

Substitute this: (it is all one line)
Start Date: (SELECT c.calendar_date FROM Calendar AS c WHERE
c.work_day = 1 AND c.holiday = 0 AND c.calendar_date < [a.Ship
Date] AND c.calendar_date >= DATEADD("d",-(([a.Lead Time Days]
* 2) + 14),[a.Ship Date]) AND [a.Lead Time Days] = (SELECT
COUNT(* ) FROM Calendar c2 WHERE c2.calendar_date < [a.Ship
Date] AND c2.calendar_date >= .calendar_date AND c2.work_day
= 1 AND c.holiday = 0))

In other words I substituted a subquery for your DateAdd
expression. The value of Start Date is now determined by the
subquery.

All that is needed for your query to work now is the calendar
table.

 

[Guest]

Thank you for your code but when I copied the code into the query I get a
syntax error. Can you see where it is?

I am so grateful for your help. As soon as I get this code finished I can
set up the database and go on my maternity leave happy knowing that it all
works.

Thank you

Thank you once again.

I have imported the Calendar table, the queries and the module which is now
called Module 2 as I already have Module 1.

I am not an expert at Access and am in above my head.

Where would I put the coding that you sent me - SELECT a.Product_Nbr???

And how does it work with the original code I had in the query

START DATE: DateAdd("d",-[LEAD TIME DAYS],[SHIP DATE])

Thank you.

You never furnished a query; you merely furnished a column from
query design view.

Where you see: START DATE: DateAdd("d",-[LEAD TIME DAYS],[SHIP
DATE])

Substitute this: (it is all one line)
Start Date: (SELECT c.calendar_date FROM Calendar AS c WHERE
c.work_day = 1 AND c.holiday = 0 AND c.calendar_date < [a.Ship
Date] AND c.calendar_date >= DATEADD("d",-(([a.Lead Time Days]
* 2) + 14),[a.Ship Date]) AND [a.Lead Time Days] = (SELECT
COUNT(* ) FROM Calendar c2 WHERE c2.calendar_date < [a.Ship
Date] AND c2.calendar_date >= .calendar_date AND c2.work_day
= 1 AND c.holiday = 0))

In other words I substituted a subquery for your DateAdd
expression. The value of Start Date is now determined by the
subquery.

All that is needed for your query to work now is the calendar
table.

 

[Michael Gramelspacher]

(e-mail address removed)>,
(e-mail address removed) says...

Thank you for your code but when I copied the code into the query I get a
syntax error. Can you see where it is?

I am so grateful for your help. As soon as I get this code finished I can
set up the database and go on my maternity leave happy knowing that it all
works.

Thank you

Thank you once again.

I have imported the Calendar table, the queries and the module which is now
called Module 2 as I already have Module 1.

I am not an expert at Access and am in above my head.

Where would I put the coding that you sent me - SELECT a.Product_Nbr???

And how does it work with the original code I had in the query

START DATE: DateAdd("d",-[LEAD TIME DAYS],[SHIP DATE])

Thank you.

You never furnished a query; you merely furnished a column from
query design view.

Where you see: START DATE: DateAdd("d",-[LEAD TIME DAYS],[SHIP
DATE])

Substitute this: (it is all one line)
Start Date: (SELECT c.calendar_date FROM Calendar AS c WHERE
c.work_day = 1 AND c.holiday = 0 AND c.calendar_date < [a.Ship
Date] AND c.calendar_date >= DATEADD("d",-(([a.Lead Time Days]
* 2) + 14),[a.Ship Date]) AND [a.Lead Time Days] = (SELECT
COUNT(* ) FROM Calendar c2 WHERE c2.calendar_date < [a.Ship
Date] AND c2.calendar_date >= .calendar_date AND c2.work_day
= 1 AND c.holiday = 0))

In other words I substituted a subquery for your DateAdd
expression. The value of Start Date is now determined by the
subquery.

All that is needed for your query to work now is the calendar
table.

I cannot see what is happening on your computer. Open in
design view the query that gives the syntax error. Switch to
SQL view and copy the full SQL statement and paste it into a
newsgroup message. The SQL I furnished must be edited and your
real field names and table name substituted for the ones I
used. I am guessing your real table name is not Table1 and it
is not alaised as a.

 

[Guest]

thank you once again

I didn't realise I had to import this bit of code in - where should it go

SELECT a.Product_Nbr,
a.[Ship Date],
a.[Lead Time Days],
(SELECT c.calendar_date
FROM Calendar AS c
WHERE c.work_day = 1
AND c.holiday = 0
AND c.calendar_date < [a.Ship Date]
AND c.calendar_date >= DATEADD("d",-(([a.Lead
Time Days] * 2) + 14),[a.Ship Date])
AND [a.LEad Time Days] = (SELECT COUNT(* )
FROM Calendar c2
WHERE
c2.calendar_date < [a.Ship Date]
AND
c2.calendar_date >= c.calendar_date
AND c2.work_day
= 1
AND c.holiday =
0)) AS [Start Date]
FROM Table1 AS a;

Thanks

(e-mail address removed)>,
(e-mail address removed) says...

Thank you for your code but when I copied the code into the query I get a
syntax error. Can you see where it is?

I am so grateful for your help. As soon as I get this code finished I can
set up the database and go on my maternity leave happy knowing that it all
works.

Thank you

Thank you once again.

I have imported the Calendar table, the queries and the module which is now
called Module 2 as I already have Module 1.

I am not an expert at Access and am in above my head.

Where would I put the coding that you sent me - SELECT a.Product_Nbr???

And how does it work with the original code I had in the query

START DATE: DateAdd("d",-[LEAD TIME DAYS],[SHIP DATE])

Thank you.


You never furnished a query; you merely furnished a column from
query design view.

Where you see: START DATE: DateAdd("d",-[LEAD TIME DAYS],[SHIP
DATE])

Substitute this: (it is all one line)
Start Date: (SELECT c.calendar_date FROM Calendar AS c WHERE
c.work_day = 1 AND c.holiday = 0 AND c.calendar_date < [a.Ship
Date] AND c.calendar_date >= DATEADD("d",-(([a.Lead Time Days]
* 2) + 14),[a.Ship Date]) AND [a.Lead Time Days] = (SELECT
COUNT(* ) FROM Calendar c2 WHERE c2.calendar_date < [a.Ship
Date] AND c2.calendar_date >= .calendar_date AND c2.work_day
= 1 AND c.holiday = 0))

In other words I substituted a subquery for your DateAdd
expression. The value of Start Date is now determined by the
subquery.

All that is needed for your query to work now is the calendar
table.

I cannot see what is happening on your computer. Open in
design view the query that gives the syntax error. Switch to
SQL view and copy the full SQL statement and paste it into a
newsgroup message. The SQL I furnished must be edited and your
real field names and table name substituted for the ones I
used. I am guessing your real table name is not Table1 and it
is not alaised as a.

 

[John W. Vinson]

thank you once again

I didn't realise I had to import this bit of code in - where should it go

SELECT a.Product_Nbr,
a.[Ship Date],
a.[Lead Time Days],
(SELECT c.calendar_date
FROM Calendar AS c
WHERE c.work_day = 1
AND c.holiday = 0
AND c.calendar_date < [a.Ship Date]
AND c.calendar_date >= DATEADD("d",-(([a.Lead
Time Days] * 2) + 14),[a.Ship Date])
AND [a.LEad Time Days] = (SELECT COUNT(* )
FROM Calendar c2
WHERE
c2.calendar_date < [a.Ship Date]
AND
c2.calendar_date >= c.calendar_date
AND c2.work_day
= 1
AND c.holiday =
0)) AS [Start Date]
FROM Table1 AS a;

This is not VBA code; it is a Query, in SQL (the language of queries).

Edit it (if necessary) to use your own table and field names. Create a new
Query in the query designer, without adding ANY tables; on the Menu select
View... SQL. You'll see a big white text window with just

SELECT;

in the corner. Select it and paste this query in its place.

One note: there's a couple of minor typos - [a.Ship Date] and [a.LEad Time
Days] should be a.[Ship Date] and a.[Lead Time Days] respectively.

John W. Vinson [MVP]