return results when there is a no data in field

This is what you asked for, why do I think I'm not getting the whole picture?


SELECT prop.*
FROM prop INNER JOIN cust ON prop.id2 = cust.ID
WHERE
(
         prop.town = 'splott'  AND prop.beds =' 3'
);

What you have done above is fine, but I have 6 drop down's with their values which are used to query the database.
The first option on all the drop down is valued as 'All', so if I selected one of the options from the first four drop down's and then selected 'All' on the remaining two.  I should receive all results based on the four values passed, as for the last two drop down's, the values could be any data.
i.e.

town      prop_type   beds     min  max
splott     detached    3            88   99
splott     semi           3            99   290
canton   detached   3            88   342

Criteria
town= splott
beds = 3
beds = all
min = all
max all

the returned results will be:
splott     detached    3            88   99
splott     semi           3            99   290

I thought I was missing something!

So, this is a parameter query, then.  You need to pass the search arguments to the query in parameters.  For sake of illustration, let's name those parameters [ptown], [pbeds], and so forth.  Let's also assume that Null is passed when "All" is selected in the dropdown. (Please advise if you don't know how to setup your combobox to achieve that).

SELECT prop.*
FROM prop INNER JOIN cust ON prop.id2 = cust.ID
WHERE (
       prop.town = nz([ptown], prop.town)
and prop.prop_type = nz([ptype],prop.prop_type)
and prop.beds = nz([pbeds], prop.beds)
and prop.min = nz([pmin], prop.min)
and prop.max = nz([pmax],prop.max);

i'm getting an error message:
syntax error in query expression

Remove the first paren immediately following the WHERE

Sorry, dont understand

I did the following and the this error message came up:
undefined function 'nz' in expression


SELECT prop.*
FROM prop INNER JOIN cust ON prop.id2 = cust.ID
WHERE
       prop.town = nz([ptown], prop.town)
and prop.prop_type = nz([ptype],prop.prop_type)
and prop.beds = nz([pbeds], prop.beds)
and prop.min = nz([pmin], prop.min)
and prop.max = nz([pmax],prop.max);

Hmmm...thought it was access.  Well, you can always do "the old fashioned way"

SELECT prop.*
FROM prop INNER JOIN cust ON prop.id2 = cust.ID
WHERE
       (prop.town = [ptown] or [ptown] is null)
and (prop.prop_type = [ptype] or [ptype] is null)
and (prop.beds = [pbeds] or [pbeds] is null)
and (prop.min = [pmin] or [pmin] is null)
and (prop.max = [pmax] or [pmax] is null);

I could'nt get the code above to work, but you gave me some ideas, so I managed to put the below code together, which works fine.
The returned results are based on the data selected and if the fields are null.

The problem i'm having:
I dont know how to make the returned results to appear even if the fields are null and if some of the other selected option are selected as 'All'.  


SELECT *
FROM prop2
WHERE
       (town like 'ptown' or town is null) and  (p_type like 'ptype' or p_type is null) and (beds like 'pbeds' or beds is null or beds ) and (min like 'pmin' or min is null) and (max like 'pmax' or max is null) and (p_built like 'pbuilt' or p_built is null);

thanks.

What database are you using?

Let's get one dropdown working first.  Then you can apply the same technique to the other dropdowns with a series of "and" conditions in the where clause.

Currently, you have:
SELECT *
FROM prop2
WHERE
       (town like 'ptown' or town is null)

That returns any row where town = 'ptown'  (probably none)  or any row where town is null.  You are not even referencing the combo box!!!

You need to figure out how to pass the value of the combobox into the query. Different databases do that different ways, so I need to know what database you are using and what language you are using to construct the SQL.

In the end, your sql needs to look like this:

SELECT *
FROM prop2
WHERE
       (prop2.town = "<combobox>" or <combobox> is null)







 

I'm using MS Access,

I'm using ASP to query the database using the code i've shown you above.
If we can use the same format as before:

SELECT *
FROM prop2
WHERE
       (town like 'ptown' or town is null)

I'm happy with this code, I just dont know how to code it to work for what I want it to do.

Imagin there are 5 drop down's, all drop down's have their own values plus there will be another option named 'All' (All - meaning any data in that column).
i.e.

data:
town      prop_type   beds     min  max
splott     detached    3            88   99
splott     semi           3            99   290
canton   detached   6            88   342
splott     semi           6           88    393
splott     detached   3           88    393
canton   semi           6           88    123
splott     semi           3           88    33

Criteria:
town= splott
prop_type= 3
beds = all
min = all
max= 88

the returned results will be:
splott     detached   3            88   99
splott     detached   3            88    393
splott     semi          3             88    33

>I'm using ASP to query the database using the code i've shown you above.

I haven't seen any code, just some SQL.  It helps to know this is an ASP page, but I still don't know what language you are using or how you are connecting to the database--both  those things make a big difference.  Constructing the SQL for what you want to do is fairly trivial, but I have to know the environment first.

Perhaps you could show the fragment of code where you are constructing and running the SQL.  


 

All I need to do is query MS Access database using the query i've shown.
There is no need to go through all the other code.
As long as the query works, the rest will work fine.

Let me rephrase my question:
What query would I use to query an MS access database to return the following results, based on the following data and criteria:

data:
town      prop_type   beds     min  max
splott     detached    3            88   99
splott     semi           3            99   290
canton   detached   6            88   342
splott     semi           6           88    393
splott     detached   3           88    393
canton   semi           6           88    123
splott     semi           3           88    33

Criteria:
town= splott
prop_type= 3
beds = all
min = all
max= 88

the returned results will be:
splott     detached   3            88   99
splott     detached   3            88    393
splott     semi          3             88    33

Hi,
I have worked more on this and used an IIf statement:
SELECT *
FROM prop2
WHERE (IIf(town like 'ptown', ptown or ptown like '0',town));

I have classed the 'All' option from the drop down menu list as being 0 value.
This code works fine, if the value passed is numeric, but when I use a text value the following error occurs 'too few parameters, expected 1'

i'm not sure if this is write, but i think this is a way to my solution.
Hope you can help.
thanks.

How can I get my point across? You either need parameterized SQL or dynamic SQL and the way to do those in every language and database is different.  With dynamic SQL, you construct the SQL in code, simply leaving out the conditions corresponding to the combo boxes that = "all".  For the criteria you mention, you would end up with this:

Select * from Prop2
where  town = "splott"
   and beds = 3
   and min = 88

But, for a different mix of criteria, the SQL would be different. OK, that's dynamic SQL.  And you can do that from an ASP page using DAO or ADO.

With parameterized SQL, the SQL is always the same.  But you pass parameters to the SQL to make it return the appropriate results.  Here is an Access parameter query that works as you describe.  It is essentially the same solution I presented earlier, except with zeros instead of nulls representing the "all" criteria.  I have tested this in Access and it works!  You can try for yourself by pasting it into an Access query, in which case you will get prompted for the parameters (remember to enter 0 instead of 'all').  Once you are satisfied that it works in Access, then I guess you can figure out how to run it from the ASP page.
 
Parameters [ptown] text(100), [ptype] text(100), [pbeds] long, [pmin] long, [pmax] long;
Select * from Prop2
where (town = [ptown] or [ptown] = "0")
and  (prop_type = [ptype] or [ptype] = "0")
and (beds = [pbeds] or [pbeds] = 0)
and (min = [pmin] or [pmin] = 0)
and (max =  [pmax] or [pmax] = 0)

Apparently not!  

Closed, 125 points refunded.
Vee_Mod
Community Support Moderator