Debugging help. SQL/VBA fusion

[thelonelyghost]

Software: Access 2000
Operating System: Windows XP Professional SP3
Problem: Error 2001 based on filter being "ModelYear <= 2006".
Solution: Table's datatype was text when SQL expected number.

I'm in the middle of debugging an issue with some dynamic SQL code and I think I may have missed some important rule. When I try to use it, it pops up with a general syntax error (Runtime Error 2001). My error arises when I have the function Me.frmSubForm.F orm.Filter = SQLFilter even though SQLFilter = "ModelYear <= 2006". Here's the relevant function information:

Main function:

'Outputs string of SQL statement
Private Function SQLFilter() As String

Dim strSQLContent As String, strDelim As String

'detecting whether to use AND or OR
'based on button 'ANY' or 'ALL'
  strDelim = IIf(Me.btn2SearchSettingsAnyAll.Value = 1, " OR ", " AND ")

'transforming form inputs into SQL code with subfunctions
  strSQLContent = strSQLContent & lib.GreaterThanAttachAnd("ModelYear", Me.txtMinModelYear.Value, strDelim)
  strSQLContent = strSQLContent & lib.LessThanAttachAnd("ModelYear", Me.txtMaxModelYear.Value, strDelim)

'Clean up the concatenation of all of the fields into a SQL statement fragment
SQLFilter = lib.StrReplace(strSQLContent, strDelim, "", 1)

Sub-functions from the lib Module:
GreaterThanAtta chAnd():

'This code is meant to a string as part of a dynamic SQL statement.
'strField = field (i.e. 'strJusticeLeague')
'strValue = control (i.e. 'Me.strBatman')
'strDelim = 'AND' or 'OR'.

Public Function GreaterThanAttachAnd(strField As Variant, strValue As Variant, strDelim As Variant) As String

  If strValue = "''" Or strValue = "" Or IsNull(strValue) Then
  Exit Function
  Else
    strValue = Trim(strValue)
    If strValue Like "##/##/####" Then 'filters dates
      strValue = "#" & strValue & "#"
    Else: If IsNumeric(strValue) = False Then strValue = "'" & strValue & "'"
    End If
    GreaterThanAttachAnd = strDelim & strField & " >= " & strValue
  End If

End Function

LessThanAttachA nd():

'This code is meant to a string as part of a dynamic SQL statement.
'strField = field (i.e. 'strJusticeLeague')
'strValue = control (i.e. 'Me.strBatman')
'strDelim = 'AND' or 'OR'.

Public Function LessThanAttachAnd(strField As Variant, strValue As Variant, strDelim As Variant) As String

  If strValue = "''" Or strValue = "" Or IsNull(strValue) Then
  Exit Function
  Else
    strValue = Trim(strValue)
    If strValue Like "##/##/####" Then
      strValue = "#" & strValue & "#"
    Else: If IsNumeric(strValue) = False Then strValue = "'" & strValue & "'"
    End If
    LessThanAttachAnd = strDelim & strField & " <= " & strValue
  End If

End Function

StrReplace():

Public Function StrReplace(strHaystack As String, strNeedle As String, strReplaceWith As String, intRepeat As Integer) As String

Dim init As Long, i As Integer

'Failsafe for illegal inputs, then declarations after "Else "
If Len(strHaystack) < 1 Or Len(strNeedle) < 1 Or intRepeat > 0 = False Then
    Exit Function
Else
  init = InStr(strHaystack, strNeedle)
  i = IIf(IsNull(intRepeat), 99, intRepeat)
End If

Do While init > 0 And IIf(IsNull(intRepeat), 1 = 1, i > 0)
  strHaystack = Left(strHaystack, init - 1) & strReplaceWith & Right(strHaystack, Len(strHaystack) - Len(strNeedle) - init + 1)
  
  'Maintenance for While-loop
  i = IIf(intRepeat > 0, i - 1, i)
  init = InStr(strHaystack, strNeedle)
Loop

StrReplace = strHaystack

End Function

[NeoPa]

What's the error message and where does this line occur within the various pieces of code posted?

[NeoPa]

By the way, some general debugging tips with SQL and VBA are included indented below :

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

[thelonelyghost]

Sorry if this wasn't clear. It's error 2001, which I've found is Access's way of saying "General syntax error. FIX IT!" I went line-by-line and followed how each variable (such as strValue) changed along the way, and it all does it as I expected. I checked to see if the SQL made sense and it did to me, but I'm posting this to make sure I didn't miss some rule with setting a filter. An example of a rule I've overlooked in the past is comparing '29' with 29 proves difficult, so I made an exclusion rule for numbers and dates when applying quotes.

BTW, NeoPa, since I know you've been following my progress I thought you'd enjoy this news. I rewrote the copyrighted find function I talked about in earlier threads, debugged it, successfully tested it, and added functionality. It's posted above as the StrReplace() function.

EDIT: added error number and rearranged OP to be an easier read.

[thelonelyghost]

Also (watch me double-post!) I was already following your suggestion of posting the overall function's output for SQLFilter. On the last line of my OP (before I revised it), I said it was a string containing "ModelYear <= 2006", taken verbatim from the debugger.

The issue that prompts the SQL syntax error is trying to apply it to a filter (Me.frmSubForm.F orm.Filter = SQLFilter) or, if the filter isn't turned on yet, turning the filter on (Me.frmSubForm.F orm.FilterOn = True).

[NeoPa]

thelonelyghost: I was already following your suggestion of posting the overall function's output for SQLFilter. On the last line of my OP (before I revised it), I said it was a string containing "ModelYear <= 2006", taken verbatim from the debugger.

I'm not surprised, but I couldn't be sure. It never hurts to drop that bit in I find, even if the OP themselves is already aware of it.

I'll try to find where the problem is, and what the error message is from what you've posted already, but if you get a second dropping the line number and procedure of the line of code that it errors on and the actual error message (The message I find when I search for 2001 is "Invalid picture".) would be a help.

[NeoPa]

thelonelyghost: Sorry if this wasn't clear. It's error 2001, which I've found is Access's way of saying "General syntax error. FIX IT!" I went line-by-line and followed how each variable (such as strValue) changed along the way, and it all does it as I expected. I checked to see if the SQL made sense and it did to me, but I'm posting this to make sure I didn't miss some rule with setting a filter. An example of a rule I've overlooked in the past is comparing '29' with 29 proves difficult, so I made an exclusion rule for numbers and dates when applying quotes.

In your case there may well be a problem if your field [ModelYear] is a string. Otherwise I see no problem with that as a .Filter value. I would change it, if it were me, to :

[ModelYear]<=2006

but that is about readability and being explicit with code - not to fix any perceived error.

thelonelyghost: BTW, NeoPa, since I know you've been following my progress I thought you'd enjoy this news. I rewrote the copyrighted find function I talked about in earlier threads, debugged it, successfully tested it, and added functionality. It's posted above as the StrReplace() function.

Well done. I have a similar function myself. Not exactly the same requirement, but similar. I use the Replace() function heavily in that. I doubt that would work for your specific requirements though. Mine takes multiple pairs of parameters as well as the original string, and replaces the first of each pair with the second, as many times as it finds them.

[NeoPa]

I'm sorry. I looked through all your code but could find nothing that set the .Filter property, or even the .FilterOn one.

I've commented already as much as I can on the string you posted, otherwise I see no more I can do now.

[thelonelyghost]

I know there's no .Filter or .FilterOn in my code above because it's stand-alone for a command button. I didn't think it was too relevant but I'll add the code from that too. It was pretty much exactly as I described it and it works for everything else I've tested it on, so I don't see this as being the issue.

'Displays search results in subform
'based on partial SQL string contstructed with SQLFilter function
Private Sub btnSearch_Click()

Me.frmSubForm.Form.Filter = SQLFilter
Me.frmSubForm.Form.FilterOn = True
Me.frmSubForm.Form.OrderBy = Me.cmbGroupBy

'Me.frmSubForm.Form.RecordSource = BuildSQLStr

End Sub

The horrible thing is that this error comes up on line 5 of the above button's code if the filter is already on, whereas it's on line 6 if the filter is off. For further info on my error 2001, it says:

"[I]Runtime Error '2001':
You canceled the previous operation.[/I]"
Continue(greyed)    End       Debug       Help(greyed)

The fields for min and max model year, since I want to search it as a range or each one independently (hence using '>=' and '<=' instead of 'BETWEEN'), are text boxes with an input mask of 4 numerical characters. I just take the value of the field and input that into the SQL though so there shouldn't be a datatype issue, right? You saw my SQL output...

Did that help? It didn't do much for me. The exact same error in the past was caused by SQL syntax being off. You may be familiar with my issues with the dates and our talk about Format()? (linkback) This issue honestly has me stumped even worse than the last set of obstacles, mostly because the error is so general.

[NeoPa]

thelonelyghost: I know there's no .Filter or .FilterOn in my code above because it's stand-alone for a command button. I didn't think it was too relevant but I'll add the code from that too. It was pretty much exactly as I described it and it works for everything else I've tested it on, so I don't see this as being the issue.

That's probably true. However, it gives us, that haven't any direct access to your database, both context (without which it is very difficult to think of, let alone solve, your problem) and confirmation of other information you've given us. You'd be surprised at how often we are led up the garden path by information which fits the poster's understanding, yet which is simply wrong. Posted code gives us clues as to how much to rely on certain statements. As communication is 90+% of the issue when dealing over a web page, this is very important.

thelonelyghost: The horrible thing is that this error comes up on line 5 of the above button's code if the filter is already on, whereas it's on line 6 if the filter is off.

This makes perfect sense. If .FilterOn is true then the new filter value is applied immediately (line #5). If not, then it is only applied after it is set to True (Line #6).

thelonelyghost: For further info on my error 2001, it says:

"[I]Runtime Error '2001':
You canceled the previous operation.[/I]"
Continue(greyed)    End       Debug       Help(greyed)

Good news and bad news. The good news is that this information is very helpful. The bad is that it often indicates a corruption in your database. Not guaranteed, but it's a worrying indicator.

thelonelyghost: The fields for min and max model year, since I want to search it as a range or each one independently (hence using '>=' and '<=' instead of 'BETWEEN'), are text boxes with an input mask of 4 numerical characters. I just take the value of the field and input that into the SQL though so there shouldn't be a datatype issue, right? You saw my SQL output...

I saw your SQL output, but as in my previous post (#7) I don't know the datatype of your field [ModelYear]. As I said, if it's a string then the SQL would be wrong. The control that you're describing doesn't come into this at all. Entirely unrelated.

thelonelyghost: Did that help? It didn't do much for me. The exact same error in the past was caused by SQL syntax being off. You may be familiar with my issues with the dates and our talk about Format()? (linkback) This issue honestly has me stumped even worse than the last set of obstacles, mostly because the error is so general.

It certainly did.

Please note the bolded text in the quote. It would be a good idea to ignore this whole point in future as irrelevant. If someone asks for information, it is better to trust that they probably know why they want it, rather than trying to understand why. Not understanding certainly doesn't indicate it is not worth providing whatever's requested. It just gets in the way of positive communication, which is the most important individual element when dealing with someone across a forum web page.

My advice, after all that, is to check the type of the [ModelYear] field in your table. If it is not numeric then that could well be your problem. If it is numeric, then I suggest you look at Compacting/Repairing your database. This may be recoverable. If not, then you may need to consider recreating any objects that are not recovered. Never good news I know, but check out [ModelYear] first. It may be less drastic than that.

[thelonelyghost]

Possible further insight: The same exact error as above happens when I choose something from the Engine combo box (viewed as "3.9L", as a string). Debugging showed that it was identifying it by the ID number from the lookup table. Searching by the associated content (i.e. content = "3.9L" versus ID = "12") from the lookup table fixes this issue.

[thelonelyghost]

Just saw the new post. I looked at the table I'm filtering and slapped my forehead. It was as simple as datatype mismatch, like you suggested. I could have sworn earlier that it was a number datatype, but in fact it was a text datatype. For a while I didn't realize one could put an input mask on anything other than text fields so... :) IT'S SOLVED!

Apologies for the needless info, I thought it helped explain why I didn't include it in the information to begin with. I'm well aware that most people on this site know what they're doing when they ask for things such as your request for error description and it was in no way meant to insult you.

Side note, I do appreciate the instructions on local forum ettiquette since, I've very recently and shockingly realized, not every forum has the same basic rules. I'm just trying to seamlessly fit into the community here while simultaneously learning more about this software.

[NeoPa]

As you say, I've caught a number of your posts, so I know better than to think you're not trying to work well with us. I just felt a few pointers may help due to the apparent difficulties in this particular thread. I never felt insulted by the way. That would be more likely to trigger a deafening silence than pointers on how better to proceed :D

This sounds like a difficult one in truth. I remember when I first fell over that sort of confusion. It was actually where a field was set up as a ComboBox in the table itself. Confused me mightily.

Anyway, all resolved now, and I can happily say that I'm looking forward to more of your questions. You always do your best to explain them well and work with us to resolve the issue.