Query attached to Searsh form not functioning properly

[kw62583]

I am having some trouble with a database I am building to track aircraft trends. I am trying to build a search form to query my main table for records using: Like "*" & [Forms]![Search]![Aircraft Type] & "*". When I entered this into the first two fields, replacing [Aircraft Type] with the appropriate field name. I didn’t have a problem, however when I entered it into my third field my query “broke”, no matter what I enter it returns the same three records (see picture below of search results). I remove criteria for that field and it searches fine. Is there a problem with my criteria, the format of the data in that field or is it because some records have no data in that field? Is there a better method of searching my database? Also when I click my search button I want it to leave the query table in the background and have a box that updates with the number of records found to match my query, how do I do this? I have attached some pictures of my database so you can see what I am working with.

[NTC]

although your screen shots help I can't follow your post in regard to your stated 'third' field... queries are never wrong so you can be sure it is not broken nor a bug. When you have multiple criteria in the same row of query design they are AND statements and so it will only return records that contain all 3 criteria....

on a different point - wrapping your search fields in wild cards is somewhat an awkward design whereby the user must know what to type... you might consider changing your search form fields to comboboxes that lookup values that exist in the database.... this will probably also make your query design easier....

[kw62583]

The"third" field I was referring to is the third field in my query, theAircraft Type field. As you can see inthe first picture, data is entered into this field such as C-130, KC-135, orC-17 but there are also some records with no data in that field. Also I realize it is technically not brokenbecause it is still pulling results but to me it is “broken” because it stoppedworking as intended or desired.
I got the formula(yes I realize that’s not the proper term but I can’t think of what it’sreferred to in Access) from an access tutorial on building a database searchform I found on you-tube. Myunderstanding of it is basically, it tells the query where to look for thecriteria and as long as a record contains, in part, what is in the source it isa match. Also I do want it to query inan AND function so I can narrow down my results very specifically. When I applied it to this database, initially,it worked as desired but I only had the formula in the Tracking # and Statusfields though. I could type part of whatI was looking for in the field and it would return all records that containedwhat I typed. So if I only entered o inthe status field it would return all Open & Closed records, or if I entered1 in the Tracking # field it would return all records with a 1 in the Tracking#. If I left the form completely blank itwould pull all records and if I entered 2 in the Tracking # field and Open inthe Status field it would pull all Open records with a 2 in the Tracking#. Additionally if nothing matched mycriteria it would pull nothing.
After I tested itand it appeared to be working as desired I added it to the Aircraft Type fieldand that’s when it quit functioning properly. Now no matter what I enter it pulls the same three results, see the 4^thpicture from my original post, even if all fields are blank. In trying tofigure out what the problem was I removed the formula from the Status field andat one point even the Tracking # field, leaving it only in the Aircraft Typefield, but it didn’t affect the results of the query. I’m thinking the records with blank fieldsare effecting the formula I used but I do not know how to modify it. What I want is for it to exclude blanks,unless the field is blank, in the search form. As for the complexity of the query design it seemed straight forward tome and far less complex than some of the other examples I came across. All data is in one table so there are norelationships to worry about, it is simply the fields I want in a report andthen in the fields I was to use as criteria all the formula. Also I did try to use a combo box for thisfield and the results were the same.
I am good with access on a basic level and greatlyappreciate your time and assistance. Ifyou need clarification on anything in my post let me know and I will try toclarify. Also if you think uploading mydatabase would help I can try. Granted Iam still building it, I hit this snag and stopped until I get this figuredout. There are a few other things I amplanning on doing to this form that I may need some additional assistance withonce I get past this.

[ssanfu]

I got the formula(yes I realize that’s not the proper term but I can’t think of what it’sreferred to in Access)

I think you are referring to "Criteria".

I would suggest fixing the field names first. Should use only letters and numbers in object names. NO spaces, special characters or punctuation (except the underscore).
The hash mark (#) is a delimiter and will/could confuse Access.

Also, "Date", "Time", System" and "Class" are reserved words in Access and shouldn't be used for object names..........

[kw62583]

ssanfu, Well I was able to find part my problem and fix it thanks to your suggestion. It wasn't actually that I had spaces or poorly named fields, when I was typing in my criteria statement I was using the auto fill to help me and was selecting the wrong field from the list. I'm not sure if I just explained that properly but basically as I was entering the statement, when I got to the [ access popped up a little window allowing me to select the type of object I was referencing, after I closed the bracket and entered the ! it gave me another one for object name, and finally what field in that object and as I typed it narrowed it down. Then I just selected the name of the object I wanted to use as the source for the criteria. Apparently, there are two different types of objects listed for the form, one has a wrench and hammer icon the other has a table icon. The wrench and hammer icon is the text field but I don't know what the other field is. Well apparently I had selected the wrong type of field. Now my query is working again, however it still will not pull records with empty fields. But I know how finicky Access can be sometimes to I if it does not like spaces and #'s I'll go in there and rename my fields. So now that the query is working again, how do I get access to pull all records including blank ones if the search block is blank? I'm guessing it would be some form of IF statement?

[ssanfu]

See if this helps:
http://access.mvps.org/access/queries/qry0001.htm

[kw62583]

Thanks, I'll give that a try. I'm not sure if it will give me the desired result but it will be closer that I currently am. I am thinking I may have to make all fields mandatory and either add a default value of "N/A" or a combo box with "N/A" as an option in the list.

[kw62583]

Actually it appears that is my saving grace. I added that to the or line and it looks like it is working like I want. I leave my search form blank and it pulls all records, I enter something into the applicable field and it only pulls records related to my entry. More in depth testing to come later on but as of now it appears to be working perfectly. Thanks a lot.

[kw62583]

Ok, after further testing it does not appear to be producing the desired effect. At this point I think I need to start from scratch on my query. What is the best way to build a QBF? I have googled quite a bit now and basically I'm lost. I have tried to follow a few of the examples I have found and either they do not work as advertised or I get lost in the coding. The last one resulted in me having to delete my query because once I saved and closed it so I could test it all of the OR fields multiplied and I ended up with over 100 lines of or criteria in 11 fields. The intent is for the user to be able to enter the desired criteria into the form and have a query pull all applicable records such as: all records that Lewis as the POC; all records for a specific aircraft; or all open records that Lewis is working on, even if there are null values in those records. I will also build a report based on the query and the results will be used for trend analysis. I know there is a way to do it because I have used quite a few DB's with similar search forms, I just don't know how they achieved this. There is one I have access to but it is done via VB code (I think) and I don't understand it.
Here is a list of what I am looking to get out of my QBF:
1: Use multiple fields to narrow results (and function)
2: If a field is blank include Null results
3: If a field is not blank ignore Null results
4: Pull partial matches (ie: enter "o" and all records that contain an "o" are pulled)

[ssanfu]

What is the best way to build a QBF?

I have never used QBF.

The last one resulted in me having to delete my query because once I saved and closed it so I could test it all of the OR fields multiplied and I ended up with over 100 lines of or criteria in 11 fields.

Yes, you have to have a criteria line for every control on the form.

I know there is a way to do it because I have used quite a few DB's with similar search forms, I just don't know how they achieved this. There is one I have access to but it is done via VB code (I think) and I don't understand it.

That is the way I build my search forms: using VBA and unbound controls on a form.

Here is an article by Allen Browne about searching
http://www.allenbrowne.com/ser-62.html

And here is the code
http://www.allenbrowne.com/ser-62code.html

Obviously, you would have to modify the code for your table, fields and controls.

Would you care to post your dB with some records?

[kw62583]

Thanks, My Access knowledge is completely self taught and my experience is limited in some ways, until now I have built/rebuild several DB's but managed to avoid coding and only had limited macro usage. To search out data, I have always built multiple fixed queries. One of the DB's I used at my previous work center had a search form that functioned like I want this one to but it was build with code by a temp who was good with coding. The last time I looked at what he did I was completely lost, but my Access knowledge has grown quite a bit since then. I understand I need the criteria for each control on the form, I'm assuming control = empty txt box? Also my form and all fields are unbound and I don't know if it helps but the DB was created in Access 2010 and I'm currently using 2013 to edit it. I tried to upload a copy of my DB with my last post but it wouldn't upload for some reason. The internet connection at my current location is fairly slow with very limited bandwidth, I think that was my problem. I'm going to rebuild the query and try to upload the DB so you can check it out. You may be able to spot my mistake, and I'm sure there are a few. I will also read over the article you posted and try my hand at coding which is completely new to me.

I may not have explained what the query did very well. Of the 20 or so fields in my query only 11 have criteria, the rest have no criteria. I entered "[Forms]![Search]![fieldname] or [Forms]![Search]![fieldname] is null" in the criteria for each field, which would have been simple to delete, I then pressed "run query" to do a quick test and it seemed to work. After that I saved my changes and closed the query, went to my form and attempted a search. The search should have pulled 3 records but the query was empty. When I checked the design view for my query there were 11 new fields at the end of my query, all a variation of "[Forms]![Search]![fieldname], still not that hard to edit out. But it also added well over 100 lines of Or criteria to each field, all variations of " or [Forms]![Search]![fieldname] is null. I can only assume there was an Or line for each potential combination of fields.

[kw62583]

Apparently I am limited to a 500kb .accdb file size, my database is 9.5mb. If you would like I can e-mail it to you.

[cyanidem]

Try Database Tools-> Compact and repair, then zip your accdb file. I believe you're allowed to attach up to 2MB zips.