Results 1 to 14 of 14
  1. #1
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163

    VBA function result as query criteria

    I've written this function in a module, which appears to work and return the correct value when I step through it. However, I can't figure out how to use the result as a query criteria. I end up with weird results like an empty query or a message saying the expression is too complex. I feel pretty good about my function (which I'm sure is false confidence) because it steps through correctly. My function is below.



    Public Function OrderFee()


    Dim Client As Integer
    Dim Billto As Integer
    Dim FilteredClient As Integer


    Client = Forms("orders").ClientID
    Billto = Forms("orders").BillToID


    If Forms("orders").ServiceID = 15 Or Forms("orders").ServiceID = 17 Then
    FilteredClient = Billto

    Else
    FilteredClient = Client

    End If


    End Function

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    This is a very simple calculation - why use a custom VBA function instead of IIf() expression in query or textbox?

    IIf([ServiceID] = 15 Or [ServiceID] = 17, [BillToID], [ClientID])

    Post the query SQL statement you are attempting. How do you want to use this calculated value as 'criteria'? Exactly what are you trying to accomplish?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Because that would be too easy. Why build one when you can build two at twice the price. Thanks for saving me from myself June. Worked like a charm.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    However, I can't figure out how to use the result as a query criteria.
    You have the simplest solution, but what about down the road when you need to get something out of a function?
    Here is an opportunity for you to expand your knowledge about functions, such as when you need a function or a sub will do; how to pass values or objects to a function (or just references to them); how to get a function to return a value or object and when to pass values by reference. Your example will figure out what the variable equals, but doesn't return that value to anything that could use it.

    While I'm at it, I recommend you read up on naming variables. Let's say you are reviewing code and are 100 lines deep, trying to debug a problem. You are looking at the variable "Provider" and are wondering what data type it is (you can't remember every detail, can you?). If your variable name was strProvider (and was properly declared), you'd know. Better to adopt good practice conventions early on.

  5. #5
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Thanks Micron. I actually kept at the function and got it to work. Although the expression idea offered promise, I just couldn't get over the fact that the function idea should work. I became obsessed with figuring out why it wasn't.

    It appears that I hadn't made the function name equal to the result I was trying to pass.

    In other words, I had a function called OrderFee() but had declared a variable called FilteredClient and made that equal to the result of the if. I never ultimately made the function name equal to it.

    So I just eliminated the variable "FilteredClient". So my last instructions changed to


    OrderFee = Client

    OrderFee = Billto

    Thanks for the advice on the naming stuff. I can see how that would help with understanding everything after I finally get it to work.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    How about something a bit more concise:
    Code:
    Public Function OrderFee() as Integer
    
    If Forms("orders").ServiceID = 15 Or Forms("orders").ServiceID = 17 Then
      OrderFee = Forms("orders").BillToID
    Else
       OrderFee =Forms("orders").ClientID
    End If
    
    End Function
    Do you get the reason for declaring the function's returned data type? If there is a common default situation, you can even set the value of a function right off and change it for those exceptions if it makes sense and is safe to do so:
    Code:
    Public Function OrderFee() as Integer
    
    OrderFee =Forms("orders").ClientID
    If Forms("orders").ServiceID = 15 Or Forms("orders").ServiceID = 17 Then
      OrderFee = Forms("orders").BillToID
    End If
    
    End Function
    Either way, you should consider error handling if you have not done so, lest your function crash if someone enters a non-integer value in a control. The original expression is still the best solution, IMHO.

  7. #7
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Thanks Micron. That does look cleaner.

    I believe declaring the data type helps insure that the correct data type comes out of the function? Also it makes sure the correct amount of memory is reserved for the result? Those are just a couple of things I've read...I think.

    I may go with your second suggestion as you correctly assumed, there is a common default.

    Thanks again.

  8. #8
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    As far as error handling goes, I've been reading up on it and planned on adding some in today. Do you have a suggestion for this particular function? I've noticed reverse engineering seems to be the most efficient way of learning this stuff, for me anyway.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by lefty2cox View Post
    Thanks Micron. That does look cleaner.

    I believe declaring the data type helps insure that the correct data type comes out of the function? Yes, mostly. An error will result if you try to assign it any other type. You also know what type it's supposed to pass (remember the 100 lines). Also it makes sure the correct amount of memory is reserved for the result? Inconsequential. You could declare it as a double and that would allow any integer as well but using 4x the memory allocation (8 bytes vs 2). I doubt you would notice a difference. Those are just a couple of things I've read...I think.

    For this function,
    I may go with your second suggestion as you correctly assumed, there is a common default.
    Thanks again.
    Dealing with the errors that arise is a good way to learn, but risky if one is learning while doing mission critical stuff. Error handling is dealt with in the procedure that calls a routine or function IF there is no error handler in the called procedure. Actually, it will "go upstream" looking for a handler. The best way to handle errors is to prevent them, IMHO. If you can, create functions that will be reusable as much as possible. I will post one or two suggestions later. As I write this, something came up & I gotta run.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Quote Originally Posted by June7 View Post
    I have not. Thank you for the resource. Looks like there's a lot of good stuff on that site.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I can't speak for others, but IMO, Allen Browne and Dirk Golgar are a couple of Access gods (with small g). You should bookmark that site June7 gave you.

    As far as error handling goes, I've been reading up on it and planned on adding some in today. Do you have a suggestion for this particular function?
    In general, you have to try and adopt a somewhat 'computer illiterate' approach and apply it as stringently as the case requires. F'rinstance, I used to have the function of Beta tester, trying to break an application. Common sense would dictate that a number is expected in a control, but what happens if you enter 'dog'? Or what if the entered start date is after the end date? Stuff like that.
    As for your particular situation, what comes to mind is 1) ensuring data that is going to be passed is of the right type and 2) ensuring controls that can have Null or empty strings as values are dealt with. For #1, I think validation in the property sheet is the simplest when you can use it. Or you could use a function that takes two arguments; the control and the expected data type, but why complicate things? For#2:

    Code:
    Function IsNullEmpty(Ctl as Control) As Boolean
    IsNullEmpty = False
    If IsNull(ctl) or ctl="" Then IsNullEmpty = True
    End Function
    Most likely you'd call this on a button click for unbound controls (and you're not duplicating this type of validation in the property sheet).

    Most times, I'll iterate through a form's controls and look at the tag property. If it says (no quotes) "Reqd" (NOT Required) then I'll pass the control to the function to test it and if it returns True, add it to a list then msgbox the list. This way, I can list all the controls at once rather than make them fill in 5 missing ones, one at a time, with each click of the button they're trying to save the form with. Some will disagree with this method, implying it takes a long time to go through them all. My use of this has demonstrated that even on an old Dell with XP, a hundred controls takes a second or so. However, there may be valid reasons that haven't been pointed out to me yet, beyond personal preference or maybe having to redirect error handling, so at the end I included a way to skip the only two controls I know of that you'd want to bypass. This is air code, so there might be a boo-boo or two in it.

    Code:
    Private Sub cmdSave_Click()
    Dim ctl As Control
    Dim strlList As String, strMsg As String
    
    strList=""
    strMsg = "Please supply value(s) for: " & vbCrLf
    On Error Resume Next 'need to redirect error handling for when ctl type has no tag property
    For Each ctl In Me.Controls 'as long as this code resides on the form you're checking
      If ctl.Tag = "Reqd" Then
        If IsNullEmpty (ctl) = True Then
          strList = "- " & ctl.Controls(0).Caption & vbCrLf 'get the ATTACHED label text. If not attached, cannot use as written
        End If
      End If
    Next
    
    If strList <> "" Then
       msgbox (strMsg & strList)
       Exit Sub 'don't move on to form save or whatever
    End If
    
    End Sub
    To bypass labels and command buttons:
    For Each ctl In Me.Controls
    If ctl.ControlType = acLabel Or ctl.ControlType = acCommandButton Then GoTo bypassControl
    bypassControl: goes just before the Next statement

  13. #13
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Thanks Micron.

    I booked marked that page.

    I think I'm getting it now. When I say I'm new at this I mean I started learning last week....literally. But you did a good job explaining. So I feel like I understand both pieces of code you show.

    At the risk of sounding naive, air code, What does that term mean?

  14. #14
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    At the risk of sounding naive, air code, What does that term mean?
    You know what they say - the only dumb question is the one that doesn't get asked.
    Air code means it's from my head/off the cuff. It's untested. I don't know about anyone else, but I only use Notepad to write it - usually not even the vb editor.

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

Similar Threads

  1. Replies: 15
    Last Post: 07-09-2015, 01:39 AM
  2. Query Criteria Function and Operator List?
    By samanthaM in forum Access
    Replies: 2
    Last Post: 06-03-2012, 04:52 PM
  3. Replies: 2
    Last Post: 12-22-2010, 09:57 AM
  4. Replies: 2
    Last Post: 05-18-2010, 01:43 PM
  5. Replies: 2
    Last Post: 08-04-2008, 04:16 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