Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Darkladymelz is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    43

    Partial Matches in a Parameter Value

    I need to be able to enter into a parameter value part of the value but i do not know the expression to use.

    For example when searching an address - 13 Dummy Road

    I want to be able to just search the number or just the road name and for my query to show all partial matches.

    Is this possible?

    The SQL for my current query I want to edit is:

    SELECT [Property Details].[Property Ref], [Property Details].[Property Address], [Property Details].County, [Property Details].[Post Code], [Property Details].[Property Type], [Property Details].Bedrooms, [Property Details].Floor, [Property Details].[Start Date], [Property Details].[End Date], [Property Details].Rent, [Property Details].[Guaranteed Rent], [Property Details].Supplier, [Property Details].[Landlord Ref], [Property Details].[Tenant Ref]
    FROM [Property Details]
    WHERE ((([Property Details].[Property Ref])=[Enter Property Ref:])) OR ((([Property Details].[Property Address])=[Enter Address:])) OR ((([Property Details].[Post Code])=[Enter Postcode:])) OR ((([Property Details].Bedrooms)=[Enter No of Bedrooms:]));

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I rustled up this quick SQL from a Table I use for testing stuff:

    Code:
    SELECT [00_AAA_Test].ID, [00_AAA_Test].Order, [00_AAA_Test].[Customer Name], [00_AAA_Test].[No of Boxes], [00_AAA_Test].[No of days on Hold], [00_AAA_Test].REASON
    FROM 00_AAA_Test
    WHERE ((([00_AAA_Test].[Customer Name]) Like ("*" & [Enter Partial Name:] & "*")));
    Let me know if it helps you.

  3. #3
    Darkladymelz is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    43
    Thank you!

    This worked for finding a partial match in an address, but not for Post Code.

    When I enter it for Post Code it stops the expression in address from working. Do i need a different expression to find partial Postcodes?

  4. #4
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Can you post the SQL of your query?

  5. #5
    Darkladymelz is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    43
    SELECT [Property Details].[Property Ref], [Property Details].[Property Address], [Property Details].County, [Property Details].[Post Code], [Property Details].[Property Type], [Property Details].Bedrooms, [Property Details].Floor, [Property Details].[Start Date], [Property Details].[End Date], [Property Details].Rent, [Property Details].[Guaranteed Rent], [Property Details].Supplier, [Property Details].[Landlord Ref], [Property Details].[Tenant Ref]
    FROM [Property Details]
    WHERE ((([Property Details].[Property Ref])=[Enter Property Ref:])) OR ((([Property Details].[Property Address]) Like ("*" & [Enter Address:] & "*"))) OR ((([Property Details].[Post Code])=[Enter Postcode:])) OR ((([Property Details].Bedrooms)=[Enter No of Bedrooms:]));

  6. #6
    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,847
    Are you looking for partial matches on

    -Property Ref or
    -Address or
    -Post Code or
    -Number of Bedrooms?

    Or are some of these to be exact matches?

  7. #7
    Darkladymelz is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    43
    Just Address and Post Code to be partial matches.

    Property Ref and Number of Bedrooms should be exact matches

  8. #8
    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,847
    ....
    WHERE ((([Property Details].[Property Ref])=[Enter Property Ref:])) OR
    ((([Property Details].[Property Address]) Like ("*" & [Enter Address:] & "*"))) OR
    ((([Property Details].[Post Code]) Like ("*" &[Enter Postcode:] & "*")) OR
    ((([Property Details].Bedrooms)=[Enter No of Bedrooms:]));

  9. #9
    Darkladymelz is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    43
    Thanks orange for your input. Please find attached .gif file of the Error message I got when using the above expression.

    This is the SQL i used:

    SELECT [Property Details].[Property Ref], [Property Details].[Property Address], [Property Details].County, [Property Details].[Post Code], [Property Details].[Property Type], [Property Details].Bedrooms, [Property Details].Floor, [Property Details].[Start Date], [Property Details].[End Date], [Property Details].Rent, [Property Details].[Guaranteed Rent], [Property Details].Supplier, [Property Details].[Landlord Ref], [Property Details].[Tenant Ref]
    FROM [Property Details]
    WHERE ((([Property Details].[Property Ref])=[Enter Property Ref:])) OR ((([Property Details].[Property Address]) Like ("*" & [Enter Address:] & "*"))) OR ((([Property Details].[Post Code]) Like ("*" &[Enter Postcode:] & "*")) OR ((([Property Details].Bedrooms)=[Enter No of Bedrooms:]));

  10. #10
    Darkladymelz is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    43
    Click image for larger version. 

Name:	screenshot.gif 
Views:	13 
Size:	8.5 KB 
ID:	6577

    sorry here is the Error Message

  11. #11
    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,847
    Another try

    SELECT [Property Ref]
    , [Property Address]
    , County, [Post Code]
    , [Property Type]
    , Bedrooms, Floor
    , [Start Date]
    , [End Date]
    , Rent
    , [Guaranteed Rent]
    , Supplier
    , [Landlord Ref]
    , [Tenant Ref]
    FROM [Property Details]
    WHERE [Property Ref]=[Enter Property Ref:] OR
    [Property Address] Like "*" & [Enter Address:] & "*" OR
    [Post Code]) Like "*" & [Enter Postcode:]& "*" OR
    Bedrooms=[Enter No of Bedrooms:];

  12. #12
    Darkladymelz is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    43
    The SQL works, but for instance if I search a partial Postcode of N9, it will bring up all Post Codes with N and 9 in the them. There are several partial postcodes I wish to search by: N9, N18, EN3 etc....

    Also this stops the partial search for address from working, regardless whether I enter a Postcode or not.

  13. #13
    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,847
    If you put N9 in the prompt [Enter Postcode:], it will search for N9* or *N9* or *N9, but it will NOT find postal codes that do NOT have an N and 9 side by side N9, It will not find "aNbb9x". The "N9" must be contiguous.

    If you can save a copy of your database as mdb (2003), I'll look at it.

  14. #14
    Darkladymelz is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    43
    I put N9 into the prompt box and it brought up all Postcodes with N in it, regardless if a 9 was next to it or not.

  15. #15
    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,847
    Tell us about all the parameter boxes and the values you used.
    You must have put other values in other boxes. The OR operator allows for any condition to be met.
    So if another condition was met, you could have a postcode that didn't match your *N9**.

    Please post a few of the records that were selected and did not have the N9 side by side.


    If you want all conditions to be met you must use the AND operator.

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

Similar Threads

  1. Replies: 5
    Last Post: 01-25-2012, 08:07 AM
  2. Replies: 2
    Last Post: 10-20-2011, 08:31 AM
  3. partial display
    By tawright in forum Access
    Replies: 2
    Last Post: 07-20-2011, 09:38 AM
  4. Help: Comparison query to find non-matches
    By 14erclimber in forum Queries
    Replies: 6
    Last Post: 06-09-2010, 09:29 AM
  5. Replies: 1
    Last Post: 05-18-2010, 12:05 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