Parameter Values

[Darkladymelz]

I have created 3 parameter values within one query but I only require one out of the 3 to be answered in order to achieve my results. The other two could be filled out or left blank.

I keep finding that this is pulling up blank results although I know I am putting in correct information. (answering 1 but leaving other 2 blank). Is there another way I should be trying or an expression I should be using?



Thank you for your help in advance.

[orange]

Post the SQL for your query.

[Darkladymelz]

SELECT [Landlord Details].[Landlord Ref], [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, [Landlord Details].Firstname, [Landlord Details].Surname, [Landlord Details].[Contact Address], [Landlord Details].[Home No], [Landlord Details].[Work No], [Landlord Details].[Mobile No], [Landlord Details].[Email Address]
FROM [Landlord Details] INNER JOIN [Property Details] ON [Landlord Details].[Landlord Ref] = [Property Details].[Landlord Ref]
WHERE ((([Landlord Details].[Landlord Ref])=[Enter Landlord Ref]) AND (([Landlord Details].Firstname)=[Enter Landlord Name]) AND (([Landlord Details].Surname)=[Enter Landlord Surname]));

[orange]

Your parameters are joined by AND which means
((([Landlord Details].[Landlord Ref])=[Enter Landlord Ref]) AND (([Landlord Details].Firstname)=[Enter Landlord Name]) AND (([Landlord Details].Surname)=[Enter Landlord Surname]));

If you leave 1 or 2 empty, you are asking for empty (either NULL or zero length string values in your data).

What exactly do you want to search for -exact matches, first few characters????

You may want to review the LIKE operator
http://office.microsoft.com/en-us/ac...001231490.aspx

[Darkladymelz]

I want to search for exact matches only.

What would I replace the AND with?

[orange]

Try this:

([Landlord Details].[Landlord Ref]=[Enter Landlord Ref]) OR
ISNull([Landlord Details].[Landlord Ref]) OR [Landlord Details].[Landlord Ref]='')
AND
([Landlord Details].Firstname=[Enter Landlord Name] OR
IsNull([Landlord Details].Firstname) OR [Landlord Details].Firstname ='')
AND
([Landlord Details].Surname=[Enter Landlord Surname] OR
IsNULL([Landlord Details].Surname) OR [Landlord Details].Surname='' )

[Darkladymelz]

Thank you.

I have tried this. Parameter Value boxes are appearing but results are not showing for "Landlord Ref" or "Firstname". The only results that show are for Surname.

Please let me know if I have gone wrong somewhere. Here is the SQL:

SELECT [Landlord Details].[Landlord Ref], [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, [Landlord Details].Firstname, [Landlord Details].Surname, [Landlord Details].[Contact Address], [Landlord Details].[Home No], [Landlord Details].[Work No], [Landlord Details].[Mobile No], [Landlord Details].[Email Address]
FROM [Landlord Details] INNER JOIN [Property Details] ON [Landlord Details].[Landlord Ref] = [Property Details].[Landlord Ref]
WHERE ((([Landlord Details].[Landlord Ref])=[Enter Landlord Ref:]) OR IsNull ([Landlord Details].[Landlord Ref]) OR [Landlord Details].[Landlord Ref]=[Enter Landlord Ref:]) AND (([Landlord Details].Firstname)=[Enter Firstname:]) OR IsNull ([Landlord Details].Firstname) OR [Landlord Details].Firstname =[Enter Firstname:] AND (([Landlord Details].Surname)=[Enter Surname:]) OR IsNull ([Landlord Details].Surname) OR [Landlord Details].Surname=[Enter Surname:];

Thank you

[orange]

I played with the brackets and removed the second request for the parameter

SELECT [Landlord Details].[Landlord Ref], [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, [Landlord Details].Firstname, [Landlord Details].Surname, [Landlord Details].[Contact Address], [Landlord Details].[Home No], [Landlord Details].[Work No], [Landlord Details].[Mobile No], [Landlord Details].[Email Address]
FROM [Landlord Details] INNER JOIN [Property Details] ON [Landlord Details].[Landlord Ref] = [Property Details].[Landlord Ref]
WHERE
(
(([Landlord Details].[Landlord Ref])=[Enter Landlord Ref:]) OR
IsNull ([Landlord Details].[Landlord Ref]) OR
[Landlord Details].[Landlord Ref]=''
)
AND
(
(([Landlord Details].Firstname)=[Enter Firstname:]) OR
IsNull ([Landlord Details].Firstname) OR
[Landlord Details].Firstname =''
)
AND
(
(([Landlord Details].Surname)=[Enter Surname:]) OR
IsNull ([Landlord Details].Surname) OR
[Landlord Details].Surname= ''
);

[orange]

Here's another version. It is a parameters query. The parameters are slightly different(spelling) but should work.

Create a new query, go to sql view and paste this into the window.

PARAMETERS
EnterLandlordRef string,
EnterFirstName string,
EnterSurname string;
SELECT [Landlord Details].[Landlord Ref], [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, [Landlord Details].Firstname, [Landlord Details].Surname, [Landlord Details].[Contact Address], [Landlord Details].[Home No], [Landlord Details].[Work No], [Landlord Details].[Mobile No], [Landlord Details].[Email Address]
FROM [Landlord Details] INNER JOIN [Property Details] ON [Landlord Details].[Landlord Ref] = [Property Details].[Landlord Ref]
WHERE
(
(([Landlord Details].[Landlord Ref])=[EnterLandlordRef]) OR
IsNull ([Landlord Details].[Landlord Ref]) OR
[Landlord Details].[Landlord Ref]=''
)
AND
(
(([Landlord Details].Firstname)=[EnterFirstname]) OR
IsNull ([Landlord Details].Firstname) OR
[Landlord Details].Firstname =''
)
AND
(
(([Landlord Details].Surname)=[Enter Surname) OR
IsNull ([Landlord Details].Surname) OR
[Landlord Details].Surname= ''
);

[Darkladymelz]

I am still having the same problem with no results showing for Landlord Ref or Firstname. Only results for Surname. This is the same when I copy and paste the SQL into a new query.

[orange]

Attached is small mdb in Acc2003 format. I have 2 tables and 1 query. The query accepts 3 parms. At least one parm must be filled. It does exact match. I have tested with FirstName, Surname, or Ref and seems to work. But I don't know exactly what you need.
Hope it helps.

Good luck.

[Darkladymelz]

Thank you! That is exactly what I wanted.

I noticed that in your SQL you did not include the fuction IsNull as you included in the previous SQL above.I believe that may have been where the query was getting confused. You had replaced the AND with OR and it worked perfectly.

WHERE ((([Landlord Details].[Landlord Ref])=[Enter Landlord Ref:])) OR ((([Landlord Details].Firstname)=[Enter Firstname:])) OR ((([Landlord Details].Surname)=[Enter Surname:]));

Thank you for all your help.

[orange]

You are welcome.