Search Form Code

[f430]

hi, i have a master table with all my part information, and it has columns with part number, date, defects,...
and i am trying to write a code for my search form.
i have created a search form on Access, and it has 5 combo boxes for:
part number
defects
assembled by
and the other 2 combo boxes are for "from" and "to" date range. i have made the combo boxes for the date range to display a calendar when clicked and choose a date.

i have been trying to write a code that only requires one of these fields to be filled out and it can filter my master table.
i would appreciate any help on this
Thanks

[NeoPa]

f430: i am not really sure what you mean by explicitly formatting these two boxes.

I suggest you look at the article I linked you to in post #8 then. It is explained fully there.

f430: i was also wondering what kind of code i should use before the where statement.

That would be a standard SQL query string. If this is something you're not familiar with then we may need a crash course on the very basics.

Queries in Access are actually QueryDef objects. These can be saved as you know, and run, and edited etc.

At the heart of every QueryDef there is the fundamental query instructions. These are written in SQL code and can be found, when editing a QueryDef, by selecting SQL View from the View menu. Such SQL code can form the basis of a QueryDef object, but there are also ways of executing SQL code from a simple string variable (or literal) from within VBA.

To learn more about what SQL can do for you look in the Help system (Finding Jet SQL Help).

The (almost) most basic form of a SQL instruction would be to display the contents of a table and goes like :

SELECT *
FROM   [TableName]

The SQL of a QueryDef is updatable. This can prove important.

[NeoPa]

Having seen your code some things become clearer to me.

Firstly, you are working on a filter string. Filter strings are essentially the WHERE clause of a full SQL statement, but without the word "WHERE ". The SELECT clause is equally unnecessary when dealing with filters (By the way, SELECT ... was just my use of the ellipsis (...) to indicate whatever you had before here. I wasn't suggesting the literal use of the dots).

You still have an underline character (_) on your line #13 which is an error (See post #8).

When building a filter string, it is advisable (generally) to start from scratch unless you know what you can expect it to contain already. It appears that your code would go around and around making the filter string longer and longer re-applying filtering on the same set of fields. Not good for you.

[f430]

NeoPa, i appreciate all the help.
i apologize for not keeping up with you, but my skills in access/vba are very basic. i am having some problems building a query string, would you be able to provide more information about building a standard sql string.

i fixed the minor errors and the here is what it looks like

If Me!productnum > "" Then _
     strFilter = strFilter & _
     " AND ([Part Number]=" & _
     Me!productnum & ")"
 
 
strFilter = "If Me!defect > "" Then strFilter = strFilter & " And ([Defectcode] = " & Me!Defect & ") & "WHERE '" & Me.Defect & "' In([Defect Code 1], [Defect Code 2], [Defect Code 3])"
 
 
strFilter = "If Me!associateid > "" Then strFilter = strFilter & " And ([associates] = " & Me!associateid & ") & "WHERE '" & Me.associateid & "' In([associate], [associate 2])"

Thanks

[NeoPa]

I must rush for now, but try looking more carefully at the code in the article linked in post #2. I can go into more detail later, but trust me, your understanding will benefit from a bit more looking at it. Even if you still need further explanation, at least when that comes you'll be able to put what you read into context. Those "aaaaaah" moments are really quite precious. I'd hate you to miss out ;)

PS. I know it probably all looks like a foreign language at the moment, but I expect it will become clearer soon.

[f430]

my date1 and date2 comboboxes are formatted as short dates in the properties of the combo box, so i guess they are explicitly formatted, and the code im trying to use is :

strFilter = "SELECT [CreationDate]" & "FROM Master Table" & "WHERE [CreationDate] Between #date1# And #date2#"

however, i am a little unsure about the codes on line 7 and 10 above, because i have not really defined what "defectcode " and "associates " are. and i keep getting them highlighted when i debug

[NeoPa]

f430: my date1 and date2 comboboxes are formatted as short dates in the properties of the combo box, so i guess they are explicitly formatted, and the code im trying to use is :

strFilter = "SELECT [CreationDate]" & "FROM Master Table" & "WHERE [CreationDate] Between #date1# And #date2#"

Absolutely not! That is not only implicitly formatted but it will certainly start to cause problems if you ever try to run it on a machine set up other than for North America, which almost coincidentally has a short date format similar to the SQL standard. I can't see how you could say that if you'd read the article I linked you to.

f430: however, i am a little unsure about the codes on line 7 and 10 above, because i have not really defined what "defectcode " and "associates " are. and i keep getting them highlighted when i debug

This sounds like a joke. Why would you think it could possibly work if you have references to non-existent items?

Have you even looked at the articles I linked you to? There is no indication of it in your comments. I feel like a man trying to help someone drowning but every time I throw the rope in the water you throw it back. You want to be rescued by a boat.

I'm happy to continue trying if you are, but we need to get past asking questions that are this obvious. References to non-existent items are going to cause a problem. Don't ask me to save you from that. Create the items before doing your test. Dates need to be formatted explicitly for use within SQL. This is a SQL standard. You can write code that doesn't do this. It will probably work for you if North America is your whole world, but it is not what I suggest for reasons of portability and the difficulty of finding the problem when it does happen.

[NeoPa]

Try this. It depends on the items being correctly named and, above all, existing, but the basic format should be about right :

strFilter = ""
If Not IsNull(Me.ProductNum) Then _
    strFilter = strFilter & " AND " & _
                "([Part Number]=" & Me.ProductNum & ")"
 
If Not IsNull(Me.Defect) Then _
    strFilter = strFilter & " AND " & _
                "(" & Me.Defect & " In([Defect Code 1]," & _
                                      "[Defect Code 2]," & _
                                      "[Defect Code 3])"
 
If Not IsNull(Me.AssociateID) Then _
    strFilter = strFilter & " AND " & _
                "(" & Me.AssociateID & " In([Associate]," & _
                                           "[Associate 2])"
 
If strFilter > "" Then strFilter = Mid(strFilter, 6)
Me.Filter = strFilter
Me.FilterOn = (strFilter > "")

PS. It assumes that Me.ProductNum, Me.Defect and Me.AssociateID are numeric (as indicated by your code). If this is not the case then we need to know.

[f430]

i did read your articles, i just didn't know how to define these variables since they were not in any of my fields in my master table. i apologize for the trouble.
i did make some modifications to my code, because some were text fields and i accidentally put in a code for a number. i have also changed the date code to something hopefully better.
however i still wasn't able to get it to work.
here is my code for your review:

Private Sub cmdfindrecords_Click()
   Dim strFilter As String, strOldFilter As String
 
   strOldFilter = Me.Filter
   strFilter = "1=1 "
 
   If Me!productnum & "" > "" Then
      strFilter = strFilter & " AND [Part Number]=" & Me!productnum & " "
   End If
 
   If Me!Defect & "" > "" Then
      strFilter = strFilter & " And """ & Me!Defect & _
         """ In([Defect Code 1], [Defect Code 2], [Defect Code 3]) "
   End If
 
   If Me!associateid & "" > "" Then
      strFilter = strFilter & " And """ & Me!associateid & _
         """ In([Associate], [Associate 2]) "
    End If
 
    If Not IsNull(Me.date1) Then
      strFilter = strFilter & " AND CreationDate >=#" & Format(Me!date1, "m/d/yyyy") & "# "
   End If
   If Not IsNull(Me.date2) Then
      strFilter = strFilter & " AND CreationDate <=#" & Format(Me!date2, "m/d/yyyy") & "# "
   End If
 
   If strFilter <> "1=1 " Then
      Me.Filter = strFilter
      Me.FilterOn = True
    Else
      Me.Filter = strOldFilter
   End If
 
Debug.Print strFilter
End Sub 

now whenever i try to search using the part number or the date range, nothing really happens.
but if i use the defect or the associate number, i get a message that asks me to enter a parameter value for defect code 3 and associate 1,2.
i tried debugging and only when the filter runs through the code below, that the search form asks me to enter the parameter values

 Me.Filter = strFilter

Thanks

[NeoPa]

It's hard to work with you because you don't respond to what I post. You just keep starting again from where you are and I can never tell what you're talking about or where you're at in your head. I never know how much of what I've already said I need to repeat. Basically, it's very frustrating trying to communicate with someone who doesn't respond to you.

I get the feeling that you're doing your best, but you don't seem to understand simple communication.

Your latest code is wrong in so many ways. I do not wish to spend five minutes on every point as that will take up so much of my time. I spent time earlier providing a good basic set of code that would work for you assuming all you'd told me so far was reliable, but instead of going from there and explaining what is still not working, you ignore that and go back to your code which is so full of holes I can't even comment on them all.

Please stop ignoring what I post. It makes me feel like I'm wasting my time. I don't post it without reason. This can help you a long way forward if you'll let it. It won't if you ignore it though.

PS.

NeoPa: PS. It assumes that Me.ProductNum, Me.Defect and Me.AssociateID are numeric (as indicated by your code). If this is not the case then we need to know.

Posting a whole set of different and non-workable code is not letting me know.

[NeoPa]

As I now have some information about another bit of the filtering I can add that into my suggested code :

strFilter = ""
If Not IsNull(Me.ProductNum) Then _
    strFilter = strFilter & " AND " & _
                "([Part Number]=" & Me.ProductNum & ")"
 
If Not IsNull(Me.Defect) Then _
    strFilter = strFilter & " AND " & _
                "('" & Me.Defect & "' In([Defect Code 1]," & _
                                        "[Defect Code 2]," & _
                                        "[Defect Code 3])"
 
If Not IsNull(Me.AssociateID) Then _
    strFilter = strFilter & " AND " & _
                "('" & Me.AssociateID & "' In([Associate]," & _
                                             "[Associate 2])"
 
If Not IsNull(Me.Date1) Then
    strFilter = strFilter & " AND ([CreationDate]"
    If Not IsNull(Me.Date2) Then
        strFilter = strFilter & _
                    " Between " & Format(Me.Date1, "\#m/d/yyyy\#") & _
                    " And " & Format(Me.Date2, "\#m/d/yyyy\#") & ")"
    Else
        strFilter = strFilter & _
                    "=" & Format(Me.Date1, "\#m/d/yyyy\#") & ")"
    End If
End If
 
If strFilter > "" Then strFilter = Mid(strFilter, 6)
Me.Filter = strFilter
Me.FilterOn = (strFilter > "")

I have changed my assumption about the Defect Code and Associate ID fields from looking at your latest code posted. I'm guessing you have some reason for doing it that way. Again. I'm only guessing as the information I'm working with is so poor. Please let me know if what the actual types of the fields are. At least I can be reasonably sure that [CreationDate] is DateTime.

If you want help on how to deal with literal strings in VBA and SQL check out Quotes (') and Double-Quotes (") - Where and When to use them.