Searching database

[D Evans]

Desperately in need of eargent help

Please can anyone help me, a novice whose coursework is creating a database for an estate agent.

I've tried varrious codes that will search the database for a property that meets the criteria of the customer and list all the appropriate properties in a DBGrid (e.g 3beds, 2 receptions, garden, garage etc.)

I have been trying to generate a code in vb, would I be better doing it in Sql?

Any help or feedback would be gratefully recieved.

Thanking you in advance
A very frustrated student!

[DKCK]

Yes, SQL might be better. Do you know ADO?

[honeybee]

If you know how to create and use recordsets, you can use an SQL statement to create a recordset, and the statement would look like : SELECT * FROM <YourTable> WHERE <CustAttribute> LIKE <YourValue>*.

.

[D Evans]

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

[Ecniv]

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 :

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 :

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

[D Evans]

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