none
Calling UDF (MS Access) from Excel VBA RRS feed

  • Question

  • Hi,
    I need to run a query on an MS Access database that has to perform a weekday calculation between two date columns, and this query has two date parameters that it receives dynamically. I am using Excel VBA as the front end.

     copy to clipboard
    Select  AgeRange, count(*) from
        (Select IIF(networkdays(date1,date())<=2, "0-2 days","over 2 days")) as AgeRange
              from table1
              where date2 >= startdt 
                  and date2 <=enddt
    ) d
    group by AgeRange

    The actual query is more complex, but this will help illustrate the issue. I build a string variable with the query, and substitute Startdt and enddt variables with the actual values. I then create a new QueryDef object and get the results from OpenRecordSet.

    Except, Networkdays is not a valid function in MS Access. So, this query fails.

    I created a WorkDays UDF in MS Access, and used that instead - the query works fine in MS Access, but when I try to pass it in the QueryDef object, it fails with 3085 error (function not available).

    I'm not sure how to get around this, and could use some help.

    In summary, I need to run a select query that will calculate weekdays or workdays between two columns (query is dynamic - dates are variable) from VBA to MS Access, get the results back to VBA so that I can populate the results. Any suggestions are appreciated and welcome.
    Thanks!

    I tried using datediff with the "w" parameter, but that doesn't work as I had hoped - it gives the number of weeks between the dates instead of weekdays

    I realize that I could get all the data (without doing the group by) down into Excel and then, do the networkdays formula calculation there, and summarize after that, but that is a very inefficient way - and defeats the purpose of having an access database in the first place. Getting tens of thousands of records is going to cause space issues in Excel too.

    Thursday, January 22, 2015 2:03 PM

Answers

  • Hi Bob,

    One solution is to create a table in the Access database storing all the dates in these years, add a column to determine if this date is a week day or not. And create another table to store the dates that are to be excluded from the working days.

    In this way, you need to create a separate query to count the valid working days based on the two tables. So the most difficult thing is to create the table that contains all the dates, please check this VBA code snippet which helps you to insert all the dates into the table.

    Sub InsertDatesIntoTable()
      Dim StartDate As Date
      Dim EndDate As Date
      Dim d As Date
      Dim w As Integer
      Dim IsWorkDay As Boolean
    
      StartDate = #1/1/2015#
      EndDate = #12/31/2015#
    
      For d = StartDate To EndDate
         Debug.Print d
         w = Weekday(d, vbSunday) 'first day of the week is Sunday
         If w = 1 Or w = 7 Then ' if the current day is Sunday or Satuday
            IsWorkDay = False
         Else
            IsWorkDay = True
         End If
         
         'Here you can insert d and IsWorkDay into the table
      Next d
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Bob-12 Friday, January 23, 2015 1:01 PM
    Friday, January 23, 2015 6:04 AM
    Moderator

All replies

  • Hi Bob,

    One solution is to create a table in the Access database storing all the dates in these years, add a column to determine if this date is a week day or not. And create another table to store the dates that are to be excluded from the working days.

    In this way, you need to create a separate query to count the valid working days based on the two tables. So the most difficult thing is to create the table that contains all the dates, please check this VBA code snippet which helps you to insert all the dates into the table.

    Sub InsertDatesIntoTable()
      Dim StartDate As Date
      Dim EndDate As Date
      Dim d As Date
      Dim w As Integer
      Dim IsWorkDay As Boolean
    
      StartDate = #1/1/2015#
      EndDate = #12/31/2015#
    
      For d = StartDate To EndDate
         Debug.Print d
         w = Weekday(d, vbSunday) 'first day of the week is Sunday
         If w = 1 Or w = 7 Then ' if the current day is Sunday or Satuday
            IsWorkDay = False
         Else
            IsWorkDay = True
         End If
         
         'Here you can insert d and IsWorkDay into the table
      Next d
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Bob-12 Friday, January 23, 2015 1:01 PM
    Friday, January 23, 2015 6:04 AM
    Moderator
  • http://superuser.com/questions/35716/equivalent-of-excels-networkdays-function-with-jet-ado

    ([DateEnd]-([DateStart]+(Weekday([DateEnd])-Weekday([DateStart]))))/7*5+(Weekday([DateEnd])-Weekday([DateStart]))+1+IIf(Weekday([DateEnd])=7,-1,0)+IIf(Weekday([DateStart])=1,-1,0)

    Friday, January 23, 2015 6:06 AM
  • Thanks danielsparkk - I'll try this out, but given the volume of records, I will need to make sure it doesn't adversely impact the performance. It does seem logically correct though.
    Friday, January 23, 2015 1:03 PM
  • Thanks Caillen Zhong. This sounds like the way to go. I'll think I should be able to figure out how to do the actual calculation. I appreciate your help.
    Friday, January 23, 2015 1:04 PM