By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,244 Members | 1,972 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,244 IT Pros & Developers. It's quick & easy.

simple query

100+
P: 284
i have a simple query and does not getting desire results which i want
i am using vb6 and access
i had a table with with 8 fields but just to simplyfy by question i am just
supposing to four.

suppose i had a table with field


Name----------City----------Type----------Value

Property1-----Karachi-----1-------------- 100
Property2---- Lahore------ 3-------------- 250
Property3----- Karachi---- 1------------- 50


Now, i had made a form to search the desired criteria
and the field which we can choose searh are

City Karachi
Type (Blank)
Value <200

and i am using the following query

Expand|Select|Wrap|Line Numbers
  1.  
  2. "select * from property where city='" & citytextbox & "' and type ='" & typetextbox & "'    and value <='" & valuetextbox & "'
  3.  
i had also tried the querey replacing and with or but does not work

The problem is when we select all the criteria like city,type and value
the query work fine but if i left any one field blank to search it does not
show me any record or wrong recrod.

i want the query just like that if i select city (karachi) remaing all other criteria balnk then it show me only all the names haveing the city karachi.
and if i select select city (karachi) and type (1) then i had to show
me all the properties where city is karachi and type =1

hope you understant my question

Thanks in advance
Mar 18 '08 #1
Share this Question
Share on Google+
9 Replies


P: 4
I hope its because of AND use OR instead of AND. You should get your desired answer.
Mar 18 '08 #2

mafaisal
100+
P: 142
Hello,

Try This

Expand|Select|Wrap|Line Numbers
  1. Dim Condition As String
  2. Condition = IIf(typetextbox.Text = "", "city='" & citytextbox.Text & "'", "city='" & citytextbox.Text & "' and type ='" & typetextbox.Text & "'")
  3. "select * from property where  " & Condition & " and value <='" & valuetextbox & "'
  4.  
  5.  
Faisal
Mar 18 '08 #3

Expert 5K+
P: 8,434
I recommend writing out the code in a more readable form, rather than using the IIF() function.

I mean, something along the lines of...
Expand|Select|Wrap|Line Numbers
  1. If typetextbox.Text = "" Then
  2.   ' Use one query format
  3. Else
  4.   ' Use different format
  5. End If
The end result may be the same, but you'll find the code much easier to maintain in future.
Mar 18 '08 #4

QVeen72
Expert 100+
P: 1,445
Hi,

Build an SQL String according to user Selection :

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim sSQL As String
  3. sSQL = "select * from property where city='" & citytextbox & "' "
  4. If Trim(typetextbox.Text) <> "" Then
  5.     sSQL = sSQL & " And type ='" & typetextbox & "'"
  6. End If
  7. If Val(valuetextbox.Text) > 0 Then
  8.     sSQL = sSQL & " And Value <='" & valuetextbox & "'"
  9. End If
  10. ' Now your SQL Is Built Here,, You can include as many If conditions you want
  11.  
Regards
Veena
Mar 19 '08 #5

100+
P: 284
i think the problem is just due to the reasen that the query is treating blank as a name and serching blank field as a filter
and it is not the problem of and ,or

like if i made all field blank and search the record then the query is showing me no record becasue there is no field in database which is
blank

but i want that if i make all fileld blanks then it has to show me all the data
becuase i want the blank fileld to be treated as means "any"
any city,type
if i did make city field blank it means i want the city can be any
while the query is searching in database for the city which is blank.


i think QVeena suggestioin is good , only one problem
i did not know
how to merger this string with recordset
i always use
Expand|Select|Wrap|Line Numbers
  1. rs.open "query",connection
so how can i use
str="query "
with recordset, acutally


Expand|Select|Wrap|Line Numbers
  1.  
  2. sSQL = "select * from property where city='" & citytextbox.text & "'"
  3. If Trim(typetextbox.Text) <> "" Then
  4.     sSQL = sSQL & " And type ='" & typetextbox & "'"
  5. End If
  6. If Val(valuetextbox.Text) > 0 Then
  7.     sSQL = sSQL & " And Value <='" & valuetextbox & "'"
  8. End If
  9. rs.open" '" & sSQL & "'",con,rsadopenstatic,adlocoptimestic
  10.  

i had use this but geeting an error
"Invalid SQL statement Expected,"Delete","Inser","Procedure",
"Select","Update".
Mar 19 '08 #6

QVeen72
Expert 100+
P: 1,445
Hi,

Change it to:

rs.open sSQL,con

Regards
Veena
Mar 19 '08 #7

100+
P: 284
thanks QVeena
all other things has working fine now, but only one problem if i filter any
field except value its work fine but if i give any value to Vlauetextbox
then it give me the error
"Data type mismatch in criteria experssion'

i think some thing is wrong in this code

Expand|Select|Wrap|Line Numbers
  1. If Val(valuetxt.Text) > 0 Then
  2.     sSQL = sSQL & " And Value <='" & valuetxt.Text & "'"
  3. End If
  4.  
well this is numeric field
it seem that there is no error in the code so whats wrong
Mar 19 '08 #8

QVeen72
Expert 100+
P: 1,445
Hi,

I wanted to warn you in my prev post only..
Remove Single quote for Numeric fields:

Expand|Select|Wrap|Line Numbers
  1. If Val(valuetxt.Text) > 0 Then
  2.     sSQL = sSQL & " And Value <= " & Val(valuetxt.Text )
  3. End If
  4.  
Regards
Veena
Mar 19 '08 #9

100+
P: 284
Thanks a Lot

it is now all working fine with your help.
Mar 19 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.