Question DLookup and Date problems - please help

[stavros]

Hi all, this is my first Post and i have just inherited an Access Database that i am trying to get my head around and also update, so here goes!

I have a Table that show products and SLT's (in Days)
ie
Product SLT
Line 6
Circuit 42

One of the forms we use has various boxes that need filling in, some with dropdowns and this is what i am trying to do.

If the Product "Line" is selected from the list, in the Order Fulfillment date field todays date + 6 is put in e.g 19/12/2012, (i.e. Today is 11/12/12 + 6 = 19/12/2012). Same for if Circuit is selected, todays date + 42 and so on. but the added days have to be Week Working days (not include weekend).

Ontop of that i also have a Customer Required Date field thats required. What i want to do is if the date in this field is lower than the Order Fulfillment date then nothings changed, if the Customer Required Date is greater than the Order Fulfilment date then its replaced with this new date.
e.g Order Fullfilment Date is 19/12/12, Customer required date is 17/12/12 - nothing happens
Order Fullfilment Date is 19/12/12, Customer required date is 2/01/13 - the Fullfiment date is changed to 2/1/13

Hope you can help?

 

[John Big Booty]

Perhaps the discussion in this thread will help you.

 

[stavros]

Thanks for the thread but i think that all a bit too much for me to work out sorry.

what i have is a as follows:

Table called Product within that a coloum called Product and a column called SLT

Within one of the forms thats uses is a combo box called Producttxt (this lists the above products)

Using a text box for testing, i have tried to use various variations of the following to get what i need. =DLookUp("SLT","[Product]","[Product]=" & "[Producttxt]")

with/without "" & [] and i either get a blank field or #error

for now all i am trying to do is get the desired SLT result in the box from the product i select

 

[jdraw]

For Date adjustments (sums) you should research the DateAdd function.
see http://www.techonthenet.com/access/functions/date/dateadd.php

You should not name a Table and a field in that table with the same name (Product).

For DLookup see http://www.techonthenet.com/access/functions/domain/dlookup.php
Please post the related code.
What is SLT?

 

[NeutronFlux]

For getting workdays, DateAdd doesn't seem to work in my experience. Here's a bit of code I just made up. It doesn't take into account holidays though, as that would require quite a bit more work that the OP may or may not need. I only tested it a bit, but so far it seems to work. I'll leave it to stavros to test it more vigorously if s/he wants to use it.

Edit: Oops, forgot to include the code

Function AddWorkDays(myDate As Date, workdays As Integer) As Date
    Dim weeks As Integer
    Dim days As Integer
 
    weeks = Fix(workdays / 5)
    days = workdays Mod 5
 
    AddWorkDays = myDate + (weeks * 7) + days
 
    'Account for passing weekends on first week
    If (Weekday(myDate, vbMonday) < 6) And ((Weekday(myDate, vbMonday) + days) > 7) Then
        AddWorkDays = AddWorkDays + 2
    End If
 
    'If final date falls on a weekend, move date two days later
    If (Weekday(AddWorkDays, vbMonday) > 5) Then
        AddWorkDays = AddWorkDays + 2
    End If
End Function

 

[jdraw]

The standard Date relate functions do not deal with Working days. That is true. Holidays can vary by Country, so they are usually handled through Custom (user defined functions).

Here is a link that deals with iso workingdays, but as you said this may be too complex for the OP.
http://www.utteraccess.com/forum/lofiversion/index.php/t1959936.html

You could (perhaps but untested) see if the dates you were adding were for Saturday or Sunday, and if so, drop those and add a corresponding number of "weekdays". That approach still would not deal with Holidays.

There was another post on this sort of topic.
http://www.access-programmers.co.uk/forums/showthread.php?p=1163707

 

[stavros]

thank you all so much

cheers
Paul

 

[Pat Hartman]

I created a database with lots of date samples. I updated it today to include an additional business days calculation. Look on the Business days tab at the second calculation.