DateAdd Working Day Query

[hobbit2612 via AccessMonster.com]

Hi,

I wonder if someone could help me please.

I'm trying to write an expression (see code below) within a query where I
calculate 15 working days from a a given date, in this case the 'Date a
report was sent'.

DateAdd(“wwâ€,15,[Date Report Sent]-DateAdd([Date Report Sent],1)*2-IIf
(Weekday([Date Report Sent],1)=7,IIf(Weekday([Date Report Sent],1)=7,0,1),IIf
(Weekday([Date Report Sent],1)=7,-1,0)))

But when I add it to my query I keep getting this message: 'The expression
you entered has a function containing the wrong number of arguments.'

I'm assuming, maybe wrongly, it's because there are not enough parentheses,
but for the life of me I can't see where.

Could someone give me a helping hand on this.

Many thanks

 

[John Spencer]

Your basic expression would be
DateAdd("d",21, [Date Report Sent]) which adds 3 weeks (assuming M to F as
workdays)

So if the date is Sunday you need to add one more Day and if it is Saturday
you need to add two more days
IIF(Weekday =1,1, IIF(Weekday=7,2,0))

DateAdd("d",21 + IIF(Weekday =1,1, IIF(Weekday=7,2,0)), [Date Report Sent])


Your error message was caused by
DateAdd([Date Report Sent],1) <<< Wrong syntax

Also note that "ww" does not add weekdays. DateAdd will treat ww as if it
were w and add weeks.

As far as the overall expression goes, I can't quite figure out what you are
attempting to do. If I've misunderstood, please try to explain in words
what you are trying to accomplish.
-- I want to add 15 working days to the date
-- Working days are M to F
-- If the due date falls on a weekend, then I want the due date to fall on
the following Monday
(or) If the due date falls on Saturday, I want the date to be on the
preceding Friday and if Sunday on the following Monday.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[hobbit2612 via AccessMonster.com]

John,

Thanks for your reply.

I am fairly new to access, particularly to expressions as complicated as this,
you will no doubt be able to tell.

I had used a similar query to work out the difference between dates which
worked, but in my naivety and through my lack of experience I thought that I
could use the same, with some changes.

I'm not very good at VB so what I'm looking for is an expression that I can
use in an query that calculates 15 working days from the 'Date Report sent'
field and that's basically it.

Thanks for your help and time

Regards

Chris

Your basic expression would be
DateAdd("d",21, [Date Report Sent]) which adds 3 weeks (assuming M to F as
workdays)

So if the date is Sunday you need to add one more Day and if it is Saturday
you need to add two more days
IIF(Weekday =1,1, IIF(Weekday=7,2,0))

DateAdd("d",21 + IIF(Weekday =1,1, IIF(Weekday=7,2,0)), [Date Report Sent])

Your error message was caused by
DateAdd([Date Report Sent],1) <<< Wrong syntax

Also note that "ww" does not add weekdays. DateAdd will treat ww as if it
were w and add weeks.

As far as the overall expression goes, I can't quite figure out what you are
attempting to do. If I've misunderstood, please try to explain in words
what you are trying to accomplish.
-- I want to add 15 working days to the date
-- Working days are M to F
-- If the due date falls on a weekend, then I want the due date to fall on
the following Monday
(or) If the due date falls on Saturday, I want the date to be on the
preceding Friday and if Sunday on the following Monday.

Hi,

[quoted text clipped - 19 lines]

Many thanks

 

[hobbit2612 via AccessMonster.com]

John,

Just a post message note.

Apologies I hadn't read your message properly that you sent yesterday, lack
of sleep!

Having looked at it this morning, I've been able to use the code that you
placed in the message and it works a treat!

Thanks very much once again for your time and help.

Chris

John,

Thanks for your reply.

I am fairly new to access, particularly to expressions as complicated as this,
you will no doubt be able to tell.

I had used a similar query to work out the difference between dates which
worked, but in my naivety and through my lack of experience I thought that I
could use the same, with some changes.

I'm not very good at VB so what I'm looking for is an expression that I can
use in an query that calculates 15 working days from the 'Date Report sent'
field and that's basically it.

Thanks for your help and time

Regards

Chris

Your basic expression would be
DateAdd("d",21, [Date Report Sent]) which adds 3 weeks (assuming M to F as

[quoted text clipped - 26 lines]

 

[hobbit2612 via AccessMonster.com]

John,

Just a post message note.

Apologies I hadn't read your message properly that you sent yesterday, lack
of sleep!

Having looked at it this morning, I've been able to use the code that you
placed in the message and it works a treat!

Thanks very much once again for your time and help.

Chris

John,

Thanks for your reply.

I am fairly new to access, particularly to expressions as complicated as this,
you will no doubt be able to tell.

I had used a similar query to work out the difference between dates which
worked, but in my naivety and through my lack of experience I thought that I
could use the same, with some changes.

I'm not very good at VB so what I'm looking for is an expression that I can
use in an query that calculates 15 working days from the 'Date Report sent'
field and that's basically it.

Thanks for your help and time

Regards

Chris

Your basic expression would be
DateAdd("d",21, [Date Report Sent]) which adds 3 weeks (assuming M to F as

[quoted text clipped - 26 lines]

 

[hobbit2612 via AccessMonster.com]

John,

Just a post message note.

Apologies I hadn't read your message properly that you sent yesterday, lack
of sleep!

Having looked at it this morning, I've been able to use the code that you
placed in the message and it works a treat!

Thanks very much once again for your time and help.

Chris

John,

Thanks for your reply.

I am fairly new to access, particularly to expressions as complicated as this,
you will no doubt be able to tell.

I had used a similar query to work out the difference between dates which
worked, but in my naivety and through my lack of experience I thought that I
could use the same, with some changes.

I'm not very good at VB so what I'm looking for is an expression that I can
use in an query that calculates 15 working days from the 'Date Report sent'
field and that's basically it.

Thanks for your help and time

Regards

Chris

Your basic expression would be
DateAdd("d",21, [Date Report Sent]) which adds 3 weeks (assuming M to F as

[quoted text clipped - 26 lines]

 

[John Spencer]

WOW! Three thank you messages for one postng ;-).

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John,

Just a post message note.

Apologies I hadn't read your message properly that you sent yesterday,
lack
of sleep!

Having looked at it this morning, I've been able to use the code that you
placed in the message and it works a treat!

Thanks very much once again for your time and help.

Chris

John,

Thanks for your reply.

I am fairly new to access, particularly to expressions as complicated as
this,
you will no doubt be able to tell.

I had used a similar query to work out the difference between dates which
worked, but in my naivety and through my lack of experience I thought that
I
could use the same, with some changes.

I'm not very good at VB so what I'm looking for is an expression that I
can
use in an query that calculates 15 working days from the 'Date Report
sent'
field and that's basically it.

Thanks for your help and time

Regards

Chris

Your basic expression would be
DateAdd("d",21, [Date Report Sent]) which adds 3 weeks (assuming M to F
as

[quoted text clipped - 26 lines]

Many thanks

 

[hobbit2612 via AccessMonster.com]

Well I was grateful, the problems with the website also helped to show that.

Kind regards

Chris

WOW! Three thank you messages for one postng ;-).

John,

[quoted text clipped - 42 lines]