Subform RecordSource Error 3008

[patjones]

What do you mean format the date strings as dates? There shouldn't be any conversion necessary...the value from the text box gets inserted in your SQL between the #'s and that's it.

[thelonelyghost]

What do you mean format the date strings as dates? There shouldn't be any conversion necessary...the value from the text box gets inserted in your SQL between the #'s and that's it.

In answer to your question...
NeoPa:

Many experts will tell you that all you need do for dates is surround them with #s. This is not actually true, though it will seem so in most situations (especially in the US).

I'm just trying to cover all of my bases. I'm not sure why I need it exactly but it seems NeoPa says I do.

On a different note, I solved the Runtime Error: 2001. Access was panicking because I had the expression Mileage <= '0' instead of Mileage <= 0

[patjones]

Well yes, you have to put #'s around them...but what I'm saying is that you should not need to do any conversion of the text box values, such as CDate(Me.dtMinD ateR.Value), before using the values in your SQL. You said you created a function to format the date strings as dates, which confused me.

Anyway, if it's all working now, never mind. Where do we stand at this point?

[thelonelyghost]

Well yes, you have to put #'s around them...but what I'm saying is that you should not need to do any conversion of the text box values, such as CDate(Me.dtMinD ateR.Value), before using the values in your SQL. You said you created a function to format the date strings as dates, which confused me.

Anyway, if it's all working now, never mind. Where do we stand at this point?

At this point the filter is working, albeit only for a few fields. I can change the sort order and can view the results, but the form has finally progressed beyond pre-alpha-stages. I'm doing some testing to see what fields DON'T work by

• Inputting data into one field at a time
• testing all functionality of said field
• Manually correcting the SQL when a field's data produces Error 2001

Now that I better understand the debugging process for this, I think I have a grasp on it. I'll post back if I have any issues I can't solve. Thank you SOOOOOOOOOO very much guys! You've been invaluable resources of info for a number of reasons. (n_n)

[NeoPa]

thelonelyghost: @NeoPa: I read the article and I got confused by a part of it. If I've already got a date in the form MM/DD/YYYY, would I use Format() like this? Format(Me.dtMax DateR, "MM/DD/YYYY")

That depends. Controls are not actually dates as such, even though they can contain data that represents dates. If you absolutely know that it will always be formatted that way, then you needn't format it again. The formatting is important for date/time data which, although it may display in various ways, is actually stored as a real number. The important thing at the end of it is just that the string of characters in your SQL string is formatted correctly. Does that make sense?

thelonelyghost: @NeoPa: Also, I'll try to double-post more. ;P Everywhere else I've been people have been double-post nazis and banned users for doing it enough. Apparently this is not the case here.

Some common sense should be used of course, but generally double-posting is not seen as too serious if it reflects the flow of your question. To be honest, retrospective edits aren't the worst thing in the world either. I do it myself quite a bit. The only issue to worry about is that sometimes another member of the thread will not realise you have updated the thread as only new posts show as an update (either by notification or threads flagged in the lists). I'd hate to see one of your threads dropped by a helper simply because they weren't aware of any recent activity.

thelonelyghost: @NeoPa: Additionally, I'm still new to the VBA Debugging world so excuse that ignorance. I tried reading up on it but the guides I read didn't help too much. I just needed a place to start stepping through the code.

I had planned to post this (Debugging in VBA) earlier but I'm not sure I did now.

[thelonelyghost]

@NeoPa:

That depends. Controls are not actually dates as such, even though they can contain data that represents dates. If you absolutely know that it will always be formatted that way, then you needn't format it again. The formatting is important for date/time data which, although it may display in various ways, is actually stored as a real number. The important thing at the end of it is just that the string of characters in your SQL string is formatted correctly. Does that make sense?

I see what you mean, and I'll have to figure out a way to ensure it stays in the MM/DD/YYYY format on the form. I'm familiar with the date being stored as a real number on if the datatype is date/time, but as you said, it only matters if the SQL string is formatted correctly.

I had planned to post this (Debugging in VBA) earlier but I'm not sure I did now.

You did post that earlier. If not on this thread, then on one of the other ones I started. I read through that before and concluded (after little more than a glance) that it was a description of the debugging tool, not instructions on how to use it or the frame of mind needed to do the necessary debugging. I'm looking through it again now because that judgement seems a bit harsh when compared to the quality of your other guides. It's probably just that I missed something.

EDIT: I found an example. In the section about the Watch Pane it talks about the functionality existing, not about how to use it. This isn't the only example available either.

[NeoPa]

You're quite right. I'm afraid that the full scope of debugging seemed a little much. Covering all the bases could have taken just too long.

What I did do, or more precisely my intention was to, outline the framework available there so that anyone with any previous debugging experience could find themselves at home quite quickly. There are probably as many debugging techniques as there are developers, so explaining How to Debug was not something I felt was too appropriate. I have my particular way of doing things, but that may not suit all.

If you follow the Watch Pane link, then open this up in your own project, you will be able to use the Right-Click menu to see some of the items introduced. I would hope that this would give you a starting point from where you could be aware of, and explore, the facilities available to you.

If the whole concept is foreign to you though, I'm happy to answer specific questions related to the article. Feel free to create a related thread and direct me to it. I will help where I can and will probably add bits into the article as and when it seems appropriate.

[thelonelyghost]

Project update: After much debugging and more help from the community, I've come to realize that my use of filters would not affect the network traffic (bandwidth) the same as if I used recordsource only. After successfully debugging the SQL, I injected it into the WHERE section of a SQL SELECT statement and set that as the recordsource. On startup, the form loads 5-10 seconds faster when referencing a table on the local machine, leading me to believe it'll be that much faster in a client/server setting.

I will post my sourcecode w/ documentation here if I get a chance. Doing this should ensure others won't have to stumble nearly as much as I did. Thanks everybody!

[patjones]

Project update: After much debugging and more help from the community, I've come to realize that my use of filters would not affect the network traffic (bandwidth) the same as if I used recordsource only. After successfully debugging the SQL, I injected it into the WHERE section of a SQL SELECT statement and set that as the recordsource. On startup, the form loads 5-10 seconds faster when referencing a table on the local machine, leading me to believe it'll be that much faster in a client/server setting.

I will post my sourcecode w/ documentation here if I get a chance. Doing this should ensure others won't have to stumble nearly as much as I did. Thanks everybody!

I'm really glad to hear it. You'll find that it cuts down on the amount of coding that you have to do as well. Definitely post your final results when you have a chance!

Pat

[thelonelyghost]

Source Code

I finally have a compilation of the source code for the application I've been using. You do have to edit some of the code to fit to your situation, but that info lies between the labels "Begin User Settings" and "End User Settings", located at the beginning of SQLFilter() and BuildSQLStr(). Let me know if anyone has any questions, comments, or suggestions for the code. I'm more than willing to help!

Note: The Access 2000 Replace() workaround is purely optional. If Replace() works on your version of Access, fix its application on line 15 of SQLFilter().

Disclaimer: This disclaimer is not to be modified or removed except when the source appears in any Visual Basic console. The following code is not to be sold at any price and will be distributed freely and without discrimination. Please refer to the latest copy of the General Public License (currently version 3.0) at http://www.gnu.org/copyleft/gpl.html for further disclaimer.

"lib" Module:

Transformation function in module "lib" for inputs on form:

Public Function FormToSQL(strField As Variant, strValue As Variant, strDelim As Variant, strOperator As Variant) As String 
  
  If strValue = "''" Or strValue = "" Or IsNull(strValue) Then 
  Exit Function 
  Else 
    strValue = Trim(strValue) 
    If strValue Like "##/##/####" Then 'Normally determines if value is a date, but LIKE shouldn't be used with dates anyway. 
      strValue = "#" & strValue & "#" 
    Else: If Not IsNumeric(strValue) Then strValue = "'" & strValue & "'" 
    End If

'Combines inputs for 
      Select Case strOperator
	Case "EQ"
	  strOperator = " = "
	Case "GT"
	  strOperator = " >= "
	Case "LT"
	  strOperator = " <= "
	Case "LK"
	  strOperator = " LIKE "
	Case Else
	  Exit Function
      End Select

    FormToSQL = strDelim & "[" & strField & "]" & strOperator & strValue 
  
  End If 
  
End Function

.
Only needed if using Access 2000 (see above note):

'*********Access 2000 Replace() Workaround*********
Public Function StrReplace(strHaystack As String, strNeedle As String, strReplaceWith As String, intRepeat As Integer) As String

Dim init As Long, i As Integer

If Len(strHaystack) < 1 Or Len(strNeedle) < 1 Or intRepeat > 0 = False Then 'Failsafe for illegal inputs
    Exit Function
Else 'General declarations of variables
  init = InStr(strHaystack, strNeedle)
  i = intRepeat
End If

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

StrReplace = strHaystack

End Function

.
Form's Module:

Header:

Option Compare Database

'**********Begin User Settings**********
'total number of control boxes that are related to the fields being queried
Private Const numberOfInputs = 8
'**********End User Settings**********

Private aFld(1 To 2, 1 To numberOfInputs) As String, aVal(1 To numberOfInputs) As Variant, aOp(1 To numberOfInputs) As String
Private strTableName As String

Transforming a form's inputs into one string of SQL for filter (SQLFilter) or SELECT application (BuildSQLStr):

'************Creates Filtering criteria in SQL syntax************
Private Function SQLFilter() As String

Dim strSQLContent As String, strDelim As String

'Dim aFld(1 To numberOfInputs) As String, aVal(1 To numberOfInputs) As Variant, aOp(1 To numberOfInputs) As String

'******************Field Names, Control Sources, and Operators******************
'****************Begin User Settings****************
'Sets the associated table of all field names to strTableName by default
For i = 1 To numberOfInputs
  aFld(1, i) = strTableName
Next

'********Notes********
'aFld : name of the field in [tblReturnLog]
'aVal : value of the field to look for in [tblReturnLog]
' aOp : abbreviation of operator in SQL, i.e. "LK" = LIKE
'*********************
aFld(2, 1) = "OldLogNumber"
aVal(1) = Me.txtOldLogNumber
 aOp(1) = "LK"
aFld(2, 2) = "CTSLogNumber"
aVal(2) = Me.intCTSLogNumber
 aOp(2) = "LK"
aFld(2, 3) = "ReportSentTo"
aVal(3) = Me.txtReportSentTo
 aOp(3) = "LK"
aFld(2, 4) = "CustomerPartNumber"
aVal(4) = Me.txtCustomerPartNumber
 aOp(4) = "LK"
aFld(2, 5) = "DateReceived"
aVal(5) = Format(Me.dtMinDateR, "MM/DD/YYYY")
 aOp(5) = "GT"
aFld(2, 6) = "DateReceived"
aVal(6) = Format(Me.dtMaxDateR, "MM/DD/YYYY")
 aOp(6) = "LT"
aFld(2, 7) = "CompletionDate"
aVal(7) = Format(Me.dtMinDateClosed, "MM/DD/YYYY")
 aOp(7) = "GT"
aFld(2, 8) = "CompletionDate"
aVal(8) = Format(Me.dtMinDateClosed, "MM/DD/YYYY")
 aOp(8) = "LT"

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

'transforming form inputs into SQL code with subfunction
For i = 1 To numberOfInputs
  strSQLContent = strSQLContent & lib.FormToSQL(aFld(2, i), aVal(i), strDelim, aOp(i))
Next

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

End Function

'*********Builds SELECT statement in SQL*********
Private Function BuildSQLStr() As String

Dim strSQLFilter As Variant, strFieldNames As String

'*********Begin User Settings*********
strTableName = "tblReturnLog" 'Sets the name of the table to a global variable
'*********End User Settings*********


strSQLFilter = SQLFilter 'Optimization: calculates value of SQLFilter once

'Lists all field names and associated tables in SQL format.  Useful if JOIN is used.
For i = 1 To numberOfInputs
  strFieldNames = strFieldNames & "[" & aFld(1, i) & "].[" & aFld(2, i) & "], "
Next
strFieldNames = Left(strFieldNames, Len(strFieldNames) - 2)

'Overall: builds SELECT statement from User Settings
'Line 1: lists the specific name of each column/field to be referenced, opening up ability to allow JOIN
'Line 2: lists criteria to filter by, if there are any
BuildSQLStr = "SELECT " & strFieldNames & " FROM [" & strTableName & "]" & _
               IIf(Len(strSQLFilter) > 0, " WHERE " & strSQLFilter & ";", ";")

End Function

[patjones]

I am happy to see that you got it all sorted out; and thanks for posting your solution. Too often, people just never respond as to what worked or what didn't work, diminishing the thread's value.

One small suggestion I have is that you can easily combine GreaterThanAtta chedAnd, LessThanAttache dAnd, LikeAttachedAnd into one function. They are all the same except for the comparison operator, so I would simply make one function, put an argument in that allows the user to indicate what comparison they want to perform, and then pick the respective operator using a Case statement or whatever logic structure you prefer.

Pat

[thelonelyghost]

Successful testing resulted in me posting the revised code above. I also modified all dependencies on the old code to fit with the revision.

[thelonelyghost]

This leads into an idea for further revision. I'm looking at using a For-each loop in SQLFilter() for FormToSQL() and setting up three arrays (arrFieldName, arrValue, arrOperator). Does anyone know if this would actually optimize the code, or would it just look prettier?

EDIT: Just did some major code revision to carry out this idea and make it easier to implement JOINs in SELECT statement.