Two word searches?

M

Minnesota

Registered User.
Local time
Today, 04:15
Joined
Jun 23, 2009
Messages
22
I have a list of cities here in MN. Obviously some are two words. When I input any two word city into my search (select from a drop down list) I get an error. However, the one word cities work fine. Is there a way I can fix this?

The error I receive is:
Run-Time error '3075':

Synax error (missing operator) in query expression '[city] = Saint Paul'.
 
Would you please post the entire SQL.

My guess is that you are not wrapping the text with quotes.
 
It needs to be

"[City]= '" & strvarible & "'"

David
 
Ok, here is what I have. I'm new to this, but it looks like my quotes follow what you've said. Correct me if I'm wrong.

Code: 
Option Compare Database
Option Explicit
Private Sub btnSearch_Click()
 
    'Updates the record source
    Me.[QryMasterDataList subform].Form.RecordSource = "SELECT * FROM tblMasterDatalist " & Buildfilter
 
End Sub
 
Private Sub btnClear_Click()
 
    Dim intIndex As Integer
 
    'Clears all entries from search
    Me.cmbCity = 0
    Me.CmbCounty = 0
    Me.CmbOffice = 0
    Me.CmbRegion = 0
 
End Sub
 
Private Function Buildfilter() As Variant
    Dim varWhere As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
 
    varWhere = Null
 
    'Check for City
    If Me.cmbCity > 0 Then
        varWhere = varWhere & "[City] = " & Me.cmbCity & " AND "
    End If
 
    'Check for County
    If Me.CmbCounty > 0 Then
        varWhere = varWhere & "[County] = " & Me.CmbCounty & " AND "
    End If
 
    'Check for Office
    If Me.CmbOffice > 0 Then
        varWhere = varWhere & "[Office] = " & Me.CmbOffice & " AND "
    End If
 
    'Check for Region
    If Me.CmbRegion > 0 Then
        varWhere = varWhere & "[officeregion] = " & Me.CmbRegion & " AND "
    End If
 
 
 
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
 
 
    If Right(varWhere, 5) = " AND " Then
        varWhere = Left(varWhere, Len(varWhere) - 5)
    End If
 
    End If
 
    Buildfilter = varWhere
 
End Function

Since I'm posting this anyway, When I select a city or other search criteria then click search it pops up a box for me to re-enter the same criteria. I assume I have a loop in there somewhere that isn't needed. If you notice this could you point it out?

Your help is greatly appreciated.
 
Code: 
Option Compare Database
Option Explicit
Private Sub btnSearch_Click()
[B] Call BuildFilter[/B]

    'Updates the record source
    Me.[QryMasterDataList subform].Form.RecordSource = "SELECT * FROM tblMasterDatalist " & Buildfilter
 
End Sub
 
Private Sub btnClear_Click()
 
    Dim intIndex As Integer
 
    'Clears all entries from search
    Me.cmbCity = 0
    Me.CmbCounty = 0
    Me.CmbOffice = 0
    Me.CmbRegion = 0
 
End Sub
 
Private Function Buildfilter() As Variant
    Dim varWhere As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
 
    varWhere = Null
 
    'Check for City
    If Me.cmbCity > 0 Then
        varWhere = varWhere & "[City] = '" & Me.cmbCity & "' AND "
    End If
 
    'Check for County
    If Me.CmbCounty > 0 Then
        varWhere = varWhere & "[County] = '" & Me.CmbCounty & "' AND "
    End If
 
    'Check for Office
    If Me.CmbOffice > 0 Then
        varWhere = varWhere & "[Office] = '" & Me.CmbOffice & "' AND "
    End If
 
    'Check for Region
    If Me.CmbRegion > 0 Then
        varWhere = varWhere & "[officeregion] = '" & Me.CmbRegion & "' AND "
    End If
 
 
 
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
 
 
    If Right(varWhere, 5) = " AND " Then
        varWhere = Left(varWhere, Len(varWhere) - 5)
    End If
 
    End If
 
    Buildfilter = varWhere
 
End Function

There seems to some kind of contradiction here. You first said about using more than one word. Then I see that your Combos are using numbers as bound fields. Which is correct? if you are using string then leave in the single quotes I have added otherwise remove them.

David
 
I guess I'm not understanding.


Like I said, I'm new and this. Sorry for the dumb questions.
 
Can you post a sample of your db to look at. This may be the quickest way of resolving your issue.

David
 
You have lookups at table level...I suspect that is your problem. Do a search here on the forums for the evils of Table Level Lookups.
 
As I read through these 'evils of table-level lookups' I am getting a little more confused. Table-level lookup would be where I have my city, county, and other drop downs in the table?
 
Yes, that would be correct. A better way to store the data is to have a separate table for each of the fields where you have a table level lookup. You would then store the ID number of the entry. For example, the city. What you should have is a separate table for the city, with an ID number (which should be unique), City Name, and City code. You would then store the cityID in your MasterTable. Now, when searching, you can simply pull records where the CityID from the combo box is equal to the cityID from the master table.
 
If you wish to try a different approach, try this:

http://www.access-programmers.co.uk/...d.php?t=176023
Click to expand...

I get File Now Found from your link.

A better way to store the data is to have a separate table for each of the fields where you have a table level lookup. You would then store the ID number of the entry. For example, the city. What you should have is a separate table for the city, with an ID number (which should be unique), City Name, and City code. You would then store the cityID in your MasterTable.
Click to expand...

Is this more how I should have it set up? Now I'm having a hard time understanding the search feature and also limiting data input to specific options (i.e. city name).

You people have been a great help. I really appreciate this.
 

Attachments

Last edited:
With the above posted database I'm still not able to search for the two word towns (i.e. Saint Paul) without getting an error. The one word town work and I don't think I have any table-level lookups.

As I've read other posts I've noticed that everybody says the table needs to show the number (ID/Key?) not the text (like you said, sorry). So now I'm confused how to make it do that without having the lookup in there.
 
Last edited:
Minnesota,
Had some free time at work here...so I played around with your database. I redid your master table with what I was referring to pertaining to lookups. I saved the old table as well and put comments in the description of the fields that were removed or changed.

One important thing to remember as well. Every table should have a primary key. That is what makes each record unique. None of your tables had primary keys.


On your search form, I fixed it up so that by searching by city, etc a filter is applied and will return the records based on the combo box selection. Any questions, let me know
 

Attachments

You must log in or register to reply here.

Similar threads

C
Solved Searching two words in one paragraph
Replies
4
Views
388
Capitala
C
C
Format text between two words
Replies
8
Views
587
CJ_London
C
E
Solved correct query syntax - (re making the found word appear red colored in report)
Replies
9
Views
283
EdK
E
A
Question Re: Modal Dialog form
Replies
7
Views
413
theDBguy
theDBguy
E
Search Button Not Finding Record and populating the text boxes on form.
Replies
13
Views
1,348
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom