Partial Matches in a Parameter Value

[Darkladymelz]

This is the SQL:

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


When I enter N9 as a partial postcode (no other parameter values) it brings up a list of all postcodes with a N in it. If I want to enter a partial address (no other parameter value) I get no results at all. If I removed the code from Enter Postcode, the partial address works fine.

I don't want to use the AND operator as I want the user to only need to enter one of the parameter values.

[orange]

Send a few test records, or send me a sample of your database in mdb (2003) format. I do NOT HAVE 2007.

I went thru some old posts because this issue seemed familiar -seems this is a related post that was Solved
https://www.accessforums.net/showthr...9261#post99261

[Darkladymelz]

Please find attached dummy database.

It is the 'SearchProp' query.

As you will see that the query will not work a partial address OR partial postcode, but if you delete the parameter value code from Postcode field, the address parameter value works perfectly.

[orange]

The issue I believe is as follows:

If you have criteria with a Like "*" & [parm], and you just hit Enter, you will get the equivalent of "*" (select all) for that parameter.

In your test DB, SearchProp query
Property ref (enter) <-- no value
Address (n)<--------------------------Select June record
POstCode (e)<-----------------------Selects where Postcode has E
Bedrooms (enter) <--no value

But if you just hit enter on address or PostCode (where partial match is the criteria) you'll get all records.