[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Adding weekdays to a date in ms access query

Posted on 2011-10-26
17
Medium Priority
?
565 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:gleverator
  • 6
  • 4
  • 4
  • +1
17 Comments
 
LVL 1

Expert Comment

by:upsfa
ID: 37034282
DateAdd("d", [integerField], [dateField])
0
 
LVL 1

Expert Comment

by:upsfa
ID: 37034313
I missed the weekday part.  This might help
http://www.tek-tips.com/faqs.cfm?fid=261
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 37034452
Here is an accurate method:

Add number of working days to date

/gustav
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:gleverator
ID: 37034863
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
 
LVL 2

Accepted Solution

by:
jdc1944 earned 375 total points
ID: 37036487
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
 
LVL 2

Expert Comment

by:jdc1944
ID: 37036493
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
 
LVL 2

Expert Comment

by:jdc1944
ID: 37036502
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 37036787
> .. 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
 

Author Comment

by:gleverator
ID: 37037476
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
 
LVL 1

Expert Comment

by:upsfa
ID: 37037612
Convert the text to a date.

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

0
 
LVL 2

Expert Comment

by:jdc1944
ID: 37037624
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 37037693
> 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
 
LVL 1

Expert Comment

by:upsfa
ID: 37037856
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 37037881
The default date format only influences the display of the value, not the value itself.

So something else must be going on.

/gustav
0
 
LVL 1

Expert Comment

by:upsfa
ID: 37037909
Would need to see the whole query to determin why it's failing.  Could be a data type mismatch in another field.
0
 
LVL 1

Expert Comment

by:upsfa
ID: 37038035
Did you use the tbllHolidays?  If so, are you sure the ObservedDate field you created was a date field?
0
 

Author Closing Comment

by:gleverator
ID: 37253042
had to modify it for my use
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question