Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 514
  • Last Modified:

Search Forms

I am trying to create a form to do searches over all of the database.  I have put three text fields (unbound) that the user can use to search for a particular data load id, customer request id, and customer case number. I would like to set it up so they can also use wildcards within their searches.

I have a couple fields that are date fields that I want to search From a certain date and To a certain date. I have a few areas that I want use this search.
0
lauriecking0623
Asked:
lauriecking0623
  • 6
  • 5
1 Solution
 
Dale FyeCommented:
So, what you have is multiple criteria you would like to be able to enter, but some might be left blank.

Generally, when I have this situation, I add those unbound controls to a form, and add a command button (cmd_Filter), which, when clicked builds a filter string.  It might look like:
Private Sub cmd_Filter_Click

    Dim varFilter as variant

    varFilter = Null

    'The following three assume that LoadID, Customer Request ID, and CustCaseNum are
    'all numeric values.  If they are text, you would have to wrap the references to the controls
    'in quotes
    if me.txt_LoadID & "" <> "" Then varFilter = "[LoadID] = " & me.txt_LoadID

    if me.txt_CustReqID & "" <> "" Then
        varFilter = (varFilter + " AND ") & "[Customer Request ID] = " & me.txt_CustReqID
    endif

    if me.txt_CustCaseNum & "" <> "" Then
        varFilter = (varFilter + " AND ") & "[Customer Case Number] = " & me.txt_CustCaseNum
    endif

    if me.txt_FromDate & "" <> "" then
        varFilter = (varFilter + " AND ") & "[FromDate] >= #" & me.txt_FromDate & "#"
    endif

    if me.txt_ThruDate & "" <> "" then
        varFilter = (varFilter + " AND ") & "[ThruDate] <> #" & me.txt_ThruDate & "#"
    endif

    me.Filter = varFilter
    me.FilterOn = NOT ISNULL(varFilter)

End Sub

Open in new window

0
 
lauriecking0623Author Commented:
One of the fields has this format AA-00001

I have been throwing an error on a very simple filter.
0
 
lauriecking0623Author Commented:
This is great for doing a search with all criteria filled out but what if I want to fill-out just one field, does this work? Also, is there a way that I can use other boolean operators such as "NOT", "OR" etc.?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Dale FyeCommented:
Which field?

Normally, for text fields, I use a syntax that looks similar to:

"[FieldName] = " & chr$(34) & me.txt_FieldName & chr$(34)

But if you want to only type in part of the string, you might try:

"[FieldName] LIKE " & chr$(34) & me.txt_FieldName  & "*" & chr$(34)
0
 
Dale FyeCommented:
1.  This would work for leaving controls blank, that is why it tests to see whether the control contains any text before adding a criteria for that field.

2.  You could certainly add the NOT, OR, functionality into the mix, that could make building the SQL string a little more difficult.  Not so much so with NOT, but with OR, you have to identify what is being OR'd.  Can you Provide an example of what you might want the SQL with an OR to look like?
0
 
lauriecking0623Author Commented:
Come to think about it. Right now, I think that I would need that since these are fields that we would search only.

I am trying to incorporate your code but I am still a little confused how to do it for my text fields. I had added ProductionName which is a text field.
0
 
Dale FyeCommented:
When you are working with text fields, you must make sure to wrap the value in quotes, so I use a syntax that looks like:
if me.txt_ProdName & "" <> "" then
        varFilter = (varFilter + " AND ") & "[ProductionName] =" & chr$(34) & me.txt_ProdName & chr$(34)
endif

Open in new window

0
 
lauriecking0623Author Commented:
Thank you. I will incorporate it and let's see how it works. I will definitely let you know. Just bear with me since I am new to the exchange so I do not know all of the rules. Thank you for your assistance so far.
0
 
Dale FyeCommented:
@laurie

I saw that you split your earlier post with 4 questions into 4 separate and distinct questions.  That is a good start.

You will find we are generally pretty patient and are here to help.  

If you have more than one person responding to you it can get confusing.  It is helpful if you preface your responses with @fyed or @boag, or something like, then make sure it is clear what post you are replying to by either quoting text, or referring to the message ID.

You will also find that responses to seem to come as quickly or as frequently on weekends, so don't get too impatient if you post a message on Sat morning and don't have an answer 2 hours later.
0
 
lauriecking0623Author Commented:
@fyed,

Thank you for the advice. I am still learning. Every group and listserv is different. I appreciate your assistance.

Laurie
0
 
lauriecking0623Author Commented:
This is great. I have changed my search form. I will put a new question up.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now