Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2008
    Posts
    78

    Unanswered: How do I link to code I've written within a Query?

    I'm a very new user to Access. Just a few days...

    I've created a table with two columns of dates. I wrote a function in the module to perform an operation on the dates and return an integer. I'd like to write a query that lists both dates and in the third column, the result of calling the funtion.

    I cannot figure out how to gain access to code that I've written in the "Module" section to be used in my query. Perhaps someone can point me to a "hello world" that illustrates the most basic linkage - I would be most appreciative.

    I'd also love to run the code I've written interactively. My function takes two dates as input and returns an integer. How can I run just this code and see the output?

    I went to macro, RunCode but i'm not having much luck. It just runs with no apparent output. How can I bring up some kind of window to run the code interactively?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    That's pretty good that you're writing functions in modules after just a few days of MSAccess (kudos).

    To see some examples of modules and different kinds of functions, I would recommend downloading and looking at some of the examples in the code bank.

    But regarding your question on writing a function, calling it (for example, passing an "integer" parameter to it), and then returning a "string" value from it, here's an example (with other examples to follow):

    - Create a new module and enter this function (it doesn't matter what you save the module name as - just don't ever save it as the same name as the function.) MSAccess can be picky about modules and functions with the same name and also, try not to create multiple functions using the same function name.

    Function RetSomeValue(ValueToFunction as integer) as String
    msgbox "The value passed to the RetSomeValue function is: " & ValueToFunction
    RetSomeValue = "XYZ"
    end function

    Then you'd call it in code like this - i.e. for example, to put this in the OnClick event of a button (doesn't matter for example purposes what you name the button but for future reference, you usually want to name buttons consistent to what they do (i.e. cmdClose or cmdOpen or cmdSomething for command button naming instead of the Command1, Command2, etc...that MSAccess defaults to when creating the button.))

    - to do this, create a button on a new form design, (cancel the wizard), show the properties window if it's not shown already (or double-click on the button in design view), then with the button properties screen showing, select the "Event" tab (again, in the properties screen window and the button highlighted/selected), click the dropdown for the On Click event and make it [Event Procedure], then click on the 3 ... to the right of the dropdown box (and put in the code below.) Then open the form and click on the button to see it work. Note: The "Event" tab is where you mainly put in code on certain actions (ie..On Click, On Dbl Click, On Got Focus, etc...) and you'll want to select [Event Procedure] and then the 3 ... to the right of the dropdown to get into the vba code of that event.) - you can also do this other ways but this is the typical way to write vba code.

    (note: passing the integer value in this example function doesn't really do anything and is just to show you how you'd do it)....

    Private Sub cmdMyButtonName_Click()
    dim SomeVariable as string
    SomeVariable = RetSomeValue(3)
    msgbox "String value returned from RetSomeValue function is: " & SomeVariable
    End Sub

    If you don't see how to create a button on a form, you'll want to show the "Form Design Tools" toolbar where you can see how to add text boxes, toggle buttons, tabs, buttons, dropdown boxes, etc...etc...)

    Save and open the form and then click on the button to see how it works.

    As a quick side note on control names on the form, keep in mind that if you change the name of a control after you've written the vba code (ie. OnClick event of a button), you'll also need to redo (or copy and paste) the vba code behind the control to the new name for that event. Hope I didn't confuse you here.

    An example (without passing an integer value to the function is)....

    Function RetSomeValue() as String
    RetSomeValue = "XYZ"
    end function

    Then you'd call this example like this in code (again, perhaps in the OnClick event of a button)...

    dim SomeVariable as String
    SomeVariable = RetSomeValue()
    msgbox "String value returned from RetSomeValue function is: " & SomeVariable

    An example of a function returning a date value is...

    Function RetSomeDateValue() as Date
    RetSomeDateValue = #01/01/2008#
    end function

    Then you'd call this example like this in code (again, perhaps in the OnClick event of a button or even try another event on a different control (ie. perhaps in the On Dbl Click event of a textbox) in the form design to experiment)...

    dim SomeDateVariable as Date
    SomeDateVariable = RetSomeDateValue()
    msgbox "Date returned from RetSomeDateValue function is: " & SomeDateVariable

    or more specific to your question...(to pass 2 dates to a function and return the difference (as an integer))...

    Function RetDateDiff(Date1 as Date, Date2 as Date) as Integer
    RetDateDiff = Date1 - Date2 (Note: also check out the DateAdd and DateDiff expressions) ex: RetDateDiff = DateDiff("d",Date1,Date2)
    end function

    Then you'd call this example like this in code (again, perhaps in the OnClick event of a button)...

    msgbox "The difference between 03/01/2008 and 01/01/2008 is: " & RetDateDiff(#03/01/2008#,#01/01/2008#)

    or to utilize the function in a query, just add a column (ie. "expression" - in the "Field" row) to the query...

    MyExprColumn: RetDateDiff(#03/01/2008#,#01/01/2008#)

    or even...

    MyExprColumn: RetDateDiff([MyDate1FieldNameinTheTable],[MyDate2FieldNameinTheTable])

    and then run the query. You'll find that there's a lot of different ways to call functions and return values.

    Also check out the "Immediate Window" and "Debug.Print" MSAccess help topics for printing immediate values in calling a function.

    As a comment on Macros, while they are good to start, you'll find that actually writing vba code (usually on an event for a control) is more ideal and practical.

    Also keep in mind that functions can be written and saved in a module or also in the vba coding part of the form design itself.

    Hope that helps. I jumped around a bit here, but again, download some of the examples in the code bank for more practical uses of functions in modules or functions in general (a couple of good examples can be found in this example: http://www.dbforums.com/showpost.php...9&postcount=68) - see the "Check for Admin" module and the "UserName (GetUser)" module and the "GetUser" form. The best way I learned to design functions was to look at other examples.
    Last edited by pkstormy; 08-31-08 at 21:32.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    agree writing code modules after a few days use is impressive.. it takes many people a long long time to wean themselves off macros into the world of VBA....

    you can use your function in a SQL query
    eg
    select DateColumn1, DateColumn2, myDateFunction(DateColun1, DateColumn2) as returnedValue from mytable

    as Paul (PKStormy) says you can now use the function in any form. report whatever in the same Access application

    eg you can use the vlaue in a control, as say the contorls data source =myDateFunction(DateColun1, DateColumn2)

    if you want to see the resutlts of running code interactively look at the code window, and select the immediate pane

    you can debug code esasily there using a question mark to returnt he value of any variable in the current scope of the code or evaluate any fnction including user written ones.
    you cna insert a break point in the code to step throug a code block... have a lok at the help system for tips on setign watches, breakpoints, debugging, stepping through code.....
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just to throw another option out there, that may or may not be applicable depending on the complexity of your function...

    Write the function in the SQL itself, [very simple] example:
    Code:
    SELECT col1
         , col2
         , col1 - col2 As [difference]
    FROM  my_table
    This would be far more efficient than a disconnected function; however you are [argueably] limited to the complexity of logic you can use.

    If you post your module code we might be able to help you to such a solution.
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    i did wonder if the function replicates the functin of dateadd........
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Aug 2008
    Posts
    78
    Thanks for the help guys. I have some db background but never used Access before. The concepts are similar to other systems but learning my way around is the stumbling block!




    Table Name: Orders

    Field List
    ============
    Order Number
    Task1DueDate
    Task2DueDate
    Task3DueDate
    <other fields>

    My goal is to run a query that returns the dates that are overdue by 2 or more WORKING days.

    For example, if today is Monday and Task1DueDate was last Friday, it would not appear in the query. If today is Monday and Task1DueDate was last Wednesday, then it would appear.


    My approach would be to write a function that is given two dates and returns an Integer indicating the number of working days between them.

    For example I might invoke myWorkingDaysFN(Task1DueDate, Now())

    I'm just not sure how to marry the Code to the Query. When I'm in the Query builder I can't seem to get the right syntax.

    Here is my function

    Code:
    Function myWorkingDaysFN(BegDate, EndDate)
       Const SUNDAY = 1
       Const SATURDAY = 7
       Dim NumDays As Integer
    
       If BegDate > EndDate Then
          myWorkingDaysFN = 0
       Else
          Select Case Weekday(BegDate)
             Case SUNDAY: BegDate = BegDate + 1
             Case SATURDAY: BegDate = BegDate + 2
          End Select
          Select Case Weekday(EndDate)
             Case SUNDAY: EndDate = EndDate - 2
             Case SATURDAY: EndDate = EndDate - 1
          End Select
          NumDays = DateDiff("d", BegDate, EndDate)
          myWorkingDaysFN = NumDays + Weekday(EndDate) - Weekday(BegDate)
       End If
    End Function
    
    Function helloWorld()
     helloWorld = "Hello World"
    End Function

    I am not sure if this code works yet, I wanted to run it interactively with some sample dates and then debug it. It's embarrassing to post this not even knowing if it works.



    My plan was to first write a query that returns the date in question and the result of the "myWorkingDaysFN" function in another column so I can examine the behavior. I'm in the process of taking all of your advice and trying to apply it. Any comments would be much appreciated.

    When I've had success I'll post again!

    Update: 12:22 PM

    In the query design view, I've changed the field to be:

    Expr3: helloWorld()

    And the query produces a column of "Hello World" - Linkage Success!

    I've changed another field to be
    Expr5: myWorkingDaysFN("Task1DueDate",Now())

    And one of the columns is all zeros - Linkage Success, Code Fail!
    Last edited by PonPending; 09-01-08 at 13:25.

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Don't EVER be embarrased about posting any code!

    If "Task1DueDate" is a field in the table, try changing this....

    Expr5: myWorkingDaysFN("Task1DueDate",Now())

    to

    Expr5: myWorkingDaysFN([Task1DueDate],Now())

    This....
    Expr5: myWorkingDaysFN("Task1DueDate",Now()) means you are passing a "string" value ("Task1DueDate") versus the field value in the table to the function (also make sure Task1DueDate is defined as a Date/Time type in your table.)

    You may also want to try this as your function statement -

    Function myWorkingDaysFN(BegDate, EndDate) as integer
    or
    Function myWorkingDaysFN(BegDate as Date, EndDate as Date) as integer

    Note: I didn't qc the coding in the function itself though but looking at it quickly, it looks ok although this...

    Case SUNDAY: BegDate = BegDate + 1
    Case SATURDAY: BegDate = BegDate + 2

    should probably be changed to...

    Case "SUNDAY"
    BegDate = BegDate + 1
    Case "SATURDAY"
    BegDate = BegDate + 2
    End Select

    (I use the above type syntax versus that below but the point is that I "think" you need to surround Sunday and Saturday in quotes)

    or

    Case "SUNDAY": BegDate = BegDate + 1
    Case "SATURDAY": BegDate = BegDate + 2

    ....
    Last edited by pkstormy; 09-01-08 at 13:46.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Aug 2008
    Posts
    78
    This advice worked great! It was taking it as a string.

    If "Task1DueDate" is a field in the table, try changing this....

    Expr5: myWorkingDaysFN("Task1DueDate",Now())

    to

    Expr5: myWorkingDaysFN([Task1DueDate],Now())

    I also realized that our Due Dates will never fall on a weekend, and I'm always comparing our Due Dates against today, which is the date the query runs. This simplifies the code (and I changed the function name to something more meaningful):


    Code:
    Function daysOverdue(DueDate)
    
     ' Due Dates can never fall on a weekend
       
       Const SUNDAY = 1
       Const SATURDAY = 7
       Dim NumDays As Integer
       EndDate = Now()
       If DueDate >= EndDate Then
          daysOverdue = "0"
       Else
          Select Case Weekday(EndDate)
             Case SUNDAY: EndDate = EndDate - 2
             Case SATURDAY: EndDate = EndDate - 1
          End Select
          NumDays = DateDiff("d", DueDate, EndDate)
          daysOverdue = NumDays + Weekday(EndDate) - Weekday(DueDate)
       End If
    End Function
    It's producing some almost correct results but after a little tracing I think I'll be able to figure it out. The big problem is that I don't want to count any weekend days into the days overdue...


    You guys have been a lot of help!

  9. #9
    Join Date
    Aug 2008
    Posts
    78
    My code was way off base. I've brought the problem to conclusion

    Code:
    daysOverdue(DueDate)
        Dim iDays
        Dim iWorkDays
        Dim sDay
        Dim i
        
        iDays = DateDiff("d", DueDate, Now())
       
        iWorkDays = 0
       
        For i = 0 To iDays
          'First day of the week is sunday
          sDay = Weekday(DateAdd("d", i, DueDate))
          If sDay <> 1 And sDay <> 7 Then
            iWorkDays = iWorkDays + 1
          End If
        Next
        daysOverdue = iWorkDays - 1
      End Function
    Thanks for all your help, I'm looking forward to tackling other challenges in access!

    One quick question? A good tutorial/reference book or two for access? I will purchase one used on half.com!

    thanks again

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The Sybex MSAccess Developer's Handbook is a MUST have and well worth the price (it's the only book I keep on my shelf.) It has tons of example code on the cd and doesn't require a "Setup" process to utilize the examples (ie. you just open the mdb and look at the code.)

    Since you've seemed to surpase the beginner's level already, I would also recommend the Sybex VBA Developer's handbook as well for vba coding (I think you can get books 1 and 2 in a package.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Here's a few links:

    http://www.developershandbook.com/

    (I would recommend all of the Sybex books in the above link but mostly the first 3 books which show at the top.)

    Especially this package of both volumes: http://www.amazon.com/exec/obidos/AS...234668-9725519

    http://www.alibris.com/search/books/qwork/4350526/used/Microsoft%20Access%202%20Developer's%20Handbook

    And yes - getting the 2000 version will help you regardless of which MSAccess version you have! (and then I also have the 2002 version which gives you examples specific to 2002).
    Last edited by pkstormy; 09-01-08 at 16:14.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Aug 2008
    Posts
    78
    thanks again. half.com here i come!

  13. #13
    Join Date
    Aug 2008
    Posts
    78
    I've actually made the code a little more efficient and flexible to include logic that allows for Holidays. I've broken out the skipping thru the dates to test if something is a working day, as an integer function returning 1 or 0, then using THAT to increment the days counter.

    Code:
    Function daysOverdue(DueDate) As Integer
    
        Dim iDays
        Dim iWorkDays
        Dim sDay
        Dim i
        
        iDays = DateDiff("d", DueDate, Now())
        iWorkDays = 0
       
        For i = 0 To iDays
         iWorkDays = iWorkDays + isWorkingDay(DateAdd("d", i, DueDate))
        Next
        
        daysOverdue = iWorkDays - 1
      End Function
      
      
    Function isWorkingDay(dateToTest) As Integer
        Const SUNDAY = 1
        Const SATURDAY = 7
        
        isWorkingDay = 1
        
        Select Case Weekday(dateToTest)
             Case SUNDAY:   isWorkingDay = 0
             Case SATURDAY: isWorkingDay = 0
        End Select
    
        Select Case dateToTest
            Case #9/1/2008#: isWorkingDay = 0
            Case #10/13/2008#: isWorkingDay = 0
            Case #11/11/2008#: isWorkingDay = 0
            Case #11/27/2008#: isWorkingDay = 0
            Case #12/25/2008#: isWorkingDay = 0
        End Select
        
     
    
    End Function
    Last edited by PonPending; 09-01-08 at 17:31.

Posting Permissions

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