Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    proper SQL syntax (Access 2000 all updates)

    This post is as much for edification and learning as for anything else.

    On a form, I want to change the record source when an option button is pressed...to show records where a Discontinued box is checked.

    The form was originally based on qryFamily, the SQL for which is
    SELECT tblFamily.FamilyID, tblFamily.HouseNbr, tblFamily.Street, tblFamily.SecondAddress, tblFamily.City, tblFamily.Province, tblFamily.Code, tblFamily.[911Address], tblFamily.Remarks, tblFamily.Discontinued, tblFamily.DateDiscontinued, "PRIMARY CONTACT: " & DLookUp("ContactData","qryContactData","FamilyID = " & [FamilyID]) AS [Primary Contact]
    FROM tblFamily;"


    On press of the option button, I want the RecordSource to change to
    Me.RecordSource = "SELECT tblFamily.FamilyID, tblFamily.HouseNbr, tblFamily.Street, tblFamily.SecondAddress, tblFamily.City, tblFamily.Province, tblFamily.Code, tblFamily.[911Address], tblFamily.Remarks, tblFamily.Discontinued, tblFamily.DateDiscontinued, "PRIMARY CONTACT: " & DLookUp("ContactData","qryContactData","FamilyID = " & [FamilyID]) AS [Primary Contact]
    FROM tblFamily; _
    & "WHERE tblFamily.Discontinued = Yes;"


    In VBA this faults out. The problem is the alias part of the statement.

    I have tried fixing the quotes, putting additional square brackets around the section "PRIMARY CONTACT: " & DLookUp("ContactData","qryContactData","FamilyID = " & [FamilyID]) AS [Primary Contact], and so on. But I can't make it work.

    I have solved the problem by making a new query based on qryFamily. This resolves the Alias issue from the query. And maybe this is the best way to accomplish the task.

    However, my question is: Is it possible to fix the syntax for an SQL to be used in a VBA statement where an Alias is involved, such as in the above?

    Thanks.

    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: proper SQL syntax (Access 2000 all updates)

    There are several problems with your code:

    1) The entire SQL string is enclosed (as it should be) in double quotes. As a consequence, you cannot just use double quotes within the SQL string, they cause VBA to think the string ends prematurely. There are several workarounds, such as
    - Replace double quotes within the SQL statement with single quotes. or
    - Replace double quotes within the SQL statement with two doube quotes.
    2) If you place a WHERE clause after the SQL statement, you should remove the semi-colon at the end of the original SQL statement.
    3) If you concatenate strings, each part should be enclosed in double quotes.
    4) You cannot have line breaks within the code for a string.
    5) There should be a space before the WHERE part.

    Try
    <code>
    Me.RecordSource = "SELECT FamilyID, HouseNbr, Street, SecondAddress, City, " & _
    "Province, Code, [911Address], Remarks, Discontinued, DateDiscontinued, " & _
    "'PRIMARY CONTACT: ' & DLookUp('ContactData','qryContactData','FamilyID = ' & [FamilyID]) " & _
    "AS [Primary Contact] FROM tblFamily WHERE Discontinued = Yes"
    </code>
    I have broken up the string in more places for readability, and removed the superfluous occurrences of tblFamily.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: proper SQL syntax (Access 2000 all updates)

    Hans
    Thanks! Works perfectly.

    Tom

Posting Permissions

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