Database Form Query

[KeithG]

Basically your are consructing a list seperated by a comma of all the tool numbers that the user has entered into the text boxes and putting it into strToolSearch.

 

[mogul0212]

Here's what I believe to be the code, but it's not working....

any ideas?

    If IsNull(Me.cmbToolSearch1) = False And Len(Me.[cmbToolSearch1]) > 1 Then
        If IsNull(strWhere) = False And Len(strWhere) > 2 Then
            strToolSearch = strWhere & " AND ([Outside Tool #1]='" & Me.cmbToolSearch1 & "')"
        Else
            strToolSearch = " Where  ([Outside Tool #1]in (" & strToolSearch & ")" & "or [Outside Tool #2]in (" & strToolSearch & ")  Or [Outside Tool #3] in(" & strToolSearch & ")  Or [Inside Tool #1]in(" & strToolSearch & ") OR [Inside Tool #2]in(" & strToolSearch & ") Or [Inside Tool #3]in(" & strToolSearch & ") Or [Inside Tool #4]in(" & strToolSearch & ") Or [Inside Tool #5]in(" & strToolSearch & "))"
        End If
    End If

 

[KeithG]

You want to assign the values from the combo boxes to strToolSearch seperated by a comma.

 

[KeithG]

I would put togther an example but I am swamped with work righ now

 

[mogul0212]

You want to assign the values from the combo boxes to strToolSearch seperated by a comma.

Ok, I thought that what that did, but must be missing something. Can understand being swamped, no problem.

I'll try playing with that more following your example, all the quote marks and brackets confuse me.

 

[mogul0212]

Somoething close to this?

    If IsNull(Me.cmbToolSearch1) = False And Len(Me.[cmbToolSearch1]) > 1 Then
            strToolSearch = strToolSearch & "'" & strToolSearch & "'" & ","
    End If
        
    If IsNull(strToolSearch) = False And Len(strToolSearch) > 2 Then
            strToolSearch = " Where  ([Outside Tool #1]in (" & strToolSearch & ")" & "or [Outside Tool #2]in (" & strToolSearch & ")  Or [Outside Tool #3] in(" & strToolSearch & ")  Or [Inside Tool #1]in(" & strToolSearch & ") OR [Inside Tool #2]in(" & strToolSearch & ") Or [Inside Tool #3]in(" & strToolSearch & ") Or [Inside Tool #4]in(" & strToolSearch & ") Or [Inside Tool #5]in(" & strToolSearch & "))"
    End If

 

[mogul0212]

KeithG...

When you get time, could you possibly try to clarify the line of code. I'd really appreciate it.

 

[fastmike]

Wow this post was pretty good. i was trying to learn about the coding since i just recently started working on access typical newbie. but man people know about access alot specially salene and keithg. hope to get a job at fedex so then i will be dealing with the mainframe but for rite now stuck with access at my new job. had to post couple of easy newbie questions but this post truly rocks. thanks alot for helping us out guys. i wish i can help you out mogu but i know this much even if u try to work on any kind of access project this forum will always help you out.

 

[KeithG]

Yeah that looks correct. Are you receiving a errors. I appoligize I haven't been around the last couple of days.

 

[mogul0212]

KeithG

It's returning all results, like it's not searching that criteria at all.

 

[KeithG]

Below is your problem line

strToolSearch = strToolSearch & "'" & strToolSearch & "'" & ","

it should be

strToolSearch= strToolSearch & "'" & me.[ComboBoxName] & "'" & ","

 

[KeithG]

Below is your problem line

strToolSearch = strToolSearch & "'" & strToolSearch & "'" & ","

it should be

strToolSearch= strToolSearch & "'" & me.[ComboBoxName] & "'" & ","

 

[KeithG]

Below is your problem line

strToolSearch = strToolSearch & "'" & strToolSearch & "'" & ","

it should be

strToolSearch= strToolSearch & "'" & me.[ComboBoxName] & "'" & ","

 

[mogul0212]

Below is your problem line

strToolSearch = strToolSearch & "'" & strToolSearch & "'" & ","

it should be

strToolSearch= strToolSearch & "'" & me.[ComboBoxName] & "'" & ","

Hmmm, changed that line, and still get every record back

 

[KeithG]

can you post your db?

 

[mogul0212]

Here's my database so far. I've added the Drawing Information Search form.

Trying to get multiple combo boxes to have search criteria criteria to search all of the Outside/Inside tool boxes for a particular tool # if used in any of the columns.

 

[KeithG]

strToolSearch = " Where  ([Outside Tool #1]in (" & strToolSearch & ")" & "or [Outside Tool #2]in (" & strToolSearch & ")  Or [Outside Tool #3] in(" & strToolSearch & ")  Or [Inside Tool #1]in(" & strToolSearch & ") OR [Inside Tool #2]in(" & strToolSearch & ") Or [Inside Tool #3]in(" & strToolSearch & ") Or [Inside Tool #4]in(" & strToolSearch & ") Or [Inside Tool #5]in(" & strToolSearch & "))"

This line is part of your problem. You should be assigning the string to strWhere instead of strToolSearch. Also I did not see code checking each combo search box.

 

[mogul0212]

strToolSearch = " Where  ([Outside Tool #1]in (" & strToolSearch & ")" & "or [Outside Tool #2]in (" & strToolSearch & ")  Or [Outside Tool #3] in(" & strToolSearch & ")  Or [Inside Tool #1]in(" & strToolSearch & ") OR [Inside Tool #2]in(" & strToolSearch & ") Or [Inside Tool #3]in(" & strToolSearch & ") Or [Inside Tool #4]in(" & strToolSearch & ") Or [Inside Tool #5]in(" & strToolSearch & "))"

This line is part of your problem. You should be assigning the string to strWhere instead of strToolSearch. Also I did not see code checking each combo search box.

Just trying to learn the syntax of the code to get it working. I've come to realize that I'm not quite up on the code. To be honest, I'm completely lost on how to do it.

 

[mogul0212]

strToolSearch = " Where  ([Outside Tool #1]in (" & strToolSearch & ")" & "or [Outside Tool #2]in (" & strToolSearch & ")  Or [Outside Tool #3] in(" & strToolSearch & ")  Or [Inside Tool #1]in(" & strToolSearch & ") OR [Inside Tool #2]in(" & strToolSearch & ") Or [Inside Tool #3]in(" & strToolSearch & ") Or [Inside Tool #4]in(" & strToolSearch & ") Or [Inside Tool #5]in(" & strToolSearch & "))"

This line is part of your problem. You should be assigning the string to strWhere instead of strToolSearch. Also I did not see code checking each combo search box.

Ok, I changed the wording to get rid of the strToolSearch and replace with the strWhere in the code. Now how would I add multiple searchs up to 8 combo boxes that would include an "And" phrase to make a selection include only those records that all of the tool #'s entered in the combo boxes?

The combos boxes are named cmbToolSearch1, cmbToolSearch2, .... cmbToolSearch8

Here's the code I modified since your last input.

    If IsNull(Me.cmbToolSearch1) = False And Len(Me.[cmbToolSearch1]) > 1 Then
            strWhere = strWhere & "'" & Me.[cmbToolSearch1] & "'" & ","
    End If
    
    If IsNull(strWhere) = False And Len(strWhere) > 2 Then
            strWhere = " Where  ([Outside Tool #1]in (" & strWhere & ")" & "or [Outside Tool #2]in (" & strWhere & ")  Or [Outside Tool #3] in(" & strWhere & ")  Or [Inside Tool #1]in(" & strWhere & ") OR [Inside Tool #2]in(" & strWhere & ") Or [Inside Tool #3]in(" & strWhere & ") Or [Inside Tool #4]in(" & strWhere & ") Or [Inside Tool #5]in(" & strWhere & "))"
    End If

 

[KeithG]

So are you saying your going to have 8 combo's and every tool # the user selects must be used in the tool record to be shown? If so you will need to change your Or to AND in your Where Clause.