Access DB and VB module/form search function

[drh224]

Hi everyone,

I hope someone can provide an example/input for me regarding a database and form I have. I have a search form that searches my database based on SQL statements. I have the multiple search criteria joined with OR statements. This will let the user expand their results when using more than one search criteria. What I want to do is give the user the option of using AND or OR when searching. I created a combo box with the AND and OR operators, and replaced my OR statements in the SQL code with "& combo89.value &". This works with the OR value, but when AND is used, its seeing the search criteria as blank, and looking for blank records, which there are none. I believe I need a if statement with a counter to accomplish my goal, but am unsure of how to program the code. If anyone would like to help, I can provide some sample code to get pointed in the right direction.

Thanks,
-Dan

[DataMiser]

I would say the first step would be to add either a debug.print or a msgbox to display the sqlstring built by your code and examine it for errors. That should give you a clue as to what the problem is.

If you still have issue with it post the section fo the code that builts the sqlstring in question and the output string that is shown by the debug.print and someone here should be able to help.

[drh224]

sSubWhere = "WHERE (((tblFrequencyLookup.FrequencyDescr) like ""*" & scriteria & "*"")) " & Combo91.Value & " (((tblFrequencyLookup.FrequencyDescr) like ""*" & sfreqcriteria & "*"")) " & Combo91.Value & " (((tblDistributionLookup.DistributionDescr) like ""*" & scriteria & "*"")) " & Combo91.Value & " (((tblDistributionLookup.DistributionDescr) like ""*" & sdistcriteria & "*"")) " & Combo91.Value & " (((tblCategoryLookup.CategoryDescr) like ""*" & scriteria & "*"")) " & Combo91.Value & " (((tblCategoryLookup.CategoryDescr) like ""*" & scatcriteria & "*"")) " & Combo91.Value & " (((tblSubCategoryLookup.SubCategoryDescr) like ""*" & scriteria & "*"")) " & Combo91.Value & " (((tblSubCategoryLookup.SubCategoryDescr) like ""*" & sscatcriteria & "*"")) " & Combo91.Value & " (((tblReport.Title) Like ""*" & scriteria & "*""))

When I select the AND value from my combo box on the form, if any of the search criteria are not selected(blank) it will yield no results. If I choose OR, it operates just fine. for each " & Combo91.Value & " statement, I originally had OR, negating the option for the user to select AND or OR. So, thats why when I select OR for my value, it works like it did prior to my modifcation. I think I need a conditional statement for each combo91.value statement, i'm just not sure how to generate the piece of code.

I used the debug.print statement(one in the beginning to show when it started, and one at the end indicating the block of code was executed) but it won't show any errors since its not erroring out, it is just not producing results.

Thanks for the inital help, it is greatly appreciated.

[dglienna]

He means print out the SQL STRING, and then paste it here.

It will show you the values that you are trying to query.

(You can then paste this into Access, and get the exact error message as to the first error. Then correct all errors, and rebuild the SQL statement in your app)

That looks like one confusing query, with al the LIKE statements, that it'd be sure to run slowly.

Why not query them all, and then apply FILTERS before you show the data?

[George1111]

I gather you are trying to selct data based on a number of Combo Boxes

Say you have 10 combo boxes

If you put select criteria into only 3 of the boxes, your sql statement should not look at the other 7 boxes (as they have no valuse to compare)

You therefore have to create a logical SQL Statement based on whether or not the combo box has been selected

That is why your AND statement never works - you are selected based on all boxes and some are blank

Your OR selection will work but it will work when any of the select criteria is met (which I dont believe is what you want)



The example you show us is different to what I have described above. (And what you are asking about)

In your example you are checking a large number of variables in a database to see if they are "LIKE" the value entered into combo91

This selection has neither AND or OR statements in it - I dont think it can work at all as you are checking a multiple number of variables with the match being that combo91 must be like everyone of them - Is that what you are trying to do ?

[drh224]

I gather you are trying to selct data based on a number of Combo Boxes

Say you have 10 combo boxes

If you put select criteria into only 3 of the boxes, your sql statement should not look at the other 7 boxes (as they have no valuse to compare)

You therefore have to create a logical SQL Statement based on whether or not the combo box has been selected

That is why your AND statement never works - you are selected based on all boxes and some are blank

That is exactly what I want. I have 4 combo boxes and 3 text fields for search options. The combo91 box I am using to let the user select either AND or OR, replaced what I orginially had in my code. I previously did not have a combo91 box for the user and just used OR statments between the search criteria:

sSubWhere = "WHERE (((tblFrequencyLookup.FrequencyDescr) like ""*" & scriteria & "*"")) OR (((tblFrequencyLookup.FrequencyDescr) like ""*" & sfreqcriteria & "*""))...etc

So my problem is that I am having trouble developing the logical statement within the SQL code. Can anyone provide a sample of how this should look?

dglienna- it actully searches quickly, I only have about 800 records in my DB at the moment. I haven't tried filtering yet, i think the way my tables are joined may cause a few issues if i would try this method, but it would be something to consider in the future after i get the AND OR search option part figured out.

[dglienna]

I did the exact same thing. I had 4 drop-downs with choices (that could all be NONE).

Whenver anything was clicked, it called BuildFilter, after setting a flag. It passed the flag to the function.

It returned the filter for all 4 fields.

As soon as you changed any drop-down, the whole table changed.

(then I did it again with a VB.Net app. Created a master temp table of three tables and 200 fields. it filled a grid, and also 4 tabbed forms of data. also filtered)

[drh224]

Regarding the drop downs, any number of them can be used, not all of them. Meaning, if the user only wanted to select 2 of the criteria and join them with an AND operator, as of right now it would produce no results...why? because it is seeing the empty field for the none selected combobox. I need a statement to test if the combobox has been selected, and if it is, use that as part of the search criteria.

[dglienna]

Here's what I did in ANOTHER form, that had a bunch of checkboxes: It builds a SQL statement.

Private Sub cmd_Click(Index As Integer)
  Dim str$, x As Integer
  Dim adorec As Object
  Set adorec = New ADODB.Recordset
  Select Case cmd(Index).Caption
  Case "Cancel"
    frmDesign.chkCustom.Value = Unchecked
    Unload Me
  Case "Select"
    With frmCustomQuery
      str = ""
      For x = 44 To 52
        If chk(x).Value = Checked Then
          str = str & chk(x).Tag & "=TRUE AND "
        End If
      Next x
    End With
    Select Case cmb.ListIndex
      Case 0
        ' Active -      1
        ' str = str & '
        str = str & "Status=1 "
      Case 4
        ' Other  - 715
        str = str & "Status=715 "
      Case 3
        ' Don't Send  - 710
        str = str & "Status=710 "
      Case 2
        ' Letter Returned - 705
         str = str & "Status=705"
      Case 1
       ' Seal Deal -  700
        str = str & "Status=700"
      Case 5
       ' Inactive - 999
        str = str & "Status=1 "
    End Select
    
    If chkUseFilter = Checked Then
      UseFilterInReport = True
    Else
      UseFilterInReport = False
    End If
    If Right$(str, 4) = "AND " Then
        str = Left$(str, Len(str) - 4)
    End If
    UserFilter = str
    strsql = "SELECT * "
    strsql = strsql & "FROM Transactions WHERE " & str
'      Debug.Print strsql ' 8 & 44
    If adorec.State = adStateOpen Then adorec.Close
    adorec.Open strsql, cnxn, adOpenDynamic, adLockOptimistic
    Set DesignerRS = adorec
  End Select
  Set adorec = Nothing
  If chkUseFilter.Value = Checked Then
    DesignerRS.Filter = TransRS.Filter
    DesignerRS.Requery
  End If
  If opt(1).Value = True Then repStriped = True Else repStriped = False
  Unload Me
End Sub

[George1111]

Basically you need to do something like the following:

sSubWhere = "WHERE"
If trim(scriteria) <> "" then sSubWhere = sSubWhere & " (((tblFrequencyLookup.FrequencyDescr) like ""*" & scriteria & "*"")) " 
If sSubWhere <> "WHERE" then sSubWhere = sSubWhere & Combo91.Value 
If trim(sfreqcriteria) <> "" then sSubWhere = sSubWhere &  " (((tblFrequencyLookup.FrequencyDescr) like ""*" & sfreqcriteria & "*"")) " 
If sSubWhere <> "WHERE" then sSubWhere = sSubWhere & Combo91.Value 
If trim(scriteria) <> "" then sSubWhere = sSubWhere &  " (((tblDistributionLookup.DistributionDescr) like ""*" & scriteria & "*"")) " 
If sSubWhere <> "WHERE" then sSubWhere = sSubWhere & Combo91.Value 
If trim(sdistcriteria) <> "" then sSubWhere = sSubWhere & 
 Combo91.Value & " (((tblDistributionLookup.DistributionDescr) like ""*" & sdistcriteria & "*"")) 
If sSubWhere <> "WHERE" then sSubWhere = sSubWhere & Combo91.Value 

etc
etc
etc


MSGBOX sSubWhere

This code is not 100% accute - check the syntax carefully and display the resultant SQL Statement before execution

BIG NOTE : MAKE SURE YOUR SELECTIVE SQL STATEMENT DOES NOT END WITH THE VALUE IN COMBO91 (IE, 'and' OR 'or')

Check for these values at the end and strip them off

[dglienna]

Why don't you try it before you post it? Doesn't look like it's right. Looks like it builds the same thing multiple times

[George1111]

I think the code is basically sound

What he is trying to do is selectively include a SQL selection based on values like scriteria, sfreqcriteria, sdistcriteria existing or not.

In the example he posted, I also picked up that 'scriteria' is used to check about 5 different database fields (APARENTLY A DUPLICATION) - only he will know if this is correct or not

What if that's what he wants to do ?

sSubWhere = "WHERE (((tblFrequencyLookup.FrequencyDescr) like ""*" & scriteria  & "*"")) " & Combo91.Value & 
" (((tblFrequencyLookup.FrequencyDescr) like ""*" & sfreqcriteria & "*"")) " & Combo91.Value &
 " (((tblDistributionLookup.DistributionDescr) like ""*" & scriteria  & "*"")) " & Combo91.Value &
 " (((tblDistributionLookup.DistributionDescr) like ""*" & sdistcriteria & "*"")) " & Combo91.Value &
 " (((tblCategoryLookup.CategoryDescr) like ""*" & scriteria  & "*"")) " & Combo91.Value &
 " (((tblCategoryLookup.CategoryDescr) like ""*" & scatcriteria & "*"")) " & Combo91.Value &
 " (((tblSubCategoryLookup.SubCategoryDescr) like ""*" & scriteria  & "*"")) " & Combo91.Value &
 " (((tblSubCategoryLookup.SubCategoryDescr) like ""*" & sscatcriteria & "*"")) " & Combo91.Value &
 " (((tblReport.Title) Like ""*" & scriteria  & "*""))

[drh224]

I think the code is basically sound

What he is trying to do is selectively include a SQL selection based on values like scriteria, sfreqcriteria, sdistcriteria existing or not.

That is what I am trying to accomplish, if the value is selected, use it, otherwise do not include. It looks the sample code you posted is accomplishing that, correct me if I am mistaken?

I modified the sample to remove those duplicate values, each criteria is unique and should only check if the value has been selected. The repeated scriteria value is a text input that searchs all combo boxes for the text the user typed in.(I think I am going to remove this to make the search a little bit simpler for now)

sSubWhere = "WHERE (((tblFrequencyLookup.FrequencyDescr) like ""*" & scriteria & "*"")) OR (((tblFrequencyLookup.FrequencyDescr) like ""*" & sfreqcriteria & "*"")) OR (((tblDistributionLookup.DistributionDescr) like ""*" & scriteria & "*"")) OR (((tblDistributionLookup.DistributionDescr) like ""*" & sdistcriteria & "*"")) OR (((tblCategoryLookup.CategoryDescr) like ""*" & scriteria & "*"")) OR (((tblCategoryLookup.CategoryDescr) like ""*" & scatcriteria & "*"")) OR (((tblSubCategoryLookup.SubCategoryDescr) like ""*" & scriteria & "*"")) OR (((tblSubCategoryLookup.SubCategoryDescr) like ""*" & sscatcriteria & "*"")) OR (((tblReport.Title) Like ""*" & scriteria & "*"")) OR (((tblReport.Title) Like ""*" & stitcriteria & "*"")) OR (((tblReport.Description) Like ""*" & scriteria & "*"")) OR (((tblReport.Description) Like ""*" & sdescriteria & "*"")))"

I can explain that more, but the above line of code shows how scriteria is used to check all fields.

sSubWhere = "WHERE"
If trim(sfreqcriteria) <> "" then sSubWhere = sSubWhere & " (((tblFrequencyLookup.FrequencyDescr) like ""*" & sfreqcriteria & "*"")) " 
If sSubWhere <> "WHERE" then sSubWhere = sSubWhere & Combo91.Value 
If trim(scatcriteria) <> "" then sSubWhere = sSubWhere &  " ((tblCategoryLookup.CategoryDescr) like ""*" & scatcriteria & "*"")) " 
If sSubWhere <> "WHERE" then sSubWhere = sSubWhere & Combo91.Value 
If trim(sscatcriteria) <> "" then sSubWhere = sSubWhere &  " (((tblSubCategoryLookup.SubCategoryDescr) like ""*" & sscatcriteria & "*"")) " 
If sSubWhere <> "WHERE" then sSubWhere = sSubWhere & Combo91.Value 
If trim(sdistcriteria) <> "" then sSubWhere = sSubWhere &  " (((tblDistributionLookup.DistributionDescr) like ""*" & sdistcriteria & "*"")) " 
If sSubWhere <> "WHERE" then sSubWhere = sSubWhere & Combo91.Value
If trim(stitcriteria) <> "" then sSubWhere = sSubWhere &  " (tblReport.Title) like ""*" & stitcriteriaa & "*"")) " 
If sSubWhere <> "WHERE" then sSubWhere = sSubWhere & Combo91.Value 
If trim(sdescriteria) <> "" then sSubWhere = sSubWhere &  " (((tblReport.Description) like ""*" & sdescriteria & "*"")) " 
If sSubWhere <> "WHERE" then sSubWhere = sSubWhere & Combo91.Value

The above code is what I modified and tried quickly in my DB, didn't work though. I am going to go back through more throughly and try to fix up these issues. Thanks for pointing me in the right direction, I may not be the best at creating new code, but I can work with modifying and geting code to work.

[dglienna]

Build your SQL string the same way that I did. I looped thru control arrays of checkboxes (for AND, OR, NOT, etc) and listboxes. If the user selected OR, he could do any number of OR's together (but not using AND or NOT)

[George1111]

David - He's only got about 6 lines of select code

Why don't you be a good fellow and put his code into your suggested method so we can all benefit from your post

(I can't work it out how your suggestion is meant to work either)