Page 1 of 2 12 LastLast
Results 1 to 15 of 21

Database search form, problem with query return too few rows

  1. #1
    buenaventura is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    10

    Post Database search form, problem with query return too few rows

    Hello!

    I'm working in a government archive, and I have been tasked with converting a MS Excel spreadsheet my colleague has made, into a searchable database. The excel is a manually written copy of ledgers of patients to a local mental asylum from around 1850-1950, and we would like to have a GUI search interface where you can search thru the database. I've gotten part of the way - I've imported the excel sheet into MS Access 2013, made a search form and a query based on some tutorial I found, see some pictures here:

    So I have the query here: https://pastebin.com/1UrjMQj7

    And this is a picture of the form that is used:




    As you can see, it is in Swedish: essentially, you are supposed to be able to use the fields to search for name, last name, occupation, birth date etc. (all info found in the table), also you have to be able to leave fields blank and it should still return rows that either have something in those fields, or nothing. So my query of Erik there should return every row that has Erik in first name field 1, 2 or 3 (some people had multiple names in the ledger, and each got their own column). You can see in the query I've tried doing this using wildcard "*" and OR statements to allow any of the name fields to match.

    This is a picture of some rows in the database:

    (I've anonymized it somewhat - the records are well outside confidentiality period, but to be safe)

    Now when I search for Erik like above, it returns only four rows:


    But I know there are hundreds of Eriks in the database (a very common name). What am I doing wrong? Is there something special about MS Access wildcards and having empty fields perhaps? Or have I botched the string concatenation somehow?

    Also, if I get the query to work properly, I would like the results to be displayed in a read-only list, with a button to go back and possible the ability to print the result, and I would like this to be available to a end user in a researchers room we have at work, how would I go about this, roughly? You have any good tutorials or guides at hand?

    Thank you so much for your help!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,617
    What's the SQL of the query?

    Typically you'd have a form where the user would enter the search criteria and a query would point to that. A report would be based on that query.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    buenaventura is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    10
    Quote Originally Posted by pbaldy View Post
    What's the SQL of the query?

    Typically you'd have a form where the user would enter the search criteria and a query would point to that. A report would be based on that query.
    Hello! I linked to a pastebin page with the SQL of the query in it! here it comes in plain text too:

    Code:
    SELECT [Män hospital].År, [Män hospital].[Efternamn 1], [Män hospital].[Efternamn 2], [Män hospital].[Förnamn 1], [Män hospital].[Förnamn 2], [Män hospital].[Förnamn 3], [Män hospital].Ålder, [Män hospital].År2, [Män hospital].Månad, [Män hospital].Dag, [Män hospital].[Födelselän/-ort], [Män hospital].Boendeort, [Män hospital].Yrke, [Män hospital].Utskriven, [Män hospital].OrsakFROM [Män hospital]
    WHERE (
    
    
    (([Män hospital].År) Like "*" & [Forms]![Sok]![inskrAr] & "*") AND 
    
    
    ((([Män hospital].[Efternamn 1]) Like "*" & [Forms]![Sok]![Efternamn] & "*") OR (([Män hospital].[Efternamn 2]) Like "*" & [Forms]![Sok]![Efternamn] & "*")) AND 
    
    
    ((([Män hospital].[Förnamn 1]) Like "*" & [Forms]![Sok]![Fornamn] & "*") OR 
    	(([Män hospital].[Förnamn 2]) Like "*" & [Forms]![Sok]![Fornamn] & "*") OR 
    	(([Män hospital].[Förnamn 3]) Like "*" & [Forms]![Sok]![Fornamn] & "*")) AND
    
    
    (([Män hospital].Ålder) Like "*" & [Forms]![Sok]![Alder] & "*") AND 
    (([Män hospital].År2) Like "*" & [Forms]![Sok]![fAr] & "*") AND 
    (([Män hospital].Månad) Like "*" & [Forms]![Sok]![fMan] & "*") AND 
    (([Män hospital].Dag) Like "*" & [Forms]![Sok]![fDag] & "*") AND 
    (([Män hospital].[Födelselän/-ort]) Like "*" & [Forms]![Sok]![fLan] & "*") AND 
    (([Män hospital].Boendeort) Like "*" & [Forms]![Sok]![boendeOrt] & "*") AND 
    (([Män hospital].Yrke) Like "*" & [Forms]![Sok]![Yrke] & "*") AND 
    (([Män hospital].Utskriven) Like "*" & [Forms]![Sok]![utskrDatum] & "*") AND 
    (([Män hospital].Orsak) Like "*" & [Forms]![Sok]![orsak] & "*")
    
    
    );

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,617
    Sorry, I don't click on unknown links. It looks like you've got the parentheses right. You may have to add something to handle the blank controls, along these lines:

    http://theaccessweb.com/queries/qry0001.htm

    So each form reference would include the "OR Forms!... Is Null" bit. I tend to build dynamic SQL when there are multiple optional search items.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    buenaventura is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    10
    Quote Originally Posted by pbaldy View Post
    Sorry, I don't click on unknown links. It looks like you've got the parentheses right. You may have to add something to handle the blank controls, along these lines:

    http://theaccessweb.com/queries/qry0001.htm

    So each form reference would include the "OR Forms!... Is Null" bit. I tend to build dynamic SQL when there are multiple optional search items.
    Aha! Thats a very concise nice answer you found, thank you! I'll try this and report back!

    Edit: also indeed, very reasonable to not click unknown links

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,617
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    buenaventura is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    10
    Quote Originally Posted by pbaldy View Post
    Happy to help!
    Hello again! I've had a long summer break and recently got back to this little project as described above! I tried applying the fix in your link, as follows (and excuse me if I am doing something obviously stupid here, I'm not sure I understand 100%):

    Code:
    SELECT [Män hospital].År, [Män hospital].[Efternamn 1], [Män hospital].[Efternamn 2], [Män hospital].[Förnamn 1], [Män hospital].[Förnamn 2], [Män hospital].[Förnamn 3], [Män hospital].Ålder, [Män hospital].År2, [Män hospital].Månad, [Män hospital].Dag, [Män hospital].[Födelselän/-ort], [Män hospital].Boendeort, [Män hospital].Yrke, [Män hospital].Utskriven, [Män hospital].OrsakFROM [Män hospital]
    WHERE (
    
    
    (([Män hospital].År) Like "*" & [Forms]![Sok]![inskrAr] & "*" OR ([Män hospital].År) Is Null) AND 
    
    
    ((([Män hospital].[Efternamn 1]) Like "*" & [Forms]![Sok]![Efternamn] & "*" OR ([Män hospital].[Efternamn 1]) Is Null) OR 
    (([Män hospital].[Efternamn 2]) Like "*" & [Forms]![Sok]![Efternamn] & "*" OR ([Män hospital].[Efternamn 2]) Is Null)) AND 
    
    
    ((([Män hospital].[Förnamn 1]) Like "*" & [Forms]![Sok]![Fornamn] & "*" OR ([Män hospital].[Förnamn 1]) Is Null) OR 
        (([Män hospital].[Förnamn 2]) Like "*" & [Forms]![Sok]![Fornamn] & "*" OR ([Män hospital].[Förnamn 2]) Is Null) OR 
        (([Män hospital].[Förnamn 3]) Like "*" & [Forms]![Sok]![Fornamn] & "*" OR ([Män hospital].[Förnamn 3]) Is Null)) AND
    
    
    (([Män hospital].Ålder) Like "*" & [Forms]![Sok]![Alder] & "*" OR ([Män hospital].Ålder) Is Null) AND 
    (([Män hospital].År2) Like "*" & [Forms]![Sok]![fAr] & "*" OR ([Män hospital].År2) Is Null) AND 
    (([Män hospital].Månad) Like "*" & [Forms]![Sok]![fMan] & "*" OR ([Män hospital].Månad) Is Null) AND 
    (([Män hospital].Dag) Like "*" & [Forms]![Sok]![fDag] & "*" OR ([Män hospital].Dag) Is Null) AND 
    (([Män hospital].[Födelselän/-ort]) Like "*" & [Forms]![Sok]![fLan] & "*" OR ([Män hospital].[Födelselän/-ort]) Is Null) AND 
    (([Män hospital].Boendeort) Like "*" & [Forms]![Sok]![boendeOrt] & "*" OR ([Män hospital].Boendeort) Is Null) AND 
    (([Män hospital].Yrke) Like "*" & [Forms]![Sok]![Yrke] & "*" OR ([Män hospital].Yrke) Is Null) AND 
    (([Män hospital].Utskriven) Like "*" & [Forms]![Sok]![utskrDatum] & "*" OR ([Män hospital].Utskriven) Is Null) AND 
    (([Män hospital].Orsak) Like "*" & [Forms]![Sok]![orsak] & "*" OR ([Män hospital].Orsak) Is Null)
    
    
    );
    However whatever I input anywhere in the form now, it returns every single record? What am I doing wrong?

    EDIT: I think I did it! I wrote like this instead:

    Code:
    WHERE ((([Män hospital].År) Like "*" & [Forms]![Sok]![inskrAr] & "*" Or ([Män hospital].År) Is Null) AND
    
    
    (([Män hospital].[Efternamn 1]) Like "*" & [Forms]![Sok]![Efternamn] & "*" OR ([Män hospital].[Efternamn 1]) Is Null) AND
    (([Män hospital].[Efternamn 2]) Like "*" & [Forms]![Sok]![Efternamn] & "*" OR ([Män hospital].[Efternamn 2]) Is Null) AND
    
    
    (([Män hospital].[Förnamn 1]) Like "*" & [Forms]![Sok]![Fornamn] & "*" OR ([Män hospital].[Förnamn 1]) Is Null) AND 
    (([Män hospital].[Förnamn 2]) Like "*" & [Forms]![Sok]![Fornamn] & "*" OR ([Män hospital].[Förnamn 2]) Is Null) AND
    (([Män hospital].[Förnamn 3]) Like "*" & [Forms]![Sok]![Fornamn] & "*" OR ([Män hospital].[Förnamn 3]) Is Null) AND
    
    
    (([Män hospital].Ålder) Like "*" & [Forms]![Sok]![Alder] & "*" OR ([Män hospital].Ålder) Is Null) AND 
    (([Män hospital].År2) Like "*" & [Forms]![Sok]![fAr] & "*" OR ([Män hospital].År2) Is Null) AND 
    (([Män hospital].Månad) Like "*" & [Forms]![Sok]![fMan] & "*" OR ([Män hospital].Månad) Is Null) AND 
    (([Män hospital].Dag) Like "*" & [Forms]![Sok]![fDag] & "*" OR ([Män hospital].Dag) Is Null) AND 
    (([Män hospital].[Födelselän/-ort]) Like "*" & [Forms]![Sok]![fLan] & "*" OR ([Män hospital].[Födelselän/-ort]) Is Null) AND 
    (([Män hospital].Boendeort) Like "*" & [Forms]![Sok]![boendeOrt] & "*" OR ([Män hospital].Boendeort) Is Null) AND 
    (([Män hospital].Yrke) Like "*" & [Forms]![Sok]![Yrke] & "*" OR ([Män hospital].Yrke) Is Null) AND 
    (([Män hospital].Utskriven) Like "*" & [Forms]![Sok]![utskrDatum] & "*" OR ([Män hospital].Utskriven) Is Null) AND 
    (([Män hospital].Orsak) Like "*" & [Forms]![Sok]![orsak] & "*" OR ([Män hospital].Orsak) Is Null)
    
    
    );
    And now it seems to work as intended. But now comes the followup question - how do I make this into a search form that I can put on a public facing (not online, but its computer we have in our researcher's room) computer somehow? It needs:

    1. The results need to be presented in a better way, like preferably in a list box underneath the search fields in the search form
    2. They need to be presented without the ability to edit any of them or the database or anything

    How should I do this?

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,617
    You could base either a listbox or subform on your query and requery either when selections were updated. A listbox would be read-only anyway, a subform could be locked so no edits were allowed. If you only put the runtime version of Access on the computer I don't think the users can get into the table directly.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    buenaventura is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    10
    All right, I did the subform version, and I made a button that updates it, works well! Thanks for your help. Now I'm on to creating a listbox where you choose which table you wanna search in (the excel documents had multiple pages formatted in the same way, one for each subsection asylum/hospital, male/female). So I would like to modify the SELECT-statement in the query that is the origin of the subform, so that it takes from the selected table in the list box there. I guess I would try and get my search button to do this? Or what would be the natural/easy approach?
    Click image for larger version. 

Name:	accessforum.jpg 
Views:	34 
Size:	134.4 KB 
ID:	39704

  10. #10
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    380
    Now I'm on to creating a listbox where you choose which table you wanna search in (the excel documents had multiple pages formatted in the same way, one for each subsection asylum/hospital, male/female).
    Very often data stored in a spreadsheet does not translate well to access and needs to be normalized. It would probably be helpful if you described what your data looks like. Why do you need to select a table to search? Are the tables all the same? Is data being stored AS the table? (ie. a table for each hospital)

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,617
    As moke implies, normally the data wouldn't be in different tables, it would be in one table with a field for gender or hospital/asylum. Then it's just another search criteria. If you're stuck with this design, you could manipulate the source of the subform in the after update event of the listbox. In your case maybe a different query. Personally I tend to create dynamic SQL with more than a couple of optional search criteria, so I'd just build it into that.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    buenaventura is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    10
    Yeah thats true - I am pretty sure the information in each page is formatted in the exact same way, so I guess it makes sense to put it all in the same table and add 2 columnsthat describes the origin (Asyl/Hospital and Male/Female). I guess I would just add two columns to the existing table and add Män and Hospitalet there, and then in Excell add the same columns to those pages before I import them into access, that seems to be the easiet way. And then I dont have to worry about switching tables. Thanks!

  13. #13
    buenaventura is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    10
    Hello again!

    I have now done as you suggested and just added the records into one big table instead, and it works nice! However now I am running into problems with the SQL again. I discovered a bug,

    1. when searching using the search fields Förnamn or Efternamn in the form, I want it to find records where the entered string is in any of the 3 Fornamn-fields/2 Efternamn-fields in the database. As it is now, it works if I search for names that are present in the Fornamn 1 field, but if I search for a name I know is in Efternamn 2, it wont find that record. The same applies to searching in the Efternamn field. I tried solving it on the Fornamn field search by nesting it (as you can see below) but now it wont work at all, it just returns all records no matter what I type in the Förnamn search field.

    Code:
    SELECT StLars2.[Journalnummer 1], StLars2.[Journalnummer 2], [StLars2].År, [StLars2].[Efternamn 1], [StLars2].[Efternamn 2], [StLars2].[Förnamn 1], [StLars2].[Förnamn 2], [StLars2].[Förnamn 3], [StLars2].Ålder, [StLars2].År2, [StLars2].Månad, [StLars2].Dag, [StLars2].[Födelselän/-ort], [StLars2].Boendeort, [StLars2].Yrke, [StLars2].Utskriven, [StLars2].Orsak, [StLars2].Avdelning
    FROM StLars2
    WHERE (  
    
    (([StLars2].År) Like "*" & Forms!Sok!inskrAr & "*" Or ([StLars2].År) Is Null) And   
    (([StLars2].[Efternamn 1]) Like "*" & Forms!Sok!Efternamn & "*" Or ([StLars2].[Efternamn 1]) Is Null) And  
    (([StLars2].[Efternamn 2]) Like "*" & Forms!Sok!Efternamn & "*" Or ([StLars2].[Efternamn 2]) Is Null) And   
    
    (
    (([StLars2].[Förnamn 1]) Like "*" & Forms!Sok!Fornamn & "*" Or ([StLars2].[Förnamn 1]) Is Null) Or  
    (([StLars2].[Förnamn 2]) Like "*" & Forms!Sok!Fornamn & "*" Or ([StLars2].[Förnamn 2]) Is Null) Or 
    (([StLars2].[Förnamn 3]) Like "*" & Forms!Sok!Fornamn & "*" Or ([StLars2].[Förnamn 3]) Is Null) 
    ) And   
    
    (([StLars2].Ålder) Like "*" & Forms!Sok!Alder & "*" Or ([StLars2].Ålder) Is Null) And  
    (([StLars2].År2) Like "*" & Forms!Sok!fAr & "*" Or ([StLars2].År2) Is Null) And  
    
    (([StLars2].Månad) Like "*" & Forms!Sok!fMan & "*" Or ([StLars2].Månad) Is Null) And  
    (([StLars2].Dag) Like "*" & Forms!Sok!fDag & "*" Or ([StLars2].Dag) Is Null) And   
    (([StLars2].[Födelselän/-ort]) Like "*" & Forms!Sok!fLan & "*" Or ([StLars2].[Födelselän/-ort]) Is Null) And  
    (([StLars2].Boendeort) Like "*" & Forms!Sok!boendeOrt & "*" Or ([StLars2].Boendeort) Is Null) And  
    (([StLars2].Yrke) Like "*" & Forms!Sok!Yrke & "*" Or ([StLars2].Yrke) Is Null) And  
    (([StLars2].Utskriven) Like "*" & Forms!Sok!utskrDatum & "*" Or ([StLars2].Utskriven) Is Null) And  
    (([StLars2].Orsak) Like "*" & Forms!Sok!orsak & "*" Or ([StLars2].Orsak) Is Null)  
    
    );
    I'm thankful for any input what I am doing wrong! I think there is something wrong with my Or/And ordering or something but I cant figure it out

    Also, I will be wanting to export this database+search form into some form that I can put on a offline computer, that makes it so that when you click an icon it opens a read-only version of Access, that opens this search form only, and you cant change anything like I can at the moment. What would be the best approach to doing that?

    Thanks alot for your help!

  14. #14
    buenaventura is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    10
    Hey I dont know how to edit my last message, but I solved my problem by consolidating the fields, so there is now only 1 first name and 1 last name field (and some of them contain multiple names but thats fine). So thats good!

    Still looking for advice on how to put this on an offline researcher's computer in our researcher's lab, so that you can just click it and get my search form to search in!

    Thanks

  15. #15
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    380
    Still looking for advice on how to put this on an offline researcher's computer in our researcher's lab, so that you can just click it and get my search form to search in!
    In backstage options, set display form to your search form.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-31-2018, 08:47 AM
  2. Replies: 1
    Last Post: 09-27-2017, 12:11 AM
  3. Replies: 3
    Last Post: 09-06-2014, 01:25 PM
  4. Replies: 1
    Last Post: 12-31-2012, 06:25 PM
  5. Replies: 11
    Last Post: 09-02-2010, 01:59 PM

Tags for this Thread

Posting Permissions

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