Adding weekdays to a date in ms access query

I have a table with a date field and integer field

The integer field resembles the amount of weekdays

The goal is to add the integer field to the date field to get a new date

For example:
DateField: 10/26/11
IntegerField: 5
Output should be datefield plus integerfield (where we are adding weekdays only) so output should be 11/2/11

What would be the query to add the weedays to the date field?
gleveratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

upsfaCommented:
DateAdd("d", [integerField], [dateField])
0
upsfaCommented:
I missed the weekday part.  This might help
http://www.tek-tips.com/faqs.cfm?fid=261
0
Gustav BrockCIOCommented:
Here is an accurate method:

Add number of working days to date

/gustav
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

gleveratorAuthor Commented:
both solutions on this page look like they car work, however i cant get see the original table referenced so i cant determine how to fit my table structure.
http://www.experts-exchange.com/Microsoft/Applications/Q_26982000.html#a35482567

My table is called "PurchaseOrders" and my date field is called "PODate" and the integer field is called "Integer1"

Can someone modify capricorn1 solution to work with my data?
0
jdc1944Commented:
Im presuming you want to show this new date in a query.  If so you need to make a module and use the following code if you are ignoring public holidays or other dates that should not be counted as a businesses day excluding weekends  
Public Function GetDueDate(dDate As Date, Span As Integer) As Date
Dim j As Integer, i As Integer, dtStart
dtStart = dDate
For j = 1 To Span
    dtStart = dtStart + 1
    Do While Weekday(dtStart) = 1 Or Weekday(dtStart) = 7
        dtStart = dtStart + 1
        i = i + 1
    Loop
Next
GetDueDate = DateAdd("d", (Span + i), dDate)

End Function

Open in new window

If you do want to use these type of holidays, create a table called tblHolidays and store the holiday dates in a field called 'observedDate' and use this code  
Public Function GetDueDate(dDate As Date, Span As Integer) As Date
Dim j As Integer, i As Integer, dtStart
dtStart = dDate
For j = 1 To Span
    dtStart = dtStart + 1
    Do While Weekday(dtStart) = 1 Or Weekday(dtStart) = 7 _
        Or Not IsNull(DLookup("observedDate", "tblHolidays", "observedDate=#" _
     & dtStart & "#"))
        dtStart = dtStart + 1
        i = i + 1
    Loop
Next
GetDueDate = DateAdd("d", (Span + i), dDate)

End Function

Open in new window


Then to get that code to work you need to add the following field to a query
 
NEWDATE: getDueDate([PODate],[integer1])

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jdc1944Commented:
the new date can also be shown in unbound field on a form.  just add

=getDueDate([PODate],[Integer1])

to the control source property of the field you want to show it in.
0
jdc1944Commented:
Sorry forgot to add, if you do want it to be shown on a form only instead of being stored in a query, you will still need to create a module as described above.
0
Gustav BrockCIOCommented:
> .. determine how to fit my table structure.

As noted, you won't do that.

Create a new module, copy and paste the function into this, compile and save the module using a name different from the function, say, basUtils.

Use this expression in a column in a query where you have pulled in your table:

DeliveryDate: ISO_WorkdayAdd([PODate], [integer1])

/gustav
0
gleveratorAuthor Commented:
jdc1944 - you have the solution and i will award you.

Just quick question.

When i put this in my query field:
Expr1: getduedate([podate],[inventory].[integer1])

It works perfectly.

But if i try to put a criteria like this:
<date()

I get this error:
Data type mismatch in criteria expression.

Why do i get that error?
0
upsfaCommented:
Convert the text to a date.

Expr1: Cdate(getduedate([podate],[inventory].[integer1]))

0
jdc1944Commented:
Hum,  I think you need to check the data types in your tables as when i put that in the criteria i get no error.  

I have 'PODate' as a Date/Time and the integer1 as a number field.
0
Gustav BrockCIOCommented:
> I get this error:
> Data type mismatch in criteria expression.

That is not possible as your function returns a date/time value only, thus wrapping the output in CDate makes no sense.

Same goes for my function and expression above.

Something else must be going on.

/gustav
0
upsfaCommented:
I used the version without the holiday table and it works for me using <now() and <date().  I don't think it should matter, but what about the default date format on your PC?

0
Gustav BrockCIOCommented:
The default date format only influences the display of the value, not the value itself.

So something else must be going on.

/gustav
0
upsfaCommented:
Would need to see the whole query to determin why it's failing.  Could be a data type mismatch in another field.
0
upsfaCommented:
Did you use the tbllHolidays?  If so, are you sure the ObservedDate field you created was a date field?
0
gleveratorAuthor Commented:
had to modify it for my use
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.