Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Aug 2001
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    struggling with search form (97)

    <img src=/S/frown.gif border=0 alt=frown width=15 height=15>
    I have 9 unbound text boxes that I am putting in an unbound search form. I have been trying all sorts of diff. code that I've found all over behind the on-click event. Essentially, I don't want to use a wildcard because I need the seach to be exact. If I put in the number 6 for the ReqNum text box, I want it to go and find Request number 6, not req number 6, 60, 600, 650, etc.... But I also want to leave the other fields blank if necessary, that is, if I don't need to search on those other fields. I want the user to type in whatever they are searching for, and have it come back in a datasheet view (for now.) Does anyone know how to do this without getting too difficult?

  2. #2
    Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: struggling with search form (97)

    I assume that each unbound text box on the form corresponds to one field on the table being filtered. You could try building a query against the table that has a criteria expression for each field that can be searched on. For example, the criteria for the ReqNum field might be this:

    =IIF(IsNull(Forms!SearchForm!ReqNum),Like "*",Like Forms!SearchForm!ReqNum)

    What this does is use Like "*" if the search field is null (to include all records) or Like the contents of the search field if the search field is not null. I think this will work and will return exact matches (unless the user enters a wildcard in the search field). The PC that I'm using doesn't have Access loaded yet, so I can't check it out. I'll check it out as soon as I can get to a copy of Access and send a follow-up if it isn't right.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: struggling with search form (97)

    To get started, you might consider using a simple form with unbound text boxes. Add a button, call it SEARCH for example, and have it run a query. You can do that with the wizard for buttons, but you will need to build the query first. The query should include all the fields you want to display or search on. In those fields where you want to search, put a criteria that looks like this:
    Forms!MySearchForm!SearchTextBox1
    Forms!MySearchForm!SearchTextBox2
    Forms!MySearchForm!SearchTextBox3 etc in the first criteria row. That will create an AND condition. If you want to do an OR condition, put them on separate lines in the criteria. Hope this helps.
    Wendell

  4. #4
    New Lounger
    Join Date
    Aug 2001
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: struggling with search form (97)

    Thanks for the start, but I'm still having a little trouble. I narrowed down the search query to only look at 6 fields, which are date fields and text fields. I have the ReqDate field as the first field in the query, which uses And as the criteria and the rest of the fields are using an Or as criteria. This is what I'm using for the critieria:
    IIf(IsNull([Forms]![Search]![ReqDate]),([Photocopy].[ReqDate]) Like "*",[Forms]![Search]![ReqDate])
    Now, the query is automatically filling in the Photocopy.ReqDate portion of the criteria. I'm not sure why. Also, when I try to do a search by ReqDate and Amount, it uses Or logic and shows all entries with that date and all entries with that $ amount. But it's not being exact, which I need it to be. I want to be able to have the user plug in the ReqDate and Amount and have that specific item come back, not entries that have both that date or dollar amount. Any more suggestions? Thanks so much for your help. I think I almost have it...

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: struggling with search form (97)

    <hr>I want to be able to have the user plug in the ReqDate and Amount and have that specific item come back, not entries that have both that date or dollar amount. <hr>
    Sorry, but that is not OR logic. You asked for and received all entries with that date AND that amount. You didn't ask to return a specific entry, only to return those that matched the criteria. If you want to get to a specific entry, then you have to supply whatever else makes that record unique.

    I think you're going to have to explain what exactly you're trying to do, because it seems that a few pieces were missing from the original question.
    Charlotte

  6. #6
    New Lounger
    Join Date
    Aug 2001
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: struggling with search form (97)

    I have a search form with 6 unbound text boxes. The name of each of the unbound text boxes refers to the field name in the query. The query is bound to a table with those field names. I want a user to be able to put in whatever information he wants in the search form using those 6 text boxes and only come up with the entry he's looking for. There is a search button that performs the query and the answers to the query are displayed in a datasheet view. I think the problem lies with the query in that, when I fill in the textbox ReqDate with a date and the textbox Amount with a dollar amount, it's not pulling what I would expect to see. What I want to see is only those items on that date with that dollar amount. What I am seeing is amounts for dates that I did not plug in the ReqDate field, which match the dollar amount that I did plug in. I want the answer to match exactly what I'm putting in the text boxes. I don't want the query to look at each text box individually and then display the results individually. I want it to take the text boxes and combine them and only display the entries that match all the criteria exactly. I guess it could be similar to a parameter query, but without the parameter statement. I want the "parameter" to be the text boxes on the search form. This doesn't mean that all six text boxes need to be filled in. But if a user plugs in the Date and Account number, only those items that have both of those criteria together in one entry will appear. I hope I'm explaining this better. Again, I thank you for your help.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: struggling with search form (97)

    It would help substantially if you could post the SQL in your query so we can see where it is going haywire. Open the query in the query designer and then choose the SQL view and copy and paste it to a post. Thanks.
    Wendell

  8. #8
    New Lounger
    Join Date
    Aug 2001
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: struggling with search form (97)

    Here is the SQL statement. Thank you!
    SELECT Photocopy.ReqDate, Photocopy.PostAcct, Photocopy.ItemAmount, Photocopy.CheckNum, Photocopy.PaidDate, Photocopy.SeqNum
    FROM Photocopy
    WHERE (((Photocopy.ReqDate)=IIf(IsNull([Forms]![Search]![ReqDate]),([Photocopy].[ReqDate]) Like "*",[Forms]![Search]![ReqDate]))) OR (((Photocopy.PostAcct)=IIf(IsNull([Forms]![Search]![PostAcct]),([Photocopy].[PostAcct]) Like "*",[Forms]![Search]![PostAcct]))) OR (((Photocopy.ItemAmount)=IIf(IsNull([Forms]![Search]![ItemAmount]),([Photocopy].[ItemAmount]) Like "*",[Forms]![Search]![ItemAmount]))) OR (((Photocopy.CheckNum)=IIf(IsNull([Forms]![Search]![CheckNum]),([Photocopy].[CheckNum]) Like "*",[Forms]![Search]![CheckNum]))) OR (((Photocopy.PaidDate)=IIf(IsNull([Forms]![Search]![PaidDate]),([Photocopy].[PaidDate]) Like "*",[Forms]![Search]![PaidDate]))) OR (((Photocopy.SeqNum)=IIf(IsNull([Forms]![Search]![SeqNum]),([Photocopy].[SeqNum]) Like "*",[Forms]![Search]![SeqNum])));

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: struggling with search form (97)

    HMM! - There are a couple of problems here:
    1 - All of the OR statements should be AND statements - otherwise you get all of the records all of the time
    2 - It turns out that the query parsing engine doesn't like the syntax of the criteria statements - if you make them all ANDs then it never returns any records unless you put in all of the search criteria. Not a good situation.

    The only way I can make this work (I've tried constructing several string critera using Likes, Nulls, etc.) is to create a query in VBA and make that the source of a form or report. In that case you compare the values of the various text boxes, and only insert criteria if the box is not null. It seems like there should be an alternative, but I haven't been able to find it. Sorry.
    Wendell

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: struggling with search form (97)

    Hi,
    In addition to changing your ORs to ANDs, I think you'll need to change your WHERE clause to something like:
    WHERE (((Photocopy.ReqDate) Like (IIf(IsNull([Forms]![Search]![ReqDate]),"*",[Forms]![Search]![ReqDate]))))
    etc. ( I hope I typed the right number of parentheses in there! )
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: struggling with search form (97)

    Rory is absolutely correct - I tried his syntax and it works. If you want to do it in the query designer rather than working in SQL, if you type Like (IIf(IsNull([Forms]![Search]![ReqDate]),"*",[Forms]![Search]![ReqDate])) in the criteria grid it should give you the right result - you will need to modify the criteria for each text box name, and make sure they go on the same line so you get an AND condition. Good Luck!
    Wendell

  12. #12
    New Lounger
    Join Date
    Aug 2001
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: struggling with search form (97)

    I hate to have to post this, and you don't need to read it line by line, but it actually works. I just wanted to show how long this was. It made the SQL itself. The statement I used was Forms![Search]![ReqNum] Or Forms![Search]![ReqNum] Is Null. The Query builder automatically made this SQL. It works in that it gives me exact results. Thanks a lot for all of your help! I tried using the other statments, but, unfortunately, it wasn't exact. From your experience, do you think an SQL this long will cause problems? I was thinking it might if there was a lot of entered data.


    SELECT Photocopy.ReqNum, Format([ReqDate],"mm/dd/yy") AS [Date], Photocopy.PostAcct, Photocopy.ItemAmount, Photocopy.CheckNum, Photocopy.SeqNum
    FROM Photocopy
    WHERE (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum])) OR (((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqDate]) Is Null)) OR (((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![ReqDate]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![PostAcct]) Is Null)) OR (((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null)) OR (((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ItemAmount]) Is Null)) OR (((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null)) OR (((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null)) OR (((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null)) OR (((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![CheckNum]) Is Null)) OR (((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null)) OR (((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null)) OR (((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null)) OR (((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null)) OR (((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null)) OR (((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null)) OR (((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null)) OR (((Photocopy.SeqNum)=[Forms]![Search]![SeqNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.CheckNum)=[Forms]![Search]![CheckNum]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND (([Forms]![Search]![CheckNum]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.ItemAmount)=[Forms]![Search]![ItemAmount]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.PostAcct)=[Forms]![Search]![PostAcct]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND ((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Format([ReqDate],"mm/dd/yy"))=[Forms]![Search]![ReqDate]) AND (([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR (((Photocopy.ReqNum)=[Forms]![Search]![ReqNum]) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null)) OR ((([Forms]![Search]![ReqNum]) Is Null) AND (([Forms]![Search]![ReqDate]) Is Null) AND (([Forms]![Search]![PostAcct]) Is Null) AND (([Forms]![Search]![ItemAmount]) Is Null) AND (([Forms]![Search]![CheckNum]) Is Null) AND (([Forms]![Search]![SeqNum]) Is Null));

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: struggling with search form (97)

    I can understand your reluctance to post it! <img src=/S/weep.gif border=0 alt=weep width=21 height=16> It obviously took a long time to build. As to the size of your query, they can be up to 64,000 characters, and you are just under 20,000. There is a limit of 40 ANDs in the WHERE clause according to Acc97 specs, but I think that is consecutive and you have intervening ORs. This sort of query would probably be pretty slow though if you had lots of records to select from. In this situation we usually build the SQL WHERE clause in VBA and then execute it using code behind the form.

    I'm also puzzled as to why Rory's suggestion didn't meet your needs. I constructed something using his post which looks like it should work based on my testing. It looks like this:
    SELECT Photocopy.ReqNum, Photocopy.ReqDate, Photocopy.PostAcct, Photocopy.ItemAmount, Photocopy.CheckNum, Photocopy.SeqNum
    FROM Photocopy
    WHERE (((Photocopy.ReqNum) Like (IIf(IsNull([Forms]![Search]![ReqNum]),"*",[Forms]![Search]![ReqNum])))
    AND ((Photocopy.ReqDate) Like (IIf(IsNull([Forms]![Search]![ReqDate]),"*",[Forms]![Search]![ReqDate])))
    AND ((Photocopy.PostAcct) Like (IIf(IsNull([Forms]![Search]![PostAcct]),"*",[Forms]![Search]![PostAcct])))
    AND ((Photocopy.ItemAmount) Like (IIf(IsNull([Forms]![Search]![ItemAmount]),"*",[Forms]![Search]![ItemAmount])))
    AND ((Photocopy.CheckNum) Like (IIf(IsNull([Forms]![Search]![CheckNum]),"*",[Forms]![Search]![CheckNum])))
    AND ((Photocopy.SeqNum) Like (IIf(IsNull([Forms]![Search]![SeqNum]),"*",[Forms]![Search]![SeqNum]))));

    Give it a try and see if it will work for you.
    Wendell

  14. #14
    New Lounger
    Join Date
    Aug 2001
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: struggling with search form (97)

    You know what, that suggestion does work. I see some flukey stuff going on with the item amount, however. When I use my search form, it automatically formats the field as currency with 2 dec places. I make sure that this format was across the board, but when I run the actual query, it doesn't return anything. The Search form does return item amounts. I'm sure I'll figure that out. Thanks for you help!!! . <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    You wouldn't know how to get the query information to show in a form? I would like to be able to open the search form, plug in the criteria, have the query run, and show the results in a form? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: struggling with search form (97)

    Now you're definitely talking VBA. What you need to do is set the data source on the form you want to display the query resutls to the SQL String, or you may want to apply it as a filter. In either case you need to set the property using VBA. Basically you want to have the form you are now using for search open the form you want to use to display results when the button is clicked. If you look at the VBA expresson docmd.openform
    you will see that it has options to specify a criteria for the form.

    The simplest way to get started with this is to create a button using the wizard, and tell it to open the display form to a specific record. That will let you see the basic code which should look like:
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "PhotocopyDisplay"

    stLinkCriteria = "[ReqNum]=" & Me![ReqNum]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    However the logic you will need to create your SQL string stLinkCriteria is more complated. In your case you have to look at each text box to see if it is empty, and if it's not then you concatenate another AND XXXX=me!textboxname to the criteria, so you will need to make it a VBA variable.

    Hope this helps - I think you are getting close to a solution.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •