[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

return results when there is a no data in field

Posted on 2007-10-04
19
Medium Priority
?
210 Views
Last Modified: 2010-03-20
Hi,

I'm trying to query a MS access database to return results of the follwing:
town      prop_type   beds     min
splott     detached    3            88
splott     semi           3            99
canton   detached   3            88

My query is to return all towns with name of splott and  beds with 3, prop_type being anything (*), and min being anything (*)
the query i'm using:

SELECT prop.*
FROM prop INNER JOIN cust ON prop.id2 = cust.ID
WHERE (((prop.town) Like 'splott' Or (prop.town) Is Null) AND ((prop.prop_type) Like ' ' Or (prop.prop_type) Is Null) AND ((prop.beds) Like '3' Or (prop.beds) Is Null) AND ((prop.min) Like '3' Or (prop.min) Is Null));

but I cant seem to get the results to retun.

Hope someone can help.

thank you.


SELECT prop.*
FROM prop INNER JOIN cust ON prop.id2 = cust.ID
WHERE (((prop.town) like 'tn' Or (prop.town) Is Null) AND ((prop.prop_type) = 'pt'));





0
Comment
Question by:heyme
  • 9
  • 8
18 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 20015422
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'
);

0
 

Author Comment

by:heyme
ID: 20016014
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
0
 
LVL 42

Expert Comment

by:dqmq
ID: 20016732
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);
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:heyme
ID: 20018136
i'm getting an error message:
syntax error in query expression
0
 
LVL 42

Expert Comment

by:dqmq
ID: 20018539
Remove the first paren immediately following the WHERE
0
 

Author Comment

by:heyme
ID: 20018561
Sorry, dont understand
0
 

Author Comment

by:heyme
ID: 20018624
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);
0
 
LVL 42

Expert Comment

by:dqmq
ID: 20019217
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);
0
 

Author Comment

by:heyme
ID: 20021149
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.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 20024167
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)







 

0
 

Author Comment

by:heyme
ID: 20024574
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
0
 
LVL 42

Expert Comment

by:dqmq
ID: 20025387
>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.  


 
0
 

Author Comment

by:heyme
ID: 20025609
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
0
 

Author Comment

by:heyme
ID: 20026441
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.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 20026842
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)
0
 

Accepted Solution

by:
heyme earned 0 total points
ID: 20027497
Hi,

I dont think you understood me, I managed to get it to work using a different way.  Here what I did:

SELECT *
FROM prop2
WHERE (IIf(ptype = prop_type.id, p_type, IIf(ptype = 0, p_type)))  and (IIf(ptown = prop_town.id, town, IIf(ptown = 0, town)))  and (IIf(pbeds = beds.id, beds, IIf(pbeds = 0, beds))) and (IIf(pbuilt = built.id, p_built, IIf(pbuilt = 0, p_built))) and (IIf(mval = 0 or xval = 0, price, price between mval and xval));

At last this is working.

thanks for your help.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 20028046
Apparently not!  
0
 
LVL 1

Expert Comment

by:Vee_Mod
ID: 20049113
Closed, 125 points refunded.
Vee_Mod
Community Support Moderator
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question