Page 3 of 5 FirstFirst 12345 LastLast
Results 31 to 45 of 73
  1. #31
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.

  2. #32
    eliasgirl01 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    5
    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.

  3. #33
    Meep is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    25
    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.

  4. #34
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Quote Originally Posted by Meep View Post
    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?

  5. #35
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Quote Originally Posted by eliasgirl01 View Post
    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.
    Yes it could be done. Do you have a test database in mdb format?
    I have access 2003 and can not use an accdb database.
    Also, could you describe your application as it applies to search?

  6. #36
    Meep is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    25
    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 (?).

  7. #37
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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?

  8. #38
    Meep is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    25
    Quote Originally Posted by orange View Post
    You have multiple callnumbers per barcode.
    No, I don't.

    Plain English: I want only unique barcodes (i.e., unique call numbers) to appear in the report. I can't remove duplicates from the table because I don't control the table, so I have to group.

  9. #39
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.

  10. #40
    Meep is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    25
    Quote Originally Posted by orange View Post
    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!

  11. #41
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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

  12. #42
    Meep is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    25
    Quote Originally Posted by orange View Post
    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).

  13. #43
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.

  14. #44
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.

  15. #45
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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

Page 3 of 5 FirstFirst 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. SQL Parameters keyword?
    By Buakaw in forum Queries
    Replies: 1
    Last Post: 08-04-2011, 06:53 PM
  2. Incorrect syntax near keyword “ORDER"
    By k9drh in forum Import/Export Data
    Replies: 1
    Last Post: 06-13-2011, 07:36 AM
  3. Replies: 7
    Last Post: 04-29-2011, 03:44 PM
  4. Making a keyword search
    By timmy in forum Forms
    Replies: 9
    Last Post: 03-14-2011, 02:57 AM
  5. Extracting data after a keyword
    By KerryA in forum Queries
    Replies: 1
    Last Post: 02-11-2010, 09:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums