Results 1 to 4 of 4

Thread: Search Engine

  1. #1
    Join Date
    Aug 2002
    Northampton, England

    Unanswered: Search Engine

    I have created a form to search my DB on certain fields. One ComboBox, DropDown.One textbox, txtInput and one command button. It works well if there is no space in the field name. However if there is a space in the field name I get the error message 'Runtime error 3075 Syntax error(missing operator) in query expression etc. etc. The code I am using is this. How do I overcome this problem? any guidance appreciated

    Dim db As Database
    Dim QD As QueryDef
    Dim WHERE As Variant

    Set db = CurrentDb()

    ' Delete the existing dynamic query; trap the error if the query does
    ' not exist.

    On Error Resume Next
    db.QueryDefs.Delete ("Dynamic_Query")
    On Error GoTo 0

    WHERE = Null

    Set QD = db.CreateQueryDef("DynamicQuery", "SELECT * FROM Nonconformances WHERE " & DropDown.Value & " LIKE '*" & txtInput & "*'")
    DoCmd.OpenQuery "DynamicQuery"

  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland

    well - first thing is not to use spaces in object names - they always bite you in the bum sooner or later.

    possible fix: have a go with
    FROM Nonconformances WHERE [" & DropDown.Value & "] LIKE '*" & txtInput & "*'")

    currently using SS 2008R2

  3. #3
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    Check to make sure you're dealing with a string field too. I'm not sure what error you get when you try to use LIKE on a numeric field, but it's something to check...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Aug 2002
    Northampton, England
    Thanks Izzy. Worked like a charm. The square brackets solved the problem. Incidentally, this is not my DB. I never have spaces in my field names. I was asked to look at the search routine. The table to search on has some 30 fields and the search form had 30 text boxes. A nightmare. Teddy, hadn't thought about numeric, as there are no numeric fields to search on. I will try it out on a numeric field. Many thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts