Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2012
    Posts
    24

    Unanswered: Need help with modifying VBA Select statement

    I have the following code (behind a form tab) to locate records that match 1 or more of the parameters in the AddToWhere lines. I need to modify the AddToWhere [Look for CR] to be able to include a range of values instead of just a single value. Any help is greatly appreciated. I have enclosed the code.

    Thanks
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    good
    any chance of actually posting the code inside [ c o d e ] & [ / c o d e ] (minus the spaces)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2012
    Posts
    24
    Here you go. I wasn't sure, but when you said no spaces, so I deleted the spaces between the lines.

    Private Sub Search_records_stats_Click()
    ' Create a WHERE clause using search criteria entered by user and
    ' set RecordSource property of Search Subform.
    Dim MySQL As String, MyCriteria As String, MyRecordSource As String
    Dim ArgCount As Integer
    Dim Tmp As Variant
    'Clear Select field
    CurrentDb.Execute "UPDATE sample_table SET [Selected] = False;", dbFailOnError
    ' Initialize argument count.
    ArgCount = 0
    ' Initialize SELECT statement.
    MySQL = "SELECT * FROM master_query WHERE "
    MyCriteria = ""
    ' Use values entered in text boxes in form header to create criteria for WHERE clause.
    AddToWhere [Look For ea], "[ea]", MyCriteria, ArgCount
    AddToWhere [Look For efis], "[efis]", MyCriteria, ArgCount
    AddToWhere [Look For district], "[district]", MyCriteria, ArgCount
    AddToWhere [Look For county], "[county]", MyCriteria, ArgCount
    AddToWhere [Look for route], "[route]", MyCriteria, ArgCount
    AddToWhere [Look for material], "[description]", MyCriteria, ArgCount
    AddToWhere [Look for br #], "[bridge_number]", MyCriteria, ArgCount
    AddToWhere [Look for br name], "[bridge_name]", MyCriteria, ArgCount
    AddToWhere [Look for CR], "[corrosion_number]", MyCriteria, ArgCount
    AddToWhere [Look for TL101], "[TL101]", MyCriteria, ArgCount
    AddToWhere [Look for MSE], "[MSE_WALL]", MyCriteria, ArgCount
    ' If no criterion specifed, return all records.
    If MyCriteria = "" Then
    MyCriteria = "True"
    End If
    ' Create SELECT statement.
    MyRecordSource = MySQL & MyCriteria
    ' Set RecordSource property of Search Subform.
    Me![Search Subform Stats].Form.RecordSource = MyRecordSource
    ' If no records match criteria, display message.
    ' Move focus to Clear button.
    If Me![Search Subform Stats].Form.RecordsetClone.RecordCount = 0 Then
    MsgBox "No records match the criteria you entered.", 48, "No Records Found"
    Me!Clearall.SetFocus
    Else
    ' Enable control in detail section.
    Tmp = EnableControls("Detail", True)
    ' Move insertion point to Search Subform.
    Me![Search Subform Stats].SetFocus
    End If
    End Sub

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sorry, that's too funny. Healdem meant to use code tags, and by spaces meant:

    [ c o d e ]

    would be

    [code]

    In any case, it would help to see the AddToWhere function, as that's probably where the changes will need to be.
    Paul

  5. #5
    Join Date
    Apr 2012
    Posts
    24
    Here you go.

    Private Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)

    ' Create criteria for WHERE clause.
    If FieldValue <> "" Then
    ' Add "and" if other criterion exists.
    If ArgCount > 0 Then
    MyCriteria = MyCriteria & " and "
    End If

    ' Append criterion to existing criteria.
    ' Enclose FieldValue and asterisk in quotation marks.
    MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) & FieldValue & Chr(42) & Chr(39))

    ' Increase argument count.
    ArgCount = ArgCount + 1
    End If

    End Sub

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you want multiple values for a where clause then you need to use an or between each item or better use the in construct
    Code:
    where acolumn = 'caltranscorrosion' and (bcolumn = 123 or bcolumn = 234)
    better
    Code:
    where acolumn = 'caltranscorrosion' and bcolumn in (123, 234)
    personally I'd do this sort of complex filter building using a form, then pass the where clause as a filter tot he form or report.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2012
    Posts
    24
    Healdem:

    I appreciate the quick response, but I still haven't figured it out. I am trying to put a range of values to look for the corrosion_number (an unbound text box) in a form. Where exactly do I define the a column and bcolumns? If they go in the Where clause, do I set up a seperate criteria for each column like I currently have for the field value? You mentioned passing the where clause as a filter to the form, how would that work?

    Thanks

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you want multiple values in a where clause then you should use the in(comma, separated, list, of, values) style.
    if you don't have any other parameters supplied with commas in then you coudl get your users to specify the list of values separated by a comma
    eg if they wanted 10 14 and 15
    express that as 10,14,15 and modify the function AddToWhere
    Code:
    Private Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)
    
    ' Create criteria for WHERE clause.
    If FieldValue <> "" Then
    ' Add "and" if other criterion exists.
    If ArgCount > 0 Then
    MyCriteria = MyCriteria & " and "
    End If
    
    'test if this criteria has multiple values
      if instr(MyCriteria,",") >0 then 'if we find a coma int he parameter then we presume we have multiple values, so use the in(xxx,yyy,zzz) style
        'note this ONLY works for numeric values separarted by a comma
        'if you need string values then these need to be quoted
        MyCriteria = MyCriteria & FieldName & " in (" & fieldvalue & ") "
    else
      ' Append criterion to existing criteria.
      ' Enclose FieldValue and asterisk in quotation marks.
      MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) & FieldValue &   Chr(42) & Chr(39))
    endif
    ' Increase argument count.
    ArgCount = ArgCount + 1
    End If
    
    End Sub
    note you will make life easier for yourself if you indent your code
    eg
    Code:
    ' Create criteria for WHERE clause.
    If FieldValue <> "" Then
      ' Add "and" if other criterion exists.
      If ArgCount > 0 Then
        MyCriteria = MyCriteria & " and "
      End If
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Apr 2012
    Posts
    24
    Healdem:

    Thanks for the assistance, most of my search criteria are string values, but I do have one numeric value that I have been playing with. I haven't been able to get it to work yet, I keep getting a message "No records match the criteria you entered.", but I suspect it's something simple.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you say ia number of values for a corrosion number, yet you refer to strings......


    if you need to search multiple string values, then get your users to specify values with quote marks

    eg if they want apple fruit orange tomato then they enter
    'apple', 'fruit', 'orange', 'tomato'
    and the code will work

    the problem with this whole approach is it depends on the users behaving themselves, not entering in crap values. ferinstance say they wanted to use names and one of thise was say d'Ath.. that will screw up the quote marks used to denote the start/stop of text literals

    now you may have more faith in your users than I have in mine. but I'd far prefer to validate user input before adding it to a where clause.

    doing things the way you are is fine, except that the user has to add values each time and they can screw things up.another disadvantage of the cvurrent approach is that users have to enter in the data ech time the query is run. which means that it may take sevearl iterations to get the right mix of parameters. usiugn a form to cllect the parameters and then submitting the filtering from that form means you can run a report suite as many tiems as you like with the same parameters. it aslo measn that if theuser wants to rerun the report but add, say, a single element they can just change that one element.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Apr 2012
    Posts
    24
    Healdem:

    While I understand what you're saying, I maintain this database for our lab. Other than when I am on vacation, no one else enters data, sends out the reports, or searches for records. The quotation thing is still not working, and if you're open to it, I can send you a copy of the database (greatly reduced). It may help you understand better what I am trying to do.
    Thanks
    caltranscorrosion

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no problem
    make a copy of the db, remove or mask anything confidential or critical
    delete any other forms or reports just leave the basic stuff needed to demonstrate the problem. then do a compact and repair on that copy
    compress it as a zip file and post it here.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Apr 2012
    Posts
    24
    Healdem:

    Here you go. I have attached both the zip file of our database along with a word doc with some examples for your use.

    CaltranscorrosionHealdem.doc

    STATS DATABASE.zip

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK I can see the problem, a mea culpa
    Code:
    if instr(MyCriteria,",") >0 then 'if we find a coma int he parameter then we presume we have multiple values, so use the in(xxx,yyy,zzz) style
        'note this ONLY works for numeric values separarted by a comma
    should read
    Code:
    if instr(FieldValue,",") >0 then 'if we find a comma in the parameter then we presume we have multiple values, so use the in(xxx,yyy,zzz) style
        'note this ONLY works for numeric values seperarted by a comma

    fieldvalue should replace mycriteria in the if statement. effectively what the if statement is doing is scanning the value of the supplied criteria (fieldvalue) to see if a comma is included. I had mistakenly used the output (MyCriteria) instead of FieldValue.
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Apr 2012
    Posts
    24
    Healdem:

    I can't thank you enough! It works great and provides me with that extra flexibility I occasionally require.

    Caltranscorrosion

Posting Permissions

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