simple query

[muddasirmunir]

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

 
"select * from property where city='" & citytextbox & "' and type ='" & typetextbox & "'    and value <='" & valuetextbox & "'
 

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

[kvishnubhat]

I hope its because of AND use OR instead of AND. You should get your desired answer.

[mafaisal]

Hello,

Try This

Dim Condition As String
Condition = IIf(typetextbox.Text = "", "city='" & citytextbox.Text & "'", "city='" & citytextbox.Text & "' and type ='" & typetextbox.Text & "'")
"select * from property where  " & Condition & " and value <='" & valuetextbox & "'
 
 

Faisal

[Killer42]

I recommend writing out the code in a more readable form, rather than using the IIF() function.

I mean, something along the lines of...

If typetextbox.Text = "" Then
  ' Use one query format
Else
  ' Use different format
End If

The end result may be the same, but you'll find the code much easier to maintain in future.

[QVeen72]

Hi,

Build an SQL String according to user Selection :

 
Dim sSQL As String
sSQL = "select * from property where city='" & citytextbox & "' "
If Trim(typetextbox.Text) <> "" Then
    sSQL = sSQL & " And type ='" & typetextbox & "'"
End If
If Val(valuetextbox.Text) > 0 Then
    sSQL = sSQL & " And Value <='" & valuetextbox & "'"
End If
' Now your SQL Is Built Here,, You can include as many If conditions you want
 

Regards
Veena

[muddasirmunir]

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

rs.open "query",connection

so how can i use
str="query "
with recordset, acutally

 
sSQL = "select * from property where city='" & citytextbox.text & "'"
If Trim(typetextbox.Text) <> "" Then
    sSQL = sSQL & " And type ='" & typetextbox & "'"
End If
If Val(valuetextbox.Text) > 0 Then
    sSQL = sSQL & " And Value <='" & valuetextbox & "'"
End If
rs.open" '" & sSQL & "'",con,rsadopenstatic,adlocoptimestic
 

i had use this but geeting an error
"Invalid SQL statement Expected,"Delete","Inser","Procedure",
"Select","Update".

[QVeen72]

Hi,

Change it to:

rs.open sSQL,con

Regards
Veena

[muddasirmunir]

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

If Val(valuetxt.Text) > 0 Then
    sSQL = sSQL & " And Value <='" & valuetxt.Text & "'"
End If
 

well this is numeric field
it seem that there is no error in the code so whats wrong

[QVeen72]

Hi,

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

If Val(valuetxt.Text) > 0 Then
    sSQL = sSQL & " And Value <= " & Val(valuetxt.Text )
End If
 

Regards
Veena

[muddasirmunir]

Thanks a Lot

it is now all working fine with your help.