Results 1 to 10 of 10
  1. #1
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192

    Error in Module

    Hi everybody.
    In my contracts and shipments database, a contracted item should be delivered within a stipulated date. If the difference between the stipulated date and delivery date exceeds certain days penalty is levied. However intervening declared holidays are excluded.
    There is a Table ‘tblDeclaredHolidays’ with 3 fields, ID, DecHolidayDate (Date/time) and DecHolidayReason (text). The table is not related with other tables. Only holiday dates are entered with reason for holiday.
    I created the following Module to calculate the number of holidays falling between the stipulated date and delivery date:
    ------------------------------------------------------------------------------------------------------------------------------------------
    Option Compare Database


    Option Explicit
    Public Function InterveningHolidays(StipulatedDate As Date, DeliveryDate As Date) As Variant
    Dim MySQL As String
    MySQL = "SELECT Nz(Count([tblDeclaredHolidays]![DecHolidayReason]),0) AS Holidays " & vbCrLf & _
    "FROM tblDeclaredHolidays " & vbCrLf & _
    "WHERE (((tblDeclaredHolidays.DecHolidayDate)>=Stipulated Date And (tblDeclaredHolidays.DecHolidayDate)<=DeliveryDate ));"
    InterveningHolidays = MySQL
    End Function

    When the function is assigned in the query, instead of showing the result, the SQL is shown. If the function is declared as Integer, Error 13 message is displayed. I think there is something wrong in declaring variables. I also feel the Select Statement may be wrong.
    Please tell what I am doing wrong.
    With thanks,
    Alex

  2. #2
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You assign the contents of the mySQL variable to the function with the following statement, so that is why the function is giving you the SQL text

    InterveningHolidays = MySQL



    You might check out this site for a function that calculates working days between two given dates. The second function presented uses a holiday table similar to what you were trying to do.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You are missing a few parts. You cannot just assign a SQL string to a variable. You have to open the recordset first.

    Code:
    Option Compare Database
    Option Explicit
    
    Public Function InterveningHolidays(StipulatedDate As Date, DeliveryDate As Date) As Variant
       Dim rst As DAO.Recordset
       Dim MySQL As String
    
       ' set default return value
       InterveningHolidays = 0
    
       MySQL = "SELECT Nz(Count([tblDeclaredHolidays]![DecHolidayReason]),0) AS Holidays"
       MySQL = MySQL & " FROM tblDeclaredHolidays "
       MySQL = MySQL & " WHERE (((tblDeclaredHolidays.DecHolidayDate)>=Stipulated Date And (tblDeclaredHolidays.DecHolidayDate)<=DeliveryDate ));"
    
       ' open recordset
       Set rst = CurrentDb.OpenRecordset(MySQL)
    
       ' check if records in recordset
       If Not rst.BOF And Not rst.EOF Then
          'populate full recordset
          rst.MoveLast
          ' assign return value
          InterveningHolidays = rst.RecordCount
       End If
    
       ' clean up
       rst.Close
       Set rst = Nothing
    
    End Function
    Didn't have time to test this, but it should run ....

  4. #4
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192

    Error in module

    Tnank u jzwp11 and Steve.
    jzwpll, your link solved my problem.

    Thanks Steve for pointing my mistake. However, your code shows the following error message:

    Run-time error 3061:

    Too few parameters. Expected2.

    Can u please analyse and find the error?

    Alex

  5. #5
    SteveH2508 is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Chelsea, London, England
    Posts
    117
    "WHERE (((tblDeclaredHolidays.DecHolidayDate)>=Stipulated Date And (tblDeclaredHolidays.DecHolidayDate)<=DeliveryDate ));"
    StiplulatedDate shouldn't have a space in and StipulatedDate and DeliveryDate should have date delimiters (#) round them so try this (untested)

    "WHERE (((tblDeclaredHolidays.DecHolidayDate)>=#" & StipulatedDate & "# And (tblDeclaredHolidays.DecHolidayDate)<=#" & DeliveryDate & "#));"

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    It looks to me as though you are ready to use the Thread Tools and mark this thread as Solved, yes?

  7. #7
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192
    Dear RuralGuy,
    Even though my problem is solved, the following code as suggested by Forum friends, still returns wrong result. All the record return the result 1, irrespective of the actual intervening holidays. I want the experts to analyse the code and find what is wrong with it.

    Rewritten Code:

    Option Compare Database
    Option Explicit

    Public Function InterveningHolidays(StipulatedDate As Date, DeliveryDate As Date) As Variant
    Dim rst As DAO.Recordset
    Dim MySQL As String

    ' set default return value
    InterveningHolidays = 0

    MySQL = "SELECT Nz(Count([tblDeclaredHolidays]![DecHolidayReason]),0) AS Holidays"
    MySQL = MySQL & " FROM tblDeclaredHolidays "
    MySQL = MySQL & " WHERE (((tblDeclaredHolidays.DecHolidayDate)>=#" & StipulatedDate & "# And (tblDeclaredHolidays.DecHolidayDate)<=#" & DeliveryDate & "#));"

    ' open recordset
    Set rst = CurrentDb.OpenRecordset(MySQL)

    ' check if records in recordset
    If Not rst.BOF And Not rst.EOF Then
    'populate full recordset
    rst.MoveLast
    ' assign return value
    InterveningHolidays = rst.RecordCount
    End If

    ' clean up
    rst.Close
    Set rst = Nothing

    End Function
    ------------------------------------------------------
    With regards,
    Alex

  8. #8
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Code:
    SELECT Nz(Count([tblDeclaredHolidays]![DecHolidayReason]),0) AS Holidays
    You don't need the Nz Function call there. Since you're returning a count of the holidays already, it will already return zero if it doesn't find any.

    Code:
    InterveningHolidays = rst.RecordCount
    Since your SQL Query is just returning the number of holidays, it will ALWAYS return a single Record (even if the number of holidays is zero).

    I don't know your exact database structure, but try this function out. . .
    Code:
    Option Compare Database
    Option Explicit
    
    Public Function InterveningHolidays(StipulatedDate As Date, DeliveryDate As Date) As Integer
      Dim rst As DAO.Recordset
    
      Dim strSQL As String
    
      ' set default return value
      InterveningHolidays = 0
    
      strSQL = "SELECT Count([DecHolidayReason]) AS Holidays " & _
               "FROM tblDeclaredHolidays " & _
               "WHERE [DecHolidayDate] BETWEEN #" & StipulatedDate & "# AND #" & DeliveryDate & "#"
    
      ' open recordset
      Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)
    
      InterveningHolidays = rst("Holidays")
    
      ' clean up
      rst.Close
    
      Set rst = Nothing
    End Function

  9. #9
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192
    Thanks Rawb.
    Your solution was wonderful and finally solved the problem.
    I thank all who tried for a solution.
    Alex

  10. #10
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Alex Motilal View Post
    Tnank u jzwp11 and Steve.
    jzwpll, your link solved my problem.

    Thanks Steve for pointing my mistake. However, your code shows the following error message:

    Run-time error 3061:

    Too few parameters. Expected2.

    Can u please analyse and find the error?

    Alex

    As noted in other posts, I missed a few things also.
    The references to the table should have been removed, the date delimiters were missing and the parameters needed to be concatenated to the WHERE clause.

    I should have taken more time..... sorry

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How do I use a MODULE in a Cross-Tab Query?
    By timo1999 in forum Modules
    Replies: 2
    Last Post: 12-13-2014, 04:51 PM
  2. Creating Macro from Module
    By Harley Guy in forum Modules
    Replies: 1
    Last Post: 11-08-2010, 07:44 AM
  3. Running module from Macro
    By Harley Guy in forum Modules
    Replies: 6
    Last Post: 10-27-2010, 11:05 AM
  4. need help with import Module
    By marubal21 in forum Modules
    Replies: 1
    Last Post: 07-22-2010, 12:23 AM
  5. Module not found
    By nooby in forum Modules
    Replies: 1
    Last Post: 12-02-2009, 02:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums