Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    45

    Unanswered: using a query in vba

    Hallo, I need to use a query in the vba code, but i keep on gettin sintax error messages. I think is the WHERE part that creates problem because it refers to a form.
    The query in sql is something like this:


    WHERE ((ISNULL([tbl_Type].[typ_ID]) And [Forms]![Form1]![cbo_type]="*") Or [tbl_Type].[typ_ID] Like [Forms]![Form1]![cbo_type]) And ((ISNULL([tbl_Person].[per_name]) And [Forms]![Form1]![cbo_person]="*") Or [tbl_Person].[per_name] Like [Forms]![Form1]![cbo_person]);

    I tried to write this in vba code:

    strSql = "...blabla...WHERE ((ISNULL([tbl_Type].[typ_ID]) And " & [Forms]![Form1]![Cbo_Type] & " = '" & "*" & "') Or [tbl_Type].[typ_ID] Like " & [Forms]![Form1]![Cbo_Type] & ") And ((ISNULL([tbl_Person].[per_name]) And " & [Forms]![Form1]![cbo_Person] & " = '" & "*" & "') Or [tbl_Person].[per_name] Like " & [Forms]![Form1]![cbo_Person] & ");"

    But I get a "missing operator" error message. Can someone see the mistake?
    thanks

  2. #2
    Join Date
    Jul 2004
    Posts
    64
    Does the same run OK from within a query?

  3. #3
    Join Date
    Feb 2004
    Posts
    45
    the sql code yes it works if I use it in an external query, the second one (the one i use in vba) doesn't work if I use it in an external query.

  4. #4
    Join Date
    Jul 2004
    Posts
    64
    Have you tried building it in a query and then copying the SQL into VBA?

  5. #5
    Join Date
    Feb 2004
    Posts
    45
    It was my first attempt but it doesn't work. I think the reason is that the query refer to some combos value on a form. When I tried this way I got a message like this "too few parameters. 11 Expected", and in fact there are 11 combos that the query refer to.

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Looking at the first and second example of the WHERE clause you have include apostrophise round the *, if it was not necessary before it should not be now !?

    Also the quotes round the * will not be there in the SQL string.

    ie. it shoud like this

    & " = " & "*" & ") or (

    Or even simpler

    & " = ""*"") or ( as two successive quotes WITHIN a string give are interpreted as a single quote.

    HTH

    MTB

  7. #7
    Join Date
    Feb 2004
    Posts
    45
    hi mike,
    thanks for the answer. I tried your solutions but the problem persists. "missing operator" message...

  8. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    David

    Just another thought has trucjk me looking at the WHERE again.

    I do not know if the first example works in a stored query, but shouldn't the item/object the the LEFT of the equals sign be related to a filed name in the dabase TABLE or QUERY and the item to RIGHT be the parameter!?

    You seem to have the form controls on the LEFT of then ="*" !!??


    MTB

Posting Permissions

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