-
Query conditions
Ok, I'm assuming this uses a creative application of IIF(), but we'll see.
I'm setting up a search form, and it will have I believe 4 search combo-boxes and/or text boxes. These choices are going to be the criteria on a query. The problem is, the user may or may not have enough information to make selections in all of them. Is there a way to set a criteria so that if the value it recievs is null it ignores it completly?
I know how to look to see if its null, I just need to know how to set it up so its not used.
Brandon
-
Say for example you have 4 text boxes.
this is what I do.
Private Sub Command0_Click()
Dim query As String
Dim query2 As String
If IsNull(Me!Text1) = False Then
query2 = query2 & " Column1 = '" & Me!Text1 & "'"
End If
If IsNull(Me!Text3) = False Then
query2 = query2 & " and Column2 = '" & Me!Text3 & "'"
End If
If IsNull(Me!Text5) = False Then
query2 = query2 & " and Column3 = '" & Me!Text5 & "'"
End If
If IsNull(Me!Text7) = False Then
query2 = query2 & " and Column4 = '" & Me!Text7 & "'"
End If
query = "select * from tablename where " & query2
query = Replace(query, " where and ", " where ")
If LTrim(RTrim((query2))) = "" Then
query = Replace(query, "where", "")
End If
MsgBox query
End Sub
-
Ok, that makes a lot of sense. One slight hitch though, the query I need to put the criteria in is already made. Not overly a big deal, and I would just drop all the SQL code from it in the approipriate places, except, its got a lotta different tables and fields, and it made Query WAY over the size limit of a string. :P
Any hints on how to fix this speed bump?
-
create 2 or 3 strings. and combine it before executing.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|