Partial Matches in a Parameter Value

[Darkladymelz]

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:]));

[Robeen]

I rustled up this quick SQL from a Table I use for testing stuff:

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.

[Darkladymelz]

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?

[Robeen]

Can you post the SQL of your query?

[Darkladymelz]

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:]));

[orange]

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?

[Darkladymelz]

Just Address and Post Code to be partial matches.

Property Ref and Number of Bedrooms should be exact matches

[orange]

....
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:]));

[Darkladymelz]

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:]));

[Darkladymelz]

sorry here is the Error Message

[orange]

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:];

[Darkladymelz]

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.

[orange]

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.

[Darkladymelz]

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.

[orange]

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.