Ok I have adjusted what I sent previously.
Your form now has a combo. You select the Output destination
Form; Report - Preview; Report - Print
Then enter your terms. Then hit the Search Titles button.
Give it a shot.
Ok I have adjusted what I sent previously.
Your form now has a combo. You select the Output destination
Form; Report - Preview; Report - Print
Then enter your terms. Then hit the Search Titles button.
Give it a shot.
I have a very similar problem to Meep, in that I want a keyword search, but I want only one search to search through multiple fields. How would I change the code to search through more than just the title? For example in Meep's case, the keyword search would go through titles, author name's, pick up locations, etc.
This is great, orange. One more thing: how do I add grouping to the FinalList query without breaking things? I keep getting errors, like enter parameter value for "Title". I changed the control source in the report from "title" to "FirstOfTitle," so it's not that.
Please explain.
The report and the form is all working off the dbo_items. This is constrained by a where clause that is applied after your keyword search is done. It's all working against your keyword search against Title.
You have 1 text box for all search terms or fragments which are separated by at least 1 space; and you have the option of a Form, A Report Preview, and a Report Print. What did I miss?
Have you tested the search and outputs with various parameters/keywords?
Yes it could be done. Do you have a test database in mdb format?I have a very similar problem to Meep, in that I want a keyword search, but I want only one search to search through multiple fields. How would I change the code to search through more than just the title? For example in Meep's case, the keyword search would go through titles, author name's, pick up locations, etc.
I have access 2003 and can not use an accdb database.
Also, could you describe your application as it applies to search?
Yes, everything is working fine. I just want to group the query by ItemBarcode (everything else as "First") so items with the same barcode don't show in the report twice. (I could group the report, but then I can't alphabetize it.)
When you do a totals query in which you elect to show the first record (per group) of a given field, the field's name changes; so Title field becomes FirstOfTitle. I think this change might be causing a problem in the code maybe (?).
You are getting multiple records because of the CallNumber. You have multiple callnumbers per barcode. Barcode is not a unique identifier to your records.
In plain English what is the keyword search result supposed to be?
You're correct, I should have said multiple records per ItemBarcode.
The same ItemBarcode for example 088452011 has 2 records, same call number, but different CurrentStatus. So these records are distinct.
Do you only want the record with a specific Status? You can restrict the search to a single record per ItemBarCode, just tell me which record to ignore.
Really, it doesn't matter. But ignore the one "available on permanent reserves." Could you tell me what changes you make so I know what to change in my current database (based on yours) to which I've made many additions? Can I just copy and paste the new module? Thank you!You're correct, I should have said multiple records per ItemBarcode.
The same ItemBarcode for example 088452011 has 2 records, same call number, but different CurrentStatus. So these records are distinct.
Do you only want the record with a specific Status? You can restrict the search to a single record per ItemBarCode, just tell me which record to ignore.
I just checked 089534206. There are 2 records that seem identical and both indicate
Item Available on Permanent Reserves
Your sample data has 545 unique itemBarCodes
but 596 Records
In any event, I only want to see unique barcodes. It doesn't matter which duplicate record(s) disappears. Is that a problem? Although, the one you found is an outlier, so I would still be okay with ignoring the duplicate barcode on permanent reserves (because I could correct that particular duplicate).
No, part of the issue is that your records have slight variations. If you check
088452011 you will see that the Titles are different. One has an unprintable character
Asc(30) at the end. This forces the 2 records to be different.
The key,for me, is to get the unique ItemBarcodes and Titles, since we are searching Titles and returning unique itembarcodes. I'm going to try a query to include only Barcode and Title, and drop the titles that contain the unprintable character.
Just tried the query, unfortunately 250 Titles have the unprintable character somewhere in the Title.
Here's a picture of the query showing the Title and ItemBarCode.
You can see the funny character - which is not in all records and in some cases in only one of the duplicate titles. You can also see that these occur in 250 records.
I can adjust the records I have to ignore them, but it will be an issue in your original data, where you process that data.
I have to go out for an hour or so but will be back later.
Ok, here's a version that deals with unique ItemBarCode per Title.
It requires a setup that is done on the first form. It takes the dbo_items and ignores multiple/replicated ItemBarCodes.
You click the prep button and then your Form appears. I have made the Author stuff on your form invisible. It's still there, but invisible.
You can use mutliple keywords or fragments or single characters separated by at least 1 space. Then click the Search just like before. The results are shown on the FoundItems form. These are in ItembarCode ascending sequence, and only 1 ItemBarCode per Title.
You can check FoundItems, if there are too many results, you can add a keyword or fragment or single character, click the search and get a reduced number.
When you are on FoundItems form, you can double click the ItemBarCode to put that record on a single page form.
Once you have the set of records you want, you can adjust the Output type and rerun the search. The report will contain only 1 ItemBarCode per Title.
Good luck.
Last edited by orange; 12-23-2011 at 07:54 AM. Reason: spelling