VBA Function output

[thelonelyghost]

Basic Information
Software: Microsoft Access 2000
OS: Microsoft Windows XP Professional SP3

I've been trying to make some of the behind-the-scenes coding for one of my forms available in a module such as creating a query from certain inputs:

Function EqualsAttachAnd(sField As String, sValue As String, strDelim As String)
 
  If sValue = "''" Or sValue = "" Then Exit Function
  Else
    sValue = Trim(sValue)
    strSQLContent = strSQLContent & strDelim & sField & "= '" & sValue & "'"
  End If
 
End Function

Where strDelim is AND or OR. The problem is that it currently outputs to a string strSQLContent that is not declared in the module I have this library of functions defined in. It is, however, defined in the individual form. Is there some way for this function to just return the variable strSQLContent from the library as a string. Example:

Call lib.EqualsAttachAnd("Customer",Me.txtCustomer," AND ")
 
'returns this string:
" AND [Customer] = 'Microsoft'"

So far I've gotten that I should append the function declaration with As String but is that really all it takes? I have no way to test the code since I'm working on only one part of it at the moment. Of the few results I've found, I came across THIS which only served to confuse me.

How would I change the code I have? I'm already familiar with Java and how to do it like that, but I'm just beginning with VBA. In Java you simply put Return variable, in this case strSQLContent.

[patjones]

The typical usage for a function is to assign the function the value. For example:

Private Function GetSum(lngA As Long, lngB As Long) As Long
   GetSum = lngA + lngB
End Function

And when you call the function:

Dim lngSum As Long
lngSum = GetSum(5.6, 3.4)
 

I made my function Private, meaning it is accessible only in the module in which I defined it. You can use the Public keyword instead if you want other modules to have access to it.

Pat

[thelonelyghost]

So all I would need to do is change my code to:

Function EqualsAttachAnd(sField As String, sValue As String, strDelim As String) As String
 
  If sValue = "''" Or sValue = "" Then Exit Function 
  Else 
    sValue = Trim(sValue) 
    EqualsAttachAnd = strDelim & sField & "= '" & sValue & "'" 
  End If 
 
End Function

Correct me if I'm wrong but this means I should recode my call statement:

'Before:
Call lib.EqualsAttachAnd("Customer",Me.txtCustomer," AND ")
 
'After:
strSQLContent = strSQLContent & lib.EqualsAttachAnd("Customer",Me.txtCustomer," AND ")

This should work?

[patjones]

I do believe that should work. Are you doing this in a class module, and hence the reason for the "lib" part of the call? Let me know if it works.

Pat

[thelonelyghost]

@zepphead80
I just used your suggestion and it doesn't seem to be working. I'm not sure if it's because what you said didn't fit the situation or if another error is masking it. I get an error message saying:

The expression On Click you entered as the event property setting produced the following error:
Invalid outside procedure.
*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.

It couldn't be more vague. I did use the [Event Procedure] selection for the On Click event, which I then put this code into it:

Private Sub btnSearch_Click()
 
'Call SQL SELECT-statement to subform
Me.frmTEMP2.Form.RecordSource = BuildSQLStr
'DoCmd.RunSQL
 
End Sub

The relevant code attached to this is:

Dim strDelim As String
  strDelim = " AND "
Dim strSQLContent As String
  SQLContent = ""
 
 
...
 
 
Public Function BuildSQLStr()
 
'Setting Form inputs to variables for easier reference, trimming space characters from      '
'before/after string, and Setting all empty inputs to zero-length instead of null.          '
SQLContent = lib.EqualsAttachAnd("OldLogNumber", Me.txtOldLogNumber.Value, strDelim) & lib.EqualsAttachAnd("CTSLogNumber", Me.intCTSLogNumber.Value, strDelim) & lib.LikeAttachAnd("ReportSentTo", Me.txtReportSentTo.Value, strDelim) & lib.EqualsAttachAnd("CustomerPartNumber", Me.txtCustomerPartNumber.Value, strDelim) & lib.GreaterThanAttachAnd("DateReceived", Me.dtMinDateR.Value, strDelim) & lib.LessThanAttachAnd("DateReceived", Me.dtMaxDateR.Value, strDelim) & lib.GreaterThanAttachAnd("CompletionDate", Me.dtMinDateClosed.Value, strDelim) & lib.LessThanAttachAnd("CompletionDate", Me.dtMaxDateClosed.Value, strDelim) & lib.LikeAttachAnd("PersonAssigned", Me.txtPersonAssigned.Value, strDelim) & lib.LikeAttachAnd("CTSAssemblyPlant", Me.txtCTSAssemblyPlant.Value, strDelim) & lib.EqualsAttachAnd("CustomerPartTracker", Me.txtCustomerPartTracker.Value, strDelim)
SQLContent = SQLContent & lib.EqualsAttachAnd("PartNumber", Me.txtPartNumber.Value, strDelim) & lib.EqualsAttachAnd("ReturnType", Me.txtReturnType.Value, strDelim) & lib.EqualsAttachAnd("ProblemType", Me.intProblemType.Value, strDelim)
SQLContent = SQLContent & lib.EqualsAttachAnd("SensorType", Me.cmbSensorType.Value, strDelim) & lib.EqualsAttachAnd("Series", Me.txtSeries.Value, strDelim) & lib.LikeAttachAnd("FaultCode", "* " & Me.txtFaultCode.Value, strDelim) & lib.EqualsAttachAnd("DateCode", Me.txtDateCode.Value, strDelim) & lib.EqualsAttachAnd("Make", Me.intMake.Value, strDelim) & lib.EqualsAttachAnd("Model", Me.intModel.Value, strDelim) & lib.GreaterThanAttachAnd("ModelYear", Me.txtMinModelYear.Value, strDelim) & lib.LessThanAttachAnd("ModelYear", Me.txtMaxModelYear.Value, strDelim) & lib.EqualsAttachAnd("Engine", Me.cmbEngine.Value, strDelim) & lib.EqualsAttachAnd("VIN", Me.txtVIN.Value, strDelim) & lib.GreaterThanAttachAnd("Mileage", Me.intMinMileage.Value, strDelim) & lib.LessThanAttachAnd("Mileage", Me.intMaxMileage.Value, strDelim) & lib.EqualsAttachAnd("MileageType", Me.cmbMileageType.Value, strDelim)
SQLContent = SQLContent & lib.LikeAttachAnd("FailureMode/Complaint-Detailed", Me.memoFailureModeComplaintDetailed.Value, strDelim) & lib.LikeAttachAnd("FailureMode/Complaint-Simple", Me.txtFailureModeComplaintSimple.Value, strDelim) & lib.LikeAttachAnd("CTSAnalysis", Me.memoCTSAnalysis.Value, strDelim) & lib.LikeAttachAnd("CTSFindings", Me.memoCTSFindings.Value, strDelim) & lib.LikeAttachAnd("Comments", Me.memoComments.Value, strDelim) & lib.EqualsAttachAnd("BOMNumber", Me.txtBOMNumber.Value, strDelim) & lib.EqualsAttachAnd("CustomerLocation", Me.cmbCustomerLocation.Value, strDelim) & lib.LikeAttachAnd("Customer", Me.txtCustomer.Value, strDelim) & lib.EqualsAttachAnd("CorrectiveActionNumber", Me.txtCorrectiveActionNumber.Value, strDelim) & lib.LikeAttachAnd("MiscIDNumber", "* " & Me.memoMiscIDNumber.Value, strDelim) & lib.LikeAttachAnd("Misc", "* " & Me.memoMisc.Value, strDelim)
 
'Clean up the concatenation of all of the fields into a SQL statement
strSQLContent = Replace(strSQLContent, strDelim, "", 0, 1)
 
'Set results to SELECT statement that can be of use
BuildSQLStr = "SELECT * FROM [Return Log] WHERE " & SQLContent & " ORDER BY [Return Log].[" & Me.cmbGroupBy.Value & "]"
 
End Function

From [lib] Class Module:

Public Function EqualsAttachAnd(strField As String, strValue As String, strDelim As String) As String
 
  If strValue = "''" Or strValue = "" Then Exit Function
  Else
    strValue = Trim(strValue)
    strSQLContent = strSQLContent & strDelim & strField & "= '" & strValue & "'"
  End If
 
End Function

Public Function GreaterThanAttachAnd(strField As String, strValue As String, strDelim As String) As String
 
  If strValue = "''" Or strValue = "" Then Exit Function
  Else
    strValue = Trim(strValue)
    strSQLContent = strSQLContent & strDelim & strField & ">= '" & strValue & "'"
  End If
 
End Function

Public Function LessThanAttachAnd(strField As String, strValue As String, strDelim As String) As String
 
  If strValue = "''" Or strValue = "" Then Exit Function
  Else
    strValue = Trim(strValue)
    strSQLContent = strSQLContent & strDelim & strField & "<= '" & strValue & "'"
  End If
 
End Function

Public Function LikeAttachAnd(strField As String, strValue As String, strDelim As String) As String
 
  If strValue = "''" Or strValue = "" Then Exit Function
  Else
    strValue = Trim(strValue)
    strSQLContent = strSQLContent & strDelim & strField & " LIKE '" & strValue & "*'"
  End If
 
End Function

I'm not sure if it's just an error in my code or if it's some sort of permissions ritual I forgot to go through. Any ideas? Until I can get a more substantial error message I don't have any clue what to do.

EDIT: I tried another function in [lib] called ClearForm() and it fails as well, leading me to believe it's an issue with permissions. Is that correct?

[gershwyn]

I believe the error message is complaining about these lines:

Dim strDelim As String
  strDelim = " AND "
Dim strSQLContent As String
  SQLContent = ""

You can't assign a value to a variable like that, outside of a function or sub-routine. If strDelim is a value that you use often throughout the code and won't change, you can declare it as constant (use the Public keyword instead if you need to access it outside this module):

Private Const strDelim as String = " AND "

Setting strSQLContent to an empty string isn't necessary in VBA. It's going to start out that way anyway the first time you use it in a given scope. Personally, I think it's a good habit to be in, but you'd need to do it at the beginning of each sub/function.

[patjones]

Well one of the things that I see immediately, which alone may or may not fix the situation, but should be corrected, is that you are not assigning the results of the function calls properly. For instance, you should have:

Public Function EqualsAttachAnd(strField As String, strValue As String, strDelim As String) As String
 
  If strValue = "''" Or strValue = "" Then Exit Function
  Else
    strValue = Trim(strValue)
    EqualsAttachAnd = strDelim & strField & "= '" & strValue & "'"
  End If
 
End Function

If you don't have EqualsAttachAnd = somewhere in the function definition, the function will exit with nothing assigned to it, and then trying to reference the function as though it has a value could produce an error.

Now, for the overall picture...I don't have a problem with putting functions in class modules and calling them in the manner that you are. In this situation I don't think it's quite what you want to do, even if it does end up working eventually.

Usually in Access when we have a bound form and want to use code to set it's recordsource after something like a button click occurring, we'd do something like:

Private Sub btn_Click()
 
Dim strSQL As String
strSQL = "SELECT some_fields FROM some_table WHERE some_condition = TRUE"
 
Me.Recordsource = strSQL
 
End Sub

The logic of this could be more complicated depending on your specific situation, but generally this is all you need. Yes, you will get some unwieldy looking SQL, but in my opinion that's just the nature of it. Another way that this is often approached is to define the form's recordsource without the WHERE clause of the SQL:

...
 
strSQL = "SELECT some_fields FROM some_table"
Me.Recordsource = strSQL
 
...

and then later on (after some event has occurred on the form, say) apply the filter which restricts the recordsource to a certain subset of records:

...
 
Dim strFilter As String
strFilter = "some_field = some_value"
Me.Filter = strFilter
Me.FilterOn = True
 
...

It is basically the WHERE clause without the WHERE. For example, if I were trying to restrict my form's records to only those employees who work in the department I select from a combo box on the form:

strFilter = "empl_dept = " & Me.cboDeptSelected

So, you don't need to do it using the filter method, but at the very least I'd suggest that you simplify everything to the three lines that I laid out at the beginning of the post.

Pat

[thelonelyghost]

Dear Lord!! I'm still having issues with this code but this time it's not liking the use of Null in the concatenation of the EqualsAttachAnd, etc.

Public Function BuildSQLStr()
 
Dim strSQLContent As String
 
'Setting Form inputs to variables for easier reference, trimming space characters from      '
'before/after string, and Setting all empty inputs to zero-length instead of null.          '
strSQLContent = lib.EqualsAttachAnd("OldLogNumber", Me.txtOldLogNumber.Value, strDelim)
strSQLContent = strSQLContent & lib.EqualsAttachAnd("CTSLogNumber", Me.intCTSLogNumber.Value, strDelim)
strSQLContent = strSQLContent & lib.LikeAttachAnd("ReportSentTo", Me.txtReportSentTo.Value, strDelim)
strSQLContent = strSQLContent & lib.EqualsAttachAnd("CustomerPartNumber", Me.txtCustomerPartNumber.Value, strDelim)
strSQLContent = strSQLContent & lib.GreaterThanAttachAnd("DateReceived", Me.dtMinDateR.Value, strDelim)
strSQLContent = strSQLContent & lib.LessThanAttachAnd("DateReceived", Me.dtMaxDateR.Value, strDelim)
strSQLContent = strSQLContent & lib.GreaterThanAttachAnd("CompletionDate", Me.dtMinDateClosed.Value, strDelim)
strSQLContent = strSQLContent & lib.LessThanAttachAnd("CompletionDate", Me.dtMaxDateClosed.Value, strDelim)
strSQLContent = strSQLContent & lib.LikeAttachAnd("PersonAssigned", Me.txtPersonAssigned.Value, strDelim)
strSQLContent = strSQLContent & lib.LikeAttachAnd("CTSAssemblyPlant", Me.txtCTSAssemblyPlant.Value, strDelim)
strSQLContent = strSQLContent & lib.EqualsAttachAnd("CustomerPartTracker", Me.txtCustomerPartTracker.Value, strDelim)
strSQLContent = strSQLContent & lib.EqualsAttachAnd("PartNumber", Me.txtPartNumber.Value, strDelim)
strSQLContent = strSQLContent & lib.EqualsAttachAnd("ReturnType", Me.txtReturnType.Value, strDelim)
strSQLContent = strSQLContent & lib.EqualsAttachAnd("ProblemType", Me.intProblemType.Value, strDelim)
strSQLContent = strSQLContent & lib.EqualsAttachAnd("SensorType", Me.cmbSensorType.Value, strDelim)
strSQLContent = strSQLContent & lib.EqualsAttachAnd("Series", Me.txtSeries.Value, strDelim)
strSQLContent = strSQLContent & lib.LikeAttachAnd("FaultCode", "* " & Me.txtFaultCode.Value, strDelim)
strSQLContent = strSQLContent & lib.EqualsAttachAnd("DateCode", Me.txtDateCode.Value, strDelim)
strSQLContent = strSQLContent & lib.EqualsAttachAnd("Make", Me.intMake.Value, strDelim)
strSQLContent = strSQLContent & lib.EqualsAttachAnd("Model", Me.intModel.Value, strDelim)
strSQLContent = strSQLContent & lib.GreaterThanAttachAnd("ModelYear", Me.txtMinModelYear.Value, strDelim)
strSQLContent = strSQLContent & lib.LessThanAttachAnd("ModelYear", Me.txtMaxModelYear.Value, strDelim)
strSQLContent = strSQLContent & lib.EqualsAttachAnd("Engine", Me.cmbEngine.Value, strDelim)
strSQLContent = strSQLContent & lib.EqualsAttachAnd("VIN", Me.txtVIN.Value, strDelim)
strSQLContent = strSQLContent & lib.GreaterThanAttachAnd("Mileage", Me.intMinMileage.Value, strDelim)
strSQLContent = strSQLContent & lib.LessThanAttachAnd("Mileage", Me.intMaxMileage.Value, strDelim)
strSQLContent = strSQLContent & lib.EqualsAttachAnd("MileageType", Me.cmbMileageType.Value, strDelim)
strSQLContent = strSQLContent & lib.LikeAttachAnd("FailureMode/Complaint-Detailed", Me.memoFailureModeComplaintDetailed.Value, strDelim)
strSQLContent = strSQLContent & lib.LikeAttachAnd("FailureMode/Complaint-Simple", Me.txtFailureModeComplaintSimple.Value, strDelim)
strSQLContent = strSQLContent & lib.LikeAttachAnd("CTSAnalysis", Me.memoCTSAnalysis.Value, strDelim)
strSQLContent = strSQLContent & lib.LikeAttachAnd("CTSFindings", Me.memoCTSFindings.Value, strDelim)
strSQLContent = strSQLContent & lib.LikeAttachAnd("Comments", Me.memoComments.Value, strDelim)
strSQLContent = strSQLContent & lib.EqualsAttachAnd("BOMNumber", Me.txtBOMNumber.Value, strDelim)
strSQLContent = strSQLContent & lib.EqualsAttachAnd("CustomerLocation", Me.cmbCustomerLocation.Value, strDelim)
strSQLContent = strSQLContent & lib.LikeAttachAnd("Customer", Me.txtCustomer.Value, strDelim)
strSQLContent = strSQLContent & lib.EqualsAttachAnd("CorrectiveActionNumber", Me.txtCorrectiveActionNumber.Value, strDelim)
strSQLContent = strSQLContent & lib.LikeAttachAnd("MiscIDNumber", "* " & Me.memoMiscIDNumber.Value, strDelim)
strSQLContent = strSQLContent & lib.LikeAttachAnd("Misc", "* " & Me.memoMisc.Value, strDelim)
 
'Clean up the concatenation of all of the fields into a SQL statement
strSQLContent = Replace(strSQLContent, strDelim, "", 0, 1)
 
'Set results to SELECT statement that can be of use
BuildSQLStr = "SELECT * FROM [Return Log] WHERE " & strSQLContent & " ORDER BY [Return Log].[" & Me.cmbGroupBy.Value & "]"
 
End Function

It starts on line 8 of the above code. I have concluded that it's one of two things: (1) this type of function by nature doesn't like null values, or (2) the function's internal mechanism to handle null values is buggy. The first conclusion would lead me to believe there are some permissions issues that I've overlooked.

As one of the experts on this site said, "An expert is someone who's willing to learn despite their ignorance" ... or something to that effect.

I realize the conversation has veered away from the original post almost completely, but I really do appreciate the help. Should I just start a new thread to solve these debugging issues or is everyone okay with it remaining here?

EDIT: I forgot to mention, I used both of your suggestions and it seemed to get me past the aforementioned vague error message, but threw me into another one. Microsoft is awesome... (-_-)

[NeoPa]

I think we can leave it here for the time-being at least.

Have you looked at Debugging in VBA?

Is your function declared as As String? If so, then string variables cannot contain Null values. If you need to handle Null values then you should use As Variant instead.

[thelonelyghost]

@NeoPa
Actually I was just looking into that. I decided to finally look at your filter form you uploaded as a reference HERE and tried checking for null before using the function (rather than inside the function like I've been doing) and it seems to get past it the issue I've been running into. I'll try your variant suggestion since I do want to try to keep this as much a part of a module as possible. I also thought that setting the control as a string converts the null value into a zero-length string automatically. If not, (out of curiosity) is there any way to convert it? or would it be more trouble than it's worth?

EDIT: setting the type as variant instead of string worked like a charm. Now I don't need all of those repetitive if-statements cluttering up my BuildSQLStr function! The debugger is yelling at me for the Replace() function I called by saying it doesn't exist. Back to the documentation!

[thelonelyghost]

EDIT: Nevermind on this post, I'll just post a new thread if I can't find one that's similar. NEW THREAD

[NeoPa]

Replace() should be found in the VBA library.

[thelonelyghost]

@NeoPa
Ah but I *was* using the VBA function Replace(), and it was in syntax! It ran into an error saying Access didn't know what function that was. After some research I found out that it's a defeciency with some versions of Access 2000. No worries though, I found a workaround that works.

If you want to see the research for yourself, google "Replace() Access 2000"

[NeoPa]

Were you using it in your SQL by any chance? If so, then that is technically not VBA code. The VBA code creates the SQL string, which is then passed to the SQL interpreter for execution. This is a confusing area for many people, particularly because some effort has gone into making available many (most) of the functions that are available in the VBA library. I know of no way that you can add library references to make them available to the SQL interpreter I'm afraid.

This level of redirection is also the cause of many confusions and difficulties when debugging such VBA -> String -> SQL work.

[thelonelyghost]

@NeoPa
No it wasn't within the SQL code. My code currently is:

'Outputs string of SQL statement
Public Function BuildSQLStr()
 
Dim strSQLContent As String
  strSQLContent = ""
 
'detecting whether to use AND or OR
'based on button 'ANY' or 'ALL'
Dim strDelim As String
  If Me.btn2SearchSettingsAnyAll.Value = 1 Then
    strDelim = " OR "
  Else: If Me.btn2SearchSettingsAnyAll.Value = 2 _
    Then strDelim = " AND "
  End If
 
'transforming form inputs into SQL code with subfunctions
' PLACEHOLDER: various Equals-, Like-, GreaterThan-, and 
' LessThanAttachAnd functions for each field on my form,
' concatenating into a string, strSQLContent
 
'Clean up the concatenation of all of the fields into a SQL statement fragment
'begins with 'WHERE'
strSQLContent = lib.SQLFixLeadDelim(strSQLContent, strDelim, "WHERE ")
 
'Outputs results to SELECT statement that can be of use
BuildSQLStr = "SELECT * FROM [tblReturnLog] " & strSQLContent & " ORDER BY [tblReturnLog].[" & Me.cmbGroupBy.Value & "]"
 
End Function

If you look on line 23 of the code, that's where the old replace function was located. I replaced it with my own that is tailored to my situation. The location of this is very much in the midst of the other VBA code. The new replace function is a modification of a workaround I found using google, authored by Alden Streeter.

It has three inputs where you have the string to replace within (strInString), the string to search for (strFindStr), and the string to replace it with (strReplaceStr). The (unmodified) code essentially searches for the first instance of strFindStr, writes the output (FindAndReplace) based on strInString until the first instance of strFindStr, appends strReplaceStr to FindAndReplace, appends the rest of strInString after strFindStr to FindAndReplace, then finally sets strInString to FindAndReplace. It loops this sequence until it doesn't find an instance of strFindStr. My modification merely took out that loop.

Sorry, I would have posted the code, but it's apparently copywritten and part of the agreement is not to distribute any modified version of the code unless it's part of an application.

[NeoPa]

Sorry. I wasn't trying to imply you were likely to have been confused by that issue. I was simply trying to process the information I had, and come up with the most reasonable interpretation. In this case I was in error. It doesn't surprise me greatly as I've caught a number of your posts already and, though you're new to the site, you're clearly no newbie.

My Google search, duplicating that which you posted, also led me to believe that this issue only occurred within DAO (essentially Jet SQL). Otherwise I was unable to find an explanation. It always worked for me. Sorry.

BTW. Good for you not posting anything you feel is copyrighted.

[thelonelyghost]

@NeoPa
I understand now. Thinking out loud, if you will. I also see that you understood more of the research than I did. I took it to mean that some versions of Access 2000 had issues with the Replace() function. In the interest of having a wider base of users (all users with Access 2000 and above), I defined a function that wasn't buggy.

As for my newbie status on these forums, I am a newbie with Access; I am not a newbie to programming nor am I a newbie to forum etiquette. Thanks to these forums complimenting my learning style, I'm quickly learning more about Access, VBA, and SQL. Working with this software would be a LOT harder without some sort of usergroup like this, so it's very much appreciated!

Although I personally have no qualms with passing along copyrighted materials and am a hardcore fan of the open source ideals, I don't want to bring any rain of legal action down on your host or the site itself. That above all else is why I didn't post the code.

[NeoPa]

It sounds like you'll fit in here very well thank you :) All good sentiments.