Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2007
    Posts
    127

    Unanswered: Linking Functions in Access Query

    I have just created a function in the Modules part of Access, how do I call it via Query?

    Many Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I believe the simple answer is: You can't.

    What is your function and what are you trying to achieve?
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2007
    Posts
    127
    Below the the code for the function....

    It works out the working days, minusing all the bank holidays..

    I need to add it to my query

    Working Day: WKDAYS([ReferralDate],[DateofVisit])-1

    Code:
    Option Compare Database
    
    Function WKDAYS(date1 As Variant, date2 As Variant) As String
    'TAKES 2 DATES IN DD/MM/YYYY FORMAT AND CALCULATES THE WORKING DAYS(MONDAY - FRIDAY) BETWEEN THE 2 DATES and excludes Bank holidays
    
    BANK = "01/01/2007,06/04/2007,09/04/2007,07/05/2007,28/05/2007,27/08/2007,25/12/2007,26/12/2007,01/01/2006,02/01/2006,14/04/2006,17/04/2006,01/05/2006,29/05/2006,28/08/2006,25/12/2006,26/12/2006,03/01/2005,25/03/2005,28/03/2005,02/05/2005,30/05/2005,29/08/2005,25/12/2005,26/12/2005,27/12/2005,01/01/2004,09/04/2004,12/04/2004,03/05/2004,31/05/2004,30/08/2004,25/12/2004,26/12/2004,27/12/2004,28/12/2004,01/01/2003,21/04/2003,22/04/2003,05/05/2003,26/05/2003,27/05/2003,25/08/2003,26/08/2003,25/12/2003,26/12/2003"
    
    we = 0
    SDATE = CDate(Left([date1], 4) & "/" & Mid([date1], 5, 2) & "/" & Right([date1], 2))
    ndate = CDate(Left([date2], 4) & "/" & Mid([date2], 5, 2) & "/" & Right([date2], 2))
    tdate = CDate(Left([date1], 4) & "/" & Mid([date1], 5, 2) & "/" & Right([date1], 2))
     
     If date1 = date2 Then
        If Weekday(SDATE) = 1 Or Weekday(SDATE) = 7 Then
           WKDAYS = 0
           Else
        If InStr([BANK], [SDATE]) <> 0 Then
           WKDAYS = 0
           Else
           WKDAYS = 1
        End If
     End If
     Else
     
    Do While Not SDATE = ndate + 1
    
             If (Weekday(SDATE) = 1 Or Weekday(SDATE) = 7) Or InStr([BANK], [SDATE]) <> 0 Then
                we = we + 1
                SDATE = SDATE + 1
                Else
                SDATE = SDATE + 1
             End If
    
    Loop
    
    WKDAYS = DateDiff("d", tdate, ndate) - we + 1
    End If
    End Function

  4. #4
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    In the query grid - right click in an empty column in the Field row and choose Build. In the left bottom pane scroll down to Functions and double click. You should see the name of your database listed, double click it. You should now see the functions listed in the center pane.

  5. #5
    Join Date
    Oct 2007
    Posts
    127
    Quote Originally Posted by rogue
    In the query grid - right click in an empty column in the Field row and choose Build. In the left bottom pane scroll down to Functions and double click. You should see the name of your database listed, double click it. You should now see the functions listed in the center pane.
    I've done as requested... But I get the following error -

    Undefined function 'WKDAYS' in expression

    This is the query i'm creating -

    Working Days: WKDAYS([Referral Date],[Date of Visit])-1

  6. #6
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Have you tested your function to ensure that it works?

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    To utilize a function in a query, you would do an expression in the column (same as what you are doing)... ex:

    Expression1: Getuser()
    where Getuser is a function which returns the user LoginID
    or
    Exp2: retSecLevel(Getuser())
    (nested functions) where passing the value returned in the GetUser function, calls the retSecLevel function to return the security level of the user.
    or (as you've put in)
    Working Day: WKDAYS([ReferralDate],[DateofVisit])-1

    but make sure your function WKDAYS utilizes 2 parameters and that ReferralDate and DateofVisit are valid fields in the table (and by the looks of the function, need to have a vaild date value.) Also, notice that WKDAYS returns as a string which it looks like you are subtracting 1 from a string value. You also need to make sure ALL your ReferralDate and DateOfVisit are date values as this is the type the function is expecting. Having some other value (or possibly a null value) can cause the whole query to die with an error. You may want to set another criteria field in the query so the return is limited to just records whereby you know the values of these fields contain valid dates.

    Add ReferralDate and DateOfVisit into the query and check on the values of these. If any are non-date (or null) values, your query which utilizes the WKDAYS function is not going to work.
    Last edited by pkstormy; 10-23-07 at 12:33.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I apologise and am very glad I've been proved wrong!
    +1 to everyone
    George
    Home | Blog

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I just considered it a typo error george
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Oct 2007
    Posts
    127
    To utilize a function in a query, you would do an expression in the column (same as what you are doing)... ex:

    Expression1: Getuser()
    where Getuser is a function which returns the user LoginID
    or
    Exp2: retSecLevel(Getuser())
    (nested functions) where passing the value returned in the GetUser function, calls the retSecLevel function to return the security level of the user.
    or (as you've put in)
    Working Day: WKDAYS([ReferralDate],[DateofVisit])-1

    but make sure your function WKDAYS utilizes 2 parameters and that ReferralDate and DateofVisit are valid fields in the table (and by the looks of the function, need to have a vaild date value.)

    What happens if the date value is not valid… For example, users entering incorrect date values… Can I add another line to the coding, so that an error message is in the field… so I can identify them and make correct amendments

    Also, notice that WKDAYS returns as a string which it looks like you are subtracting 1 from a string value.

    Is this incorrect, what can I do here?

    You also need to make sure ALL your ReferralDate and DateOfVisit are date values as this is the type the function is expecting.

    The data type for those fields is set to Data/Time

    Having some other value (or possibly a null value) can cause the whole query to die with an error. You may want to set another criteria field in the query so the return is limited to just records whereby you know the values of these fields contain valid dates.

    How do I go about in doing this?

    Add ReferralDate and DateOfVisit into the query and check on the values of these. If any are non-date (or null) values, your query which utilizes the WKDAYS function is not going to work.

    I have checked the fields and yes, there are non-date (or null) values. My functions stops at line –

    Do While Not SDATE = ndate + 1

    If (Weekday(SDATE) = 1 Or Weekday(SDATE) = 7) Or InStr([BANK], [SDATE]) <> 0 Then
    we = we + 1
    SDATE = SDATE + 1
    Else
    SDATE = SDATE + 1
    End If

    Loop

    Thanks in advance

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pkstormy
    I just considered it a typo error george
    In my defence - I thought the OP was referring to a function in a class/module...
    George
    Home | Blog

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Question: What happens if the date value is not valid… For example, users entering incorrect date values… Can I add another line to the coding, so that an error message is in the field… so I can identify them and make correct amendments?
    Answer: Use the isDate() function in your function (ie. if isDate(SomeValue) = true then....or if isnull(SomeValue) then....). Somewhere in your function you need to test for a valid date since it sounds like you do have some invalid date values. If it's not a valid date, you need to handle that by returning some value (perhaps return a Null or "" value) from the function to keep the expression working.

    Question: Also, notice that WKDAYS returns as a string which it looks like you are subtracting 1 from a string value.
    Is this incorrect, what can I do here?
    Answer: Have your function return as a Date verses a String or convert the value to a date. The way you have it set up may or may not work as it is but it's something which stood out. Access may allow some flexibility and compute this correctly but then again, it may not. You may want to do the -1 part in your function to prevent the expression in the query trying to compute on non-date fields. To make things easy, try to do all the computing part in the function verses doing some of it in the function and some if it in the query expression. In the function, after you've tested for a valid date, do your -1 in the function.

    Question: Having some other value (or possibly a null value) can cause the whole query to die with an error. You may want to set another criteria field in the query so the return is limited to just records whereby you know the values of these fields contain valid dates.
    How do I go about in doing this?
    Answer: put in some criteria in your query on another field (ie. your autonumber field) to return just the records in question. If you know the ID's of those records for your ID field (whatever your autonumber field is), put in criteria in the query where the ID values = the ID values of the questionable records. Test your query with ID's where you know you have valid date values, then put in criteria for ID's where you know you have invalid dates values.

    Statement: I have checked the fields and yes, there are non-date (or null) values. My functions stops at line –
    Answer: See answer to first question. You need to test for non-date or null values somewhere in the function.
    Last edited by pkstormy; 10-24-07 at 11:05.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by georgev
    In my defence - I thought the OP was referring to a function in a class/module...
    You can utilize functions in a class/module in an expression for a query (I thought this was what the OP was doing.) I'm a little confused now what you mean.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Me too... Me too
    George
    Home | Blog

  15. #15
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926




    .
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •