DateAdd Working Day Query

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

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200801/1

0
hobbit2612
1/28/2008 7:30:02 PM
access.queries 6343 articles. 1 followers. Follow

7 Replies
3507 Views

Similar Articles

[PageSpeed] 50

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" <u27332@uwe> wrote in message 
news:7ee5e629d79ec@uwe...
> 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
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200801/1
> 


0
John
1/28/2008 7:58:19 PM
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

John Spencer wrote:
>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

-- 
Message posted via http://www.accessmonster.com

0
hobbit2612
1/28/2008 8:15:18 PM
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

hobbit2612 wrote:
>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

-- 
Message posted via http://www.accessmonster.com

0
hobbit2612
1/29/2008 6:07:34 PM
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

hobbit2612 wrote:
>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

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200801/1

0
hobbit2612
1/29/2008 6:08:47 PM
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

hobbit2612 wrote:
>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

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200801/1

0
hobbit2612
1/29/2008 6:08:48 PM
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
..

"hobbit2612 via AccessMonster.com" <u27332@uwe> wrote in message 
news:7ef1c354ca827@uwe...
> 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
>
> hobbit2612 wrote:
>>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
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200801/1
> 


0
John
1/29/2008 6:52:08 PM
Well I was grateful, the problems with the website also helped to show that.

Kind regards

Chris

John Spencer wrote:
>WOW! Three thank you messages for one postng ;-).
>
>> John,
>>
>[quoted text clipped - 42 lines]
>>>>>
>>>>> Many thanks

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200801/1

0
hobbit2612
1/29/2008 7:04:10 PM
Reply:

Similar Artilces:

Gantt bar durations are a day short
Pulliing my hair out over this one. I have a 700+ line schedule and somehow I've not only managed to loose the full critical path but more annoyingly the gannt bars now show durations a day shorted than they are entered - the actual start/finish dates are okay (as are depenedncies bewteen tasks). I.e. a one day task has a barely perceptiple duration in the gantt dar and a two day tasks shows a 1 day duration. Where there is a depenency, the dependency is illustrated as if there it is fs+1d. I have zoomed right in to show only days and reset the gantt wizard I don't know h...

Sync Center stopped working
Does anyone know a way to repair Mictosoft Sync Center in Vista Home Premium? I've never used it, and don't envision ever needing it, but I'm getting periodic alerts that it's stopped working The pop up during boot-up, and sometimes for no apparent reason that I can figure out. SFC /Scannow reveals no problems, and a Systems Restore would undo so much that it'd be more trouble than it's worth at this point. Failing a repair method, a way to either uninstall the blasted thing or just turn it off would be nice. Thanx: Buddha ...

Day names in Calendar weekly view cells.
How does one display the days of the week, as well as the dates, in the title bars of the date cells in Weekly View of Outlook 2003 Calendar? It seems that an 'alternate calendar' of weekday names would do the trick if there's no simple setting that I've missed. Thanks, -- Michael H ...

Set Mailbox Retention for # of days
I have a new Excahnge 2003 Enterprise Server where I have a special message store for a group of users that do not have outside e-mail access. I want to set the store so that all messages over 30 days are automatcially deleted, much like the setting for retenetion you can have for the Public Folders. I thought I could do this with store properties, but cannot find the setting. Any help setting retention time in days for the entire store would be appreciated. Retention settings on Store will let you keep deleted messages/mailboxes for x number of days. These settings are on mailbox St...

auto numbering in a query
Hi all, I need to build a query that have in a field auto numbering and in a certain formating for example: 0001 0002 ....... 0012 ....... is it posible? Is this to produce data that will be viewed in a report, by any chance? "thread" <yaniv.dg@gmail.com> wrote in message news:1175794888.467011.108490@w1g2000hsg.googlegroups.com... > Hi all, > I need to build a query that have in a field auto numbering and in a > certain formating > for example: > 0001 > 0002 > ...... > 0012 > ...... > is it posible? > On Apr 5, 2:42 pm, "BruceM&q...

MS Query not installed for New Database Query
I have Excel 2000 SP3 & Windows XP Home When I go to Data | Get External Data | Run Saved Query I can imort data from an Access database into an Excel worksheet. Wheh I go to Data | Get External Data | New Database Query after a delay a message appears to tell me that MS Query is not installed. MSQRY32.EXE is in the Office folder & I have reinstalled it by renaming it to .old & running the Add/Remove facility in the Office installer to no avail. I have searched the MSKB & found an article at: http://support.microsoft.com/default.aspx?scid=kb;en-us;179686 that refers to...

Calculating Time/Days worked
Hope you will bare with me while I explain. Once again I am working with a sheet, or series of sheets that hel employees calculate their time for days and hours worked. I decided against the protection button, as it was more of a proble with employees unchecking it. So, I am left with one last functio before it is complete. Let me remind you wonderful people I am new to excel and this is m first real project. Ok, my last 'function' I would like is this... Paydays are calculated from the 11th of each month to the 25th, the again from the 26th of each month to 10th of the the next month...

MS paramater Query on ODBC Table
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C38C4D.5F11AC30 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is it possible to and if so how can I run a parameter driven MS query on = an ODBC database table? I want to be able to enter some info into a cell on a spreadsheet and = have a query run to pull back other info relating to my entry. --=20 Regards Dean=20 dkso@ntlworld.com=20 http://homepage.ntlworld.com/dkso ------=_NextPart_000_0008_01C38C4D.5F11AC30 Content-Type: text/html; charset="iso-885...

text conversion to number on select query
Hi, In a Select Query I'm joining 2 tables by Item ID (unique value, similar to Social Security Number) but 1 table created by IT has Item ID as a "number" value and the other table has it as a "text" value. How can I in a Select Query, create a formula that can either have the text as a number value and vice versa so i can link the 2 without getting "type mismatch in expression." I think I can use Cdbl Value or something like that in the formula but not sure. Thanks! "inspirz" wrote:subed going to jail > Hi, > ...

selecting a query from a combo box
HELP! Need to design a DB for my boss and I am lost! I have a database which lists students who have went on exchange over the last 17 years to over 20 countries and numerous institutions.. I have set up 3 queries/reports using parameters so the user can enter: 1) the year 2) the country or 3) the insitution. Now the problem is the insitution query as the name of the institution can get spelt various ways so I would prefer the user to select the institution from the drop down box which they use to enter the data into the table under the field "institution". My f...

Query is making a nuts
Hi Using Access 2007 I have a table and two of the fields (Status and OrigStatus) have, among other possible entries, the words "Member," "Customer" or "Request." I am trying to create a query that only displays records which do NOT have "Member," "Customer" or "Request" in either field. But it won't work! I have tried putting each word in its own Criteria column in each field using syntax such as: <>"Member" with no success. I have also tried <>"Member" OR "Customer" OR "...

The code below worked for a few days and just totally stopped working. What can I do to make sure it continues to work? Thanks!
The code below worked for a few days and just totally stopped working. What can I do to make sure it continues to work? Thanks! ----- Private Sub Worksheet_Change(ByVal Target As Range) Dim C As Range, D As Range Set D = Intersect(Range("A:A"), Target) If D Is Nothing Then Exit Sub For Each C In D On Error Resume Next Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]" ' - For Column E Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]" ' - For Column F Target.Offset(0, 9).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,I.O.! R2C1:R5...

Use query for Row Source?
I attempted to use a combo-box to look-up records on a form. The RowSource query that is generated by the wizard included 2 data fields. I wanted to show only a distinct list of the second, non-key data element (col1). When I changed the RowSource query to SELECT distinct Col1, the combo-box displays nothing. I then created a stored query with the SELECT distinct Col1. They query runs correctly, but when I put in in the RowSource, it displays nothing. Any clue what I could be doing wrong? On Fri, 26 Oct 2007 12:17:03 -0700, JHC wrote: > I attempted to use a combo-box to look-u...

Working with a worksheet that is not the active worksheet
How can I make this code work on my worksheet named February, when it is not the active worksheet? Sub BlankWeeks() ' ' Macro2 Macro ' ' If Range("C184").Value = "" Then Rows("184:228").Hidden = True End If If Range("C184").Value <> "" Then Rows("184:228").Hidden = False End If If Range("C229").Value = "" Then Rows("229:273").Hidden = True End If If Range("C229").Value <> "" The...

Add working hours to date field
We offer computer support based on a certain response time in hours. I want to have a date field, on the case form, to automatically set x WORKING hours ahead, usually 8 hours. The actually hour value will be pulled from a contract. Does anyone know if this is possible or am I shooting way too high? ...

CMap query
Hi, I have a text file and i read the complete file and build a FILELIST - Files & GROUPLIST - groups like odbc,,jet40, jet35 The Group list in the file consists of values like version | odbc | 430 version | Jet40 | 430 version | Jet35 | 430 version | oledb | 430 version | odbc | 440 version | Jet40 | 440 version | Jet35 | 440 version | oledb | 440 The File List consists of values like FILE | odbc | <COMPLETE path of odbc file like odbc32.dll> FILE | odbc | <COMPLETE path of odbc file like odbcji32.dll> FILE | oledb | <COMPLETE path of oledb dll1> FILE | ole...

How does "leave a copy" work?
Hello group. Outlook and Outlook Express both have a "leave a copy" feature, and I'm curious how they know which emails to download from the server, and which one not to download. Do they have a database somewhere in the disk for which they use to compare with the list of emails in the mail server or something? More precisely, I'm wondering why Outlook/OE sometimes loses this information somehow and redownloads everything again, and then I have to delete them since I already have them. -- Mvh / Best regards, Jack, Copenhagen The email address is for real :) Jack...

Short cuts & Dictionary not working in Outlook 2003...
Hi I am having a problem in my Outlook 2003. The problem is the auto dictionary is not working. i.e. it is not whowing underline (Red and Green) to any of the errors. Moreove the shortcuts like Shift+F7, CTL+Shift+> (for font size increment)and CTL+Shift+< (for font size reduction)are also not working. Though F7 is working. The problem started when i uninstalled my MS Word 2003 and installed Word 2000. The solve the problem i have tried to install the MS Word 2003 again. Still it was not working. So i have unstalled the whole Office and installed full office 2003. But still I am not ab...

dafault value for date not working with Date() but Now() is workin
Got a table with existing data, with simple date field on a form that I want to default to todays date. I have tried inputting the Date() into the table default value for the date, but I get an error that says "Unknown function in 'Date' in validation expression or default value on 'Data.date'. Data is the table name. If I enter Now() into the default value for date, it works just fine. But, the reports that are generated, don't show the records with the date and time in them. I can go back to the table and manually delete the time from those records...

Time interval at which MS CRM E-mail Router Service works
Hi, Can anyone please let me know the frequency (or time interval) at which Microsoft CRM E-mail Router Service works or processes requests from the mailbox? Thanks in advance and help appreciated. Bharat Chawla ...

query sort on date
I've got a table where applicant data is entered (via a form) everytime an application is submitted. Each time a person submits an application it is enteed as a new submission, thus I can have duplicate records on a person, except for the submission date and maybe the address if it changed but it is still a new record. My questions are how can I query the table data to only show the most recent applications without having duplicate records on a person? Another field in the table is the SSN. The other question pertaining to the same query is say that an application was submit...

update query help 08-26-10
ear All i have following DDL and sample data [sql 2000] create table main_tab (t_id varchar(10),fname varchar(30), lname varchar(30), gender char(1),status smallint) create table im_tab (t_id varchar(10),flag char(1)) insert into main_tab values('a0101','Ali','Hussain','M',null) insert into main_tab values('a0102','Noor','Inam',null) insert into main_tab values('a0103','Siddique','Hussain','M',null) insert into main_tab values('a0104','Farooq','Ali','M'...

how do I work out the VAT on a multpl of columns? #3
Grate!!!! - many thanks for all your help : -- chriss ----------------------------------------------------------------------- chrissy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1584 View this thread: http://www.excelforum.com/showthread.php?threadid=27337 ...

"File: send to mail recipient" not working
When users (on WinXP) select file, "send to mail recipient", no response from Outlook 2002. This occurs either when Outlook is open or closed. Problem happens when rt. clicking on .doc, .xls, .pdf's....all files. Any ideas?? Thanks!! ...

And Or in a Parameter Query
How do you make use of AND or OR in a Parameter Query? Entering one value works fine, but using and/or retrieves an empty string. Any ideas? A parameter query uses and /or just like any other query as far as I know. You can't use and / or in the parameter section because you can't have more then one data type assigned to a field. That is the point behind a parameter query: Acccess is unable to determine what type of data is being stored/returned, Currency, Date, whatever, so you use the parameter to force it to work with that data one consistent way. However, just...