Database search cont.....
Hi Guys
Managed to get search working for the amount of bedrooms, lounge, house type etc. Then I tried to add code for search in option buttons too and everything went to pots!!!! Now when I click the results command button I am getting this error message:
Run-time error '3075'
Sntax error (missing operator) in query expression '[Bedrm=AND [Reception]= AND [Garage = 'yes' AND [Gdns='yes'',
Below is my code. Can anyone please help!?
Private Sub cmdResults_Click()
Dim sql As String
Dim num_rooms As String
Dim num_reception As String
Dim gg_query As String
Dim type_query As String
Dim type_semi As String
Dim type_terr As String
Dim type_det As String
Dim type_flat As String
Dim curr_types As Byte
Dim num_types As Byte
Dim age_query As String
num_types = 0
num_rooms = ListRms
num_reception = ListRec
If chkGarage.Value Then
gg_query = "AND [Garage]='Yes' "
End If
If chkGarden.Value Then
gg_query = gg_query + " AND [Gdns]='Yes' "
End If
If chkSemiDet.Value Then
num_types = num_types + 1
type_semi = "[Type]='Semi'"
End If
If chkDetached.Value Then
num_types = num_types + 1
type_det = "[Type]='Det'"
End If
If chkFlat.Value Then
num_types = num_types + 1
type_flat = "[Type]='Flat'"
End If
If chkTerraced.Value Then
num_types = num_types + 1
type_terr = "[Type]='Terraced'"
End If
curr_types = num_types
If type_semi <> "" Then
type_query = type_semi
If curr_types > 1 Then
type_query = type_query + " OR "
curr_types = curr_types - 1
End If
End If
If type_det <> "" Then
type_query = type_query + type_det
If curr_types > 1 Then
type_query = type_query + " OR "
curr_types = curr_types - 1
End If
End If
If type_terr <> "" Then
type_query = type_query + type_terr
If curr_types > 1 Then
type_query = type_query + " OR "
curr_types = curr_types - 1
End If
End If
If type_flat <> "" Then
type_query = type_query + type_flat
If curr_types > 1 Then
type_query = type_query + " OR "
curr_types = curr_types - 1
End If
If type_query <> "" Then
type_query = " AND (" + type_query + ")"
If type_query <> "" Then
type_query = " AND (" + type_query + ")"
End If
If optNew.Value Then
age_query = "[Age]='New' "
End If
If optPostWar.Value Then
age_query = "[Age]='Post War' "
End If
If optPre1850.Value Then
age_query = "[Age]='Pre 1850' "
End If
If optPreWar.Value Then
age_query = "[Age]='Pre War' "
End If
End If
End If
' build query
sql = "SELECT * FROM [TblProperty] WHERE [Bedrm]=" + num_rooms + _
" AND [Reception]=" + num_reception + " " + gg_query + _
type_query '+ " AND " + age_query
Data1.RecordSource = sql
Data1.Refresh
End Sub
Re: Database search cont.....
Quote:
Originally posted by D Evans
Run-time error '3075'
Sntax error (missing operator) in query expression '[Bedrm=AND [Reception]= AND [Garage = 'yes' AND [Gdns='yes'',
Yes - cause you've missed the closing ] brackets. And possibly spaces.
See below - highlighted :
VB Code:
gg_query = " AND [Garage]='Yes' " [b]'<---Shouldn't this be True ? or is it a string? why use a string when you can use boolean ?[/b]
Hmmmmmmmm ok code looks ok.
Why are you doing it like this - and are you using VB because you are looking at the value of a chk yet that to me is a tickbox(checkbox) not and opt(ion). Anyway.
Not the best way of doing it though. Another way would be :
VB Code:
Dim strSql as string, strWhere as String, strTypes as String
On Error Resume Next
If chkGarage.Value Then strWhere = "[Garage]='Yes'"
If chkGarden.Value Then strWhere = strWhere & Iif(Len(strWhere)>0," AND ","") & "[Gdns]='Yes'"
'---- and so on
'---- for the several types :
If chkSemiDet.Value Then strTypes= "[Type]='Semi'"
If chkDetached.Value Then strTypes = strTypes & Iif(Len(strTypes)>0," OR ","") & "[Type]='Det'"
If chkFlat.Value Then strTypes = strTypes & Iif(Len(strTypes)>0," OR ","") & "[Type]='Flat'"
'---- and so on
strSql = "SELECT [tblProperty].* FROM [TblProperty]"
if len(strType)>0 then strWhere=strWhere & Iif(Len(strWhere)>0," ","") & "(" & strTypes & ")"
if len(strWhere)>0 then strSql=strSql & " WHERE " & strWhere
strSql=strSql & ";"
Anyways - its a suggestion.
Another would be to debug.print the sql and put a debugging stop there so you can see what its created.... before trying to use it.
Regards
Vince
Database search cont.....
You're right Vince
My coding is a bit long winded.....I knew there had to be a better way of doing it, but didn't know how! I've tried the suggestions you made and still I've had no joy................I''ll try again maybe jut needs a bit of twiddling
Thanks anyhow
DE