Expiration in Access

[jealindgar]

First, I would like to apologize for my so poor English.

Long ago I promised a friend (lawyer), create her a database for your office. I created the database, I have deleted it, I've re-created it, and so, for over ten years.
I think it's time to finish it , but always manages to defeat a major issue for the database: expiry.

this is my problem and I need your help or advice:

I am including a start date, and within a few days (here the difference, not a date, but a few days).
Then I tell my base that since the start date, adding the days of term, it should include (or not), Saturday, Sunday and / or holidays.

example:
* Date notice (F. Home): 17/06/2011
* Day deadline for submissions: 10 days
* In a field that does not include me but automatically, should read: 27/06/2011 (result of adding 10 days to 17/06/2011)

But this is not quite right

* Suppose I should not include Saturdays or Sundays and therefore should be: 07/01/2011

* So far so good, but June 24th is a holiday, so the correct date, therefore, is: 07/04/2011
* But what happens if I include Saturdays and Sundays? The correct date would be: 30/06/2011 (excluding Sundays and on 24 June)
* But sometimes I include Monday to Sunday and holidays.
* The month of August is also sometimes included or not (a mess, what I said).

And every maturity is customized, because if a municipality is a public holiday, another may not. I'm really hands tied in this matter


thanx a million!!!

 

[GinaWhipp]

Have a look at...

http://www.databasejournal.com/feat...ess-2000-How-Tos-Calculating-Working-Days.htm

OR

http://access.mvps.org/access/datetime/date0006.htm

 

[jealindgar]

I´ll take a look, thanx

 

[GinaWhipp]

We'll be waiting if you have questions...

 

[jealindgar]

It was difficult to understand both sites because I'm not sure that's what I want (language doesn´t help me much).
I got another example easier for me, ´cause I don´t know much about code.

Let´s see...

Expiration days [I indicate a number]
Starting date [I indicate a date]
Ending date [Access proposes a date]

Example:
I write 10 days in field "Expiration days" and 07/09/2011 in field "Starting date", and Access 'says' then "Ending date": 07/19/2011. I agree, but...
Saturdays and Sundays aren´t working, then check the boxes Saturday and Sunday and new date is 07/25/2011

code is:

Private Sub Texto6_AfterUpdate()
Dim Contador1 As Integer
Dim FechaPlazo1 As Date
Dim AccSab1 As Integer
Dim AccDom1 As Integer
Dim ComprobarDia As Date
Dim Y As Integer
Dim EsFiesta As Date
Dim EsFiesta1 As Date

Y = Texto6
Contador1 = 0

Inicio:
ComprobarDia = Texto8 + Contador1

EsFiesta = Nz(DLookup("Festivo", "DiasFestivos", "Festivo = #" & (Format(ComprobarDia, "mm dd yyyy")) & "# "), #1/1/1900#)

If Weekday(Me.Texto8 + Contador1, vbMonday) = 6 And Me.Sab = -1 Then AccSab1 = AccSab1 + 1: Y = Y + 1
If Weekday(Me.Texto8 + Contador1, vbMonday) = 7 And Me.Dom = -1 Then AccDom1 = AccDom1 + 1: Y = Y + 1
If EsFiesta > #1/1/1900# And Weekday(Me.Texto8 + Contador1, vbMonday) = 6 And Me.Sab = 0 Then DiasFestivos = DiasFestivos + 1: Y = Y + 1
If EsFiesta > #1/1/1900# And Weekday(Me.Texto8 + Contador1, vbMonday) = 7 And Me.Dom = 0 Then DiasFestivos = DiasFestivos + 1: Y = Y + 1
If ComprobarDia <> Texto8 And EsFiesta > #1/1/1900# And Weekday(Me.Texto8 + Contador1, vbMonday) <> 6 And Weekday(Me.Texto8 + Contador1, vbMonday) <> 7 Then DiasFestivos = DiasFestivos + 1: Y = Y + 1

Contador1 = Contador1 + 1
If Contador1 <= Y Then GoTo Inicio

FechaPlazo1 = Me.Texto8 + Me.Texto6 + AccSab1 + AccDom1 + DiasFestivos
If Weekday(FechaPlazo1, vbMonday) = 6 And Me.Sab = -1 And Me.Dom = 0 Then FechaPlazo1 = FechaPlazo1 + 1
If Weekday(FechaPlazo1, vbMonday) = 6 And Me.Sab = -1 And Me.Dom = -1 Then FechaPlazo1 = FechaPlazo1 + 2
If Weekday(FechaPlazo1, vbMonday) = 7 And Me.Dom = -1 Then FechaPlazo1 = FechaPlazo1 + 1
If Weekday(FechaPlazo1, vbMonday) = 6 And Me.Sab = 0 And DLookup("Festivo", "DiasFestivos", "Festivo = #" & ComprobarDia & "# ") And Me.Dom = 0 Then FechaPlazo1 = FechaPlazo1 + 1
If Weekday(FechaPlazo1, vbMonday) = 6 And Me.Sab = 0 And DLookup("Festivo", "DiasFestivos", "Festivo = #" & ComprobarDia & "# ") And Me.Dom = -1 Then FechaPlazo1 = FechaPlazo1 + 2
If Weekday(FechaPlazo1, vbMonday) = 7 And Me.Dom = 0 And DLookup("Festivo", "DiasFestivos", "Festivo = #" & ComprobarDia & "# ") Then FechaPlazo1 = FechaPlazo1 + 1
EsFiesta = Nz(DLookup("Festivo", "DiasFestivos", "Festivo = #" & (Format(FechaPlazo1, "mm dd yyyy")) & "# "), #1/1/1900#)
If EsFiesta > #1/1/1900# And Weekday(FechaPlazo1, vbMonday) <> 6 And Weekday(FechaPlazo1, vbMonday) <> 7 Then FechaPlazo1 = FechaPlazo1 + 1
If EsFiesta > #1/1/1900# And Weekday(FechaPlazo1, vbMonday) = 6 And Me.Sab = -1 And Me.Dom = 0 Then FechaPlazo1 = FechaPlazo1 + 1
If EsFiesta > #1/1/1900# And Weekday(FechaPlazo1, vbMonday) = 6 And Me.Sab = -1 And Me.Dom = -1 Then FechaPlazo1 = FechaPlazo1 + 2
If EsFiesta > #1/1/1900# And Weekday(FechaPlazo1, vbMonday) = 7 And Me.Dom = 0 Then FechaPlazo1 = FechaPlazo1 + 1
EsFiesta = Nz(DLookup("Festivo", "DiasFestivos", "Festivo = #" & (Format(FechaPlazo1, "mm dd yyyy")) & "# "), #1/1/1900#)
If EsFiesta > #1/1/1900# Then
AveriguaFestivo:
EsFiesta1 = Nz(DLookup("Festivo", "DiasFestivos", "Festivo = #" & (Format(FechaPlazo1 + 1, "mm dd yyyy")) & "# "), #1/1/1900#)
If EsFiesta1 > #1/1/1900# Then FechaPlazo1 = FechaPlazo1 + 1: GoTo AveriguaFestivo

End If

Me.Texto11 = FechaPlazo1

End Sub

I also got the rest of the holidays

is easier for me
but... What if I want to give my ending date? How I can edit that field? as well as Saturdays and Sundays, I am also interested in August

thanks!

 

[GinaWhipp]

I am on my way out the door... so if someone else doesn't pass by while I'm gone I'll look at it later tonight or tomorrow.

 

[jealindgar]

Hi again!
Finally I got another solution much easier. But I'm still stuck with the problem in August. I know how to include or do not include Saturdays, Sundays and holidays, but not how to include or not include the entire month of August.
Any ideas?

thanks!

 

[GinaWhipp]

Well, ideas will come when you post the solution you got that works...

 

[jealindgar]

sorry
help of a friend

module1:
__________________________________________
Option Compare Database
Option Explicit

Public Function noSunday2 (ByVal StartingDate As Date, ByVal Days As Integer) As Date
Dim accountdays, i As Integer
Dim fTmp As Date
accountdays = 0
fTmp = startingdate


For i = 0 To Days
If Weekday(fTmp) = vbSunday Then
accountdays = accountdays + 1
End If
fTmp = fTmp + 1
Next i

noSunday2 = FIni + days + accountdays
End Function

Public Function notweekend (ByVal startingdate As Date, ByVal Days As Integer) As Date
Dim accountdays, i As Integer
Dim fTmp As Date
accountdays = 0
fTmp = startingdate

For i = 0 To days
If Weekday(fTmp) = vbSunday Then
accountdays = accountdays + 1
End If
If Weekday(fTmp) = vbSaturday Then
accountdays = accountdays + 1
End If
fTmp = fTmp + 1
Next i

notweekend = startingdate + Days + accountdays
End Function
__________________________________________

in the event by clicking from Monday to Friday:

__________________________________________
Dim vIni, vDias As Variant
vIni = Me.FNotificacion.Value
vDias = Me.DVto.Value

If IsNull(vIni) Then Exit Sub
If IsNull(vDias) Then Exit Sub

Me.FromMToF.Value = notweekend(vIni, vDias)

__________________________________________

in the event by clicking from Monday to Saturday:

__________________________________________

Dim vIni, vDias As Variant
vIni = Me.StartingDate.Value
vDias = Me.FromMToS.Value

If IsNull(vIni) Then Exit Sub
If IsNull(vDias) Then Exit Sub

Me.FromMToS.Value = noSunday2(vIni, vDias)
__________________________________________

etc.

 

[GinaWhipp]

This is proving to be a bit challenging as I have never tried to exclude a month. Do you always want to exclude August?

 

[jealindgar]

Yes... or not
In law there are civil and criminal matters. For civil matters should ignore the month of August (but not always). For criminal matters, we must add the month of August (but not always).
Therefore, I have to choose whether or not to add the entire month of August.

This is proving to be a bit challenging as I have never tried to exclude a month. Do you always want to exclude August?

 

[GinaWhipp]

Yeah, it figures... as I said a bit challenging as I never attempted to remove a month (or not). Okay let me think some more on this...

 

[jealindgar]

someone has offered me this help, but do not know how to apply.


fechaTmp = fechaTmp + 1
If month(fechaTmp) = 8 then
Do until month(fechaTmp) = 9
fechaTemp=fechaTmp + 1
Loop
End If

 

[GinaWhipp]

Hmmm, you can elect to include in the loop or not, never thought of that, wass trying to incorporate in existing code.

What you would need to do is create a Public Function using that code and then call it from your events on the form. However, you will still need a trigger with can be a Check Box or Option Group.

 

[jealindgar]

uf! sorry, that's like Chinese to me

 

[GinaWhipp]

Okay, where is the *friend* that wrote that first set of code for you? Perhaps they could do that for you?

 

[jealindgar]

this is his website, what happens is I don´t want more abusing of him. But it´s so difficult.

his example here: http://neckkito.eu5.org/index.php?option=com_content&view=article&id=92&Itemid=99

It's okay, I'll figure something out.

 

[GinaWhipp]

Can you upload the database?

 

[GinaWhipp]

Those links you provided do not work...

 

[jealindgar]

Those links you provided do not work...

I know. But I don´t know what happened. The same thing happened a few weeks ago and then ran.

My "complicate" database.
Note that I know almost nothing of access code, so it is normal that my database a little tinkering, because I have code here and there. I read a lot and I asked a lot too. It is also in Spanish and in Access 2007.
1,88 MB

Now, I was just working on it