Results 1 to 3 of 3

Thread: Syntax for SQL filter in MS Access

  1. #1
    Join Date
    Oct 2002
    Location
    Indiana USA
    Posts
    79

    Syntax for SQL filter in MS Access

    I want to use an SQL statement as a filter in setting a recordset. Here are 2 statements:
    strJob = Me![JNum]
    Set rst = dbs.OpenRecordset("SELECT * FROM [JMR] WHERE [JNum] = strJob", dbOpenDynaset)

    The [JMR] table has a field [JNum]. An open form has a control which uses the [JNum} field from a different table and this latter information is to filter the [JMR] records based on its [JNum] field. I think the problem is in the use of single and double quotes in the latter part of my SQL statement. Or what?

    Thanks

  2. #2
    Join Date
    Oct 2002
    Posts
    1
    strJob = Me![JNum]

    The correct syntax depends on the type of variable that strJob is.

    It appears to be a string so the syntax would be

    Set rst = dbs.OpenRecordset("SELECT * FROM [JMR] WHERE ([JMR].[JNum] = '" & strJob & "');", dbOpenDynaset)

    If it were a number, it would be
    Set rst = dbs.OpenRecordset("SELECT * FROM [JMR] WHERE ([JMR].[JNum] = " & strJob & ");", dbOpenDynaset)

    - The [JMR]. is sometimes necesary.
    - The ; is normally required.
    - I normally use "()" to make precedence clear.

    hope it works

  3. #3
    Join Date
    Oct 2002
    Location
    Indiana USA
    Posts
    79

    Re: SQL syntax

    Fabulous! Works like a charm!

Posting Permissions

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