Posts by Newt

Re: IF Formula-Function: Multiple Entries

Excellent!
That last code works great.
Thanks for the help and the super quick responses!
You rock!
K-

Hi Guys,
I need help fixing a code on an excel spreedsheet that when inputed calculates the right taxes.
The current forumla looks like this:

=IF(E6="xx",D6,IF(+E6="i0",0,IF(+E6="GJ",ROUND(D6/1.07*0.07,2),IF(+E6="ij",ROUND(D6/1.15*0.07,2),IF(E6="",0)))))

and I need to add:

IF(+F8="ao",0),IF(+F8="aj",ROUND(E8/1.14*0.06,2),IF(+F8="bj",ROUND(E8/1.06*0.06,2),IF(+F8="CJ",0),IF(+F8="DJ",ROUND(E8/1.06*0.06,2),IF(+F8="IS",ROUND(E8/1.06*0.06,2),IF(+F8="LB",ROUND(E8/1.06*0.06,2),IF(+F8="PJ",0),IF(+F8="SJ",0),IF(+F8="TJ",ROUND(E8/1.14*0.06,2)

but my addition does not work with the previous formula.
It seems to have a problem with my brackets. I deleted them all and re-entered them but it still does not work.
Thanks for any help.

Re: Date Calculator For Weekends & Holidays

Here I tried to change a few things to make it access97 friendly. It works but only for Jan 1 2006. all the other days don't work and I'm sure I am missing a piece of code...please someone help.
Thanks,

Public Function fModBusDay(ByVal dDay As Date) As Date
     'Returns days to add to specified date depending
     'if day selected is a weekend or exists in tbl_holidays
    Dim stSQL As String
    Dim rst As ADODB.Recordset
    Dim lAdd As Long
    Dim dHol As Date
    Dim db As Database
    Dim rs As Recordset
    
     'first we get to the monday if dDay is Sat or Sun
TestWeekDay:
    Select Case WeekDay(dDay, vbMonday)
    Case 1 To 5: lAdd = 0
    Case Is = 6: lAdd = 2
    Case Is = 7: lAdd = 1
    End Select
    dDay = DateAdd("d", lAdd, dDay)
     'then we check to see if the revised date is a holiday
    Set db = CurrentDb()
    stSQL = "SELECT HolDate FROM tbl_Holidays WHERE HolDate = #" & dDay & "#"
    Set rs = db.OpenRecordset("tbl_holidays", dbOpenDynaset)
    
     'i.e. if a record is returned
    If Not rs.BOF Then
        dHol = rs(0)
        rs.Close
    End If
     'then check if dday is a holiday
    If dHol = dDay Then 'if so add another day and re-test
        dDay = DateAdd("d", 1, dDay)
        GoTo TestWeekDay
    Else ' if not, we have a valid day & can exit
        fModBusDay = dDay
        GoTo ExitHere
    End If
ExitHere:
End Function

Re: Date Calculator For Weekends & Holidays

I copied and pasted your code into a new database to make sure it worked.
I set up a table called tbl_holidays and a column called Description and the other called HolDate.
My form is called Travel Data. When it gives me the error message saying that the variable is undefined it highlights "CurrentProject"
I'm not sure how to fix it.

Re: Date Calculator For Weekends & Holidays

Now it is saying that a variable is undefined...

Set rst = CurrentProject.Connection.Execute(stSQL, , adCmdText)

??

Re: Date Calculator For Weekends & Holidays

[Blocked Image: http://img71.imageshack.us/my.php?image=snaphr4.jpg]
[Blocked Image: http://img45.imageshack.us/my.php?image=snap2kg2.jpg]

Here is the error message I am getting...

Re: Date Calculator For Weekends & Holidays

I hate to be a pain but I have access 97 only.
If you could copy and paste the code here for me that would be wicked.
I tried opening the file but it doens't recognize the newer version of access.
Thanks,

I found a shareware program that calculates Dates based on criteria you set up.
I need to find out the code for that program to use myself in access. I have made a table with all my holidays in it.
Now I need to create a code that will check to see if the day is 1. a weekend 2. a holiday or 3. a holiday that falls on a weekend that becomes a holiday on Monday. Then it needs to skip those days when calculating the due date.
I have attached a copy of the Date Calculator program that does exactly what I need an access code to do.
Please help I have tried so many different codes and nothing works.

Re: The "like" Criteria To Find Records

Haha ya I guess I shouldn't of wrote "this should work" when I have no idea what I am doing...haha
Thanks for the help...I figured it would be something dumb on my part.
It works great thanks very very much!

I am having some troubles making the "Like" criteria in access work properly for me. I am trying to use a form for the user to put in search criteria to find records.
For example find all records with the name "Danielle". I know this would be easy to just go into the query and put

Like "*Danielle*"

and it would find all variations of it but the people that are using my database will not know how to do that.
So I created a query that finds records based on criteria entered by the user on a separate form. I then put in the criteria:

Like"*([Forms]![Search]![Find Employee Name])*"

which should work but it does not.
Does anyone have any ideas???

Re: Code to add 10 days not including weekends

Can someone help me to modify this code to skip Dates that are holidays?
I have made a table called holidays and included all the holidays that need to be skipped.

Option Explicit


'==========================================================
' The DateAddW() function provides a workday substitute
' for DateAdd("w", number, date). This function performs
' error checking and ignores fractional Interval values.
'==========================================================
Function DateAddW(ByVal TheDate, ByVal Interval)


   Dim Weeks As Long, OddDays As Long, Temp As String


   If VarType(TheDate) <> 7 Or VarType(Interval) < 2 Or _
              VarType(Interval) > 5 Then
      DateAddW = TheDate
   ElseIf Interval = 0 Then
      DateAddW = TheDate
   ElseIf Interval > 0 Then
      Interval = Int(Interval)


   ' Make sure TheDate is a workday (round down).


      Temp = Format(TheDate, "ddd")
      If Temp = "Sun" Then
         TheDate = TheDate - 2
      ElseIf Temp = "Sat" Then
         TheDate = TheDate - 1
      End If


   ' Calculate Weeks and OddDays.


      Weeks = Int(Interval / 5)
      OddDays = Interval - (Weeks * 5)
      TheDate = TheDate + (Weeks * 7)


  ' Take OddDays weekend into account.


      If (DatePart("w", TheDate) + OddDays) > 6 Then
         TheDate = TheDate + OddDays + 2
      Else
         TheDate = TheDate + OddDays
      End If


      DateAddW = TheDate
   Else                         ' Interval is < 0
      Interval = Int(-Interval) ' Make positive & subtract later.


   ' Make sure TheDate is a workday (round up).


      Temp = Format(TheDate, "ddd")
      If Temp = "Sun" Then
         TheDate = TheDate + 1
      ElseIf Temp = "Sat" Then
         TheDate = TheDate + 2
      End If


   ' Calculate Weeks and OddDays.


      Weeks = Int(Interval / 5)
      OddDays = Interval - (Weeks * 5)
      TheDate = TheDate - (Weeks * 7)


   ' Take OddDays weekend into account.


      If (DatePart("w", TheDate) - OddDays) > 2 Then
         TheDate = TheDate - OddDays - 2
      Else
         TheDate = TheDate - OddDays
      End If
      'holidays
      If Not IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" _
     & TheDate & "#")) Then
     TheDate = TheDate + 1
     Else
     TheDate = TheDate
     End If


      DateAddW = TheDate
    End If


End Function

help the holidays part never works...I cannot seem to find a solution anywhere.
Thanks for any help!

Re: Updating Table with a Calculated field

Sorry Never mind my last response. I think that my initial database just has a glitch of somesort....some deleted piece floating around messing things up.
When I tried what you told me to do on the "sample" database I attached it worked just fine.
Thanks for all your help everything is now working great.
This site honestly rocks! I rely on the multitude of information floating around here and the fact that help is always available.
THANKS!!!

Re: Updating Table with a Calculated field

Ok I think I understand what you are saying but i am probably doing something wrong. Could you send me the database back with it working for you?

Re: Updating Table with a Calculated field

boooo...no that does not work either. I even tried it in the form field and it didn't even work there. The old function works fine on the form but not on the query....
I don't get it...

Re: Updating Table with a Calculated field

I tried that and it still did not work...it just said
"Undefined function <DateAddW> in expression. (Error 3085)
You entered an SQL expression that includes a Function procedure name that can't be recognized. Make sure the function exists, that it can be used in SQL expressions, or check the expression to make sure you entered the name correctly."

I am attaching a small sample of my database...work in progress...

Re: Updating Table with a Calculated field

SELECT DateAddW([Return Date],10) AS [Due Date]
FROM [By Travel Claim Number];

Is that what you were looking for?

Hey guys,
I am having a dilema.
I have a field in my access form that calculates the "Due Date". It Calculates the due date using a module I added. When I used the forumla from my module on the form it works and gives me the right day.
However, when I use the same formula in the query to try to update the actual table it says it does not recognize the function.
Any ideas???

Here is the Function:

Option Compare Database
Option Explicit


'**********************************************************
'Declarations section of the module
'**********************************************************






'==========================================================
' The DateAddW() function provides a workday substitute
' for DateAdd("w", number, date). This function performs
' error checking and ignores fractional Interval values.
'==========================================================
Function DateAddW(ByVal TheDate, ByVal Interval)


   Dim Weeks As Long, OddDays As Long, Temp As String


   If VarType(TheDate) <> 7 Or VarType(Interval) < 2 Or _
              VarType(Interval) > 5 Then
      DateAddW = TheDate
   ElseIf Interval = 0 Then
      DateAddW = TheDate
   ElseIf Interval > 0 Then
      Interval = Int(Interval)


   ' Make sure TheDate is a workday (round down).


      Temp = Format(TheDate, "ddd")
      If Temp = "Sun" Then
         TheDate = TheDate - 2
      ElseIf Temp = "Sat" Then
         TheDate = TheDate - 1
      End If


   ' Calculate Weeks and OddDays.


      Weeks = Int(Interval / 5)
      OddDays = Interval - (Weeks * 5)
      TheDate = TheDate + (Weeks * 7)


  ' Take OddDays weekend into account.


      If (DatePart("w", TheDate) + OddDays) > 6 Then
         TheDate = TheDate + OddDays + 2
      Else
         TheDate = TheDate + OddDays
      End If


      DateAddW = TheDate
   Else                         ' Interval is < 0
      Interval = Int(-Interval) ' Make positive & subtract later.


   ' Make sure TheDate is a workday (round up).


      Temp = Format(TheDate, "ddd")
      If Temp = "Sun" Then
         TheDate = TheDate + 1
      ElseIf Temp = "Sat" Then
         TheDate = TheDate + 2
      End If


   ' Calculate Weeks and OddDays.


      Weeks = Int(Interval / 5)
      OddDays = Interval - (Weeks * 5)
      TheDate = TheDate - (Weeks * 7)


   ' Take OddDays weekend into account.


      If (DatePart("w", TheDate) - OddDays) > 2 Then
         TheDate = TheDate - OddDays - 2
      Else
         TheDate = TheDate - OddDays
      End If


      DateAddW = TheDate
    End If


End Function

and here is what the field looks like:

=DateAddW([Return Date],10)

Re: Code to add 10 days not including weekends

Can anyone help make this code work in Access?

Re: Code to add 10 days not including weekends

Okay so:

=DateAdd("y",+10,Workday(Datevalue([Return Date],5,)))

This does not work but am I on the right track?

Re: Code to add 10 days not including weekends

Okay if I use the weekday function what would the code look like?