Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    dcwang3 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2009
    Posts
    12

    Beginner to Access: Search application

    Hey guys, I'm new to these forums and to access 2007. I am working on a database that has information about different parts, essentially a parts list.

    I have like 21 different tables (referrenced towards different application that the parts are used towards). I am making a form so I can do a parts list search. I actually have to things, i would like to search an "internal p/n" and a "vendors p/n".
    Each of the tables contain an internal p/n and a vendors p/n and I would like to have one search box that will look through all the tables in those two fields and display them in like a pop up spreadsheet or something.


    I was looking into creating relationships, but I did not understand how to do that really.



    Could someone help me step by step on creating this?

    Thanks

    -David

  2. #2
    dcwang3 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2009
    Posts
    12
    can anyone help?

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    21 different tables sounds like you may have an issue with the design.

  4. #4
    dcwang3 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2009
    Posts
    12

    Search Criteria Problem

    Hey guys:

    Ok so I have a database that contains a list of parts on different assemblies called "part number". Two fields that I am trying to search is Internal P/N and Vendor P/N. Every single entry is not field with a internal nor vendor pn. Some of the entries may have one or the other, most of the entries have both though.

    I am using a form called "searchpn" that will search the Internal P/N and Vendor P/N fields. I am using custom textbox with custom search command button. Here is the code below for the search command button:

    Code:
    Private Sub SEARCH_FIELDS_Click()
    DoCmd.OpenQuery "searchquery", acViewNormal
    End Sub
    Here is the code for the query criteria (QINTNERAL and QVENDOR are the names of the textbox fields):

    Code:
    Like [Forms]![searchpn].[QINTERNAL] & "*"
    Like [Forms]![searchpn].[QVENDOR] & "*"
    My problem that I am having is that with the criteria as that, it is suppose to show all entries regardless if they don't have a value in it or not...When I search with blank textboxes, only entries that HAVE BOTH internal and vendor pn will show up. For those that only has one or the other will NOT SHOW UP.

    If I search either one or the other (that has both pn filled in), then I do not have a problem finding the entry. It seems like with the criteria that I have, it will only grab entries that only HAVE BOTH fields filled with values...

    Can someone help me out?!?! Thanks


    PS: I did exactly this ==> Macromedia Flash (SWF) Movie Created by Camtasia Studio 2

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Have you tried ORing the two expresions together?
    Expression1 OR Expression2

  6. #6
    dcwang3 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2009
    Posts
    12
    if I do that with in both criteria's and save it, it will pretty much do the same thing except that if I search for a specific part that I know is an entry (and has both internal and vendor pn), it will not show it at the beginning, it basically just shows all the entries that has BOTH internal and vendor pn...

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Are you using the query builder? If so then put the criteria on different lines. Putting them on the same criteria line makes an AND and on different lines makes an OR. Switch to SQL view to see the results.

  8. #8
    dcwang3 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2009
    Posts
    12
    I am not using the query builder, I'm doing it custom...When you mean on different lines, you mean have one "Like" in the criteria, then the other "Like" in the OR section or on on the lines below the OR section?

  9. #9
    dcwang3 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2009
    Posts
    12
    Here is the SQL code when I do it like you suggested...Not too sure how to read it

    Code:
    SELECT [Part Number].[Internal P/N], [Part Number].[Vendor P/N], [Part Number].ASSEMBLY, [Part Number].DESCRIPTION, [Part Number].MFG, [Part Number].[LM CODE], [Part Number].[LM RATIONALE], [Part Number].[REF  DES], [Part Number].COMMENTS, [Part Number].DATASHEETS
    FROM [Part Number]
    WHERE ((([Part Number].[Internal P/N]) Like Forms!searchpn.QINTERNAL & "*") And (([Part Number].[Vendor P/N]) Like Forms!searchpn.QVENDOR & "*")) Or ((([Part Number].[Internal P/N]) Like Forms!searchpn.QVENDOR & "*") And (([Part Number].[Vendor P/N]) Like Forms!searchpn.QINTERNAL & "*") And ((Forms!searchpn.QINTERNAL) Is Null)) Or (((Forms!searchpn.QVENDOR) Is Null)) Or (((Forms!searchpn.QINTERNAL) Is Null) And ((Forms!searchpn.QVENDOR) Is Null))
    ORDER BY [Part Number].ASSEMBLY;

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I think your problem is with your nested parens. You need to double check them.

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You can use the Query Builder to check your work. Copy the SQL and paste it into the SQL view of a new query and then switch to design view. You will see what Access sees.

  12. #12
    dcwang3 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2009
    Posts
    12
    so I did that and it's the same thing...

    Got any other suggestions?

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Cut your Where clause down to just one test and then start adding them back to see how the returned recoredset changes.

  14. #14
    dcwang3 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2009
    Posts
    12
    so this is what I came up with...The first set of large brackets is suppose to show all the entries (which is does, but when I search for a specific entry, it doesn't, just gives all entries).

    The second set of large brackets after the "AND" is suppose to show all the entries for whatever is put into the textfields...

    This combination basically gives in exactly the same results as from what was happening before.

    Any suggestions, maybe I am doing something wrong or thinking the logic wrong?

    Thanks

    Code:
    WHERE
    (
    (([Part Number].[Internal P/N]) Like [Forms]![searchpn].[QINTERNAL] & "*")  
    OR
    (([Forms]![searchpn].[QINTERNAL]) Is Null)
    OR
    (([Part Number].[Vendor P/N]) Like [Forms]![searchpn].[QVENDOR] & "*")
    OR
    (([Forms]![searchpn].[QVENDOR]) Is Null)
    )
    AND
    (
    (
    (
    (([Part Number].[Internal P/N]) Like [Forms]![searchpn].[QINTERNAL] & "*")  
    OR
    (([Forms]![searchpn].[QINTERNAL]) Is Null)
    )
    AND
    (
    (([Part Number].[Vendor P/N]) Like [Forms]![searchpn].[QVENDOR] & "*")
    OR
    (([Forms]![searchpn].[QVENDOR]) Is Null)
    )
    )
    )

  15. #15
    dcwang3 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2009
    Posts
    12
    ok I think I solved my problem...the code is correct, they my definitions of "" and Null where off, they are two completely different things and when I was searching, I had my default value as "", but I was searching for Null...
    When I initially just search with blank values, it was a "Null", but since my default value was "", it wasn't acting correct...so when I entered something in, ran it, then deleted it, it runs fine...
    I think I solved it, thanks for the help with everything else!

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

Similar Threads

  1. Beginner trying to send email with attachment
    By ahm in forum Programming
    Replies: 2
    Last Post: 03-24-2009, 08:51 PM
  2. 1st Access application. Needs a clue..
    By Icedog in forum Access
    Replies: 3
    Last Post: 09-24-2008, 09:38 AM
  3. Replies: 0
    Last Post: 08-26-2008, 09:22 AM
  4. Access application to Web based application
    By admaldo in forum Access
    Replies: 0
    Last Post: 06-12-2008, 06:22 AM
  5. Modifying and existing MS Access Application
    By bjohnson in forum Access
    Replies: 2
    Last Post: 03-08-2006, 07:45 AM

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