Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21
  1. #16
    buenaventura is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    10
    Perfect, that seems to be just what I wanted thanks!



    OK so moving on, I have identified another problem I'm not sure how to solve, this is my query:

    Code:
    SELECT StLars.[Journalnummer 1], StLars.[Journalnummer 2], [StLars].År, [StLars].[Efternamn], [StLars].[Förnamn], [StLars].Ålder, [StLars].År2, [StLars].Månad, [StLars].Dag, [StLars].[Födelselän/-ort], [StLars].Boendeort, [StLars].Yrke, [StLars].Utskriven, [StLars].Orsak, [StLars].AvdelningFROM StLars
    WHERE (  
    
    
    (([StLars].År) Like "*" & Forms!Sok!inskrAr & "*" Or ([StLars].År) Is Null) And
    
    
    (([StLars].[Efternamn]) Like "*" & Forms!Sok!Efternamn & "*" Or ([StLars].[Efternamn]) Is Null) And
    
    
    (([StLars].[Förnamn]) Like "*" & Forms!Sok!Fornamn & "*" Or ([StLars].[Förnamn]) Is Null) And
    
    
    (([StLars].Ålder) Like "*" & Forms!Sok!Alder & "*" Or ([StLars].Ålder) Is Null) And  
    (([StLars].År2) Like "*" & Forms!Sok!fAr & "*" Or ([StLars].År2) Is Null) And  
    
    
    (([StLars].Månad) Like "*" & Forms!Sok!fMan & "*" Or ([StLars].Månad) Is Null) And  
    (([StLars].Dag) Like "*" & Forms!Sok!fDag & "*" Or ([StLars].Dag) Is Null) And   
    (([StLars].[Födelselän/-ort]) Like "*" & Forms!Sok!fLan & "*" Or ([StLars].[Födelselän/-ort]) Is Null) And  
    (([StLars].Boendeort) Like "*" & Forms!Sok!boendeOrt & "*" Or ([StLars].Boendeort) Is Null) And  
    (([StLars].Yrke) Like "*" & Forms!Sok!Yrke & "*" Or ([StLars].Yrke) Is Null) And  
    (([StLars].Utskriven) Like "*" & Forms!Sok!utskrDatum & "*" Or ([StLars].Utskriven) Is Null) And  
    (([StLars].Orsak) Like "*" & Forms!Sok!orsak & "*" Or ([StLars].Orsak) Is Null)  
    
    
    );
    Now this works for the moment, and I added these "Is Null" to get around the fact that many rows in the table have only some of the fields filled in at all. But now I run into the problem that, if I for example search for Yrke (Occupation): Lokförare (locomotive driver) I get all rows that feature that string in that field, but also all rows where that field is empty! It should only find those fields that have that string in it in that field. But when I search in name, I of course want to find all of the ones with that name, even if the Yrke-field is empty. I dont know how to phrase the question to accomplish this?

    Thanks again for your help.

  2. #17
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Personally, I do my search forms differently and build the sql dynamically.

    for instance if I had the fields FirstName, MiddleName, and LastName and text boxes on my form txtFirst, txtMiddle, and txtLast.

    I would build the sql with only the fields\textboxes that have a value. (aircode so may need adjustments)

    Code:
    If nz(me.txtFirst,"") <> "" then
         dynSQL = dynSQL & "FirstName like ""*" & Me.txtFirst & "*"" and "
    End if
    
    If nz(me.txtMiddle,"") <> "" then
         dynSQL = dynSQL &  "MiddleName like ""*" & Me.txtMiddle & "*"" and "
    End if
    
    If nz(me.txtLast,"") <> "" then
         dynSQL = dynSQL &  "LastName like ""*" & Me.txtLast & "*"" and "
    End if
    
    If dynSql <> "" Then
         dynSQL = left(dynSQL,len(dynSQL)-4)  'trim off last "and"
    End if
    
    dynSQL = "Select * from SomeTable where " & dynSQL
    
    debug.print dynSQL
    You can also use "Or" instead of "and" depending on what you want returned.
    Another method is to dynamically build a filter in the same way.

  3. #18
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I just noticed you're using the field name in the second part, should be the form control:

    ([StLars].År Like "*" & Forms!Sok!inskrAr & "*" Or Forms!Sok!inskrAr Is Null) And
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #19
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    And for the record, I agree with moke. That's the dynamic SQL I mentioned before.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #20
    buenaventura is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    10
    Quote Originally Posted by pbaldy View Post
    I just noticed you're using the field name in the second part, should be the form control:

    ([StLars].År Like "*" & Forms!Sok!inskrAr & "*" Or Forms!Sok!inskrAr Is Null) And
    Hmm, why is that? The reason I phrase it like I do is, that I want it to find rows in the database that have certain fields empty/NULL, but when I phrase it like you say, it all works out!! :O i dont really understand this But it works, thanks!

    I would like to have a button that clears all search fields also, how do I make that? EDIT: never mind I managed it with a set value to nothing makro on a button

  6. #21
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it working!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 2 of 2 FirstFirst 12
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
  •  
Other Forums: Microsoft Office Forums