Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Syntax SQL Help! (A2K)

    A few months ago I copied the SQL of a query designed in the query grid for use as the record source of a combo box. I had a game with the syntax, but eventually found the correct syntax for the SQL.

    I am going through the same process again!

    I can

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Syntax SQL Help! (A2K)

    The University of Bristol has a pretty good set of Access on-line tutorials to teach students. In particular for the Access dialect of SQL I would try:

    http://www.cse.bris.ac.uk/~ccmjs/acc97-r5.htm

    ...but the rest of the materials are worth looking at, too. The document referenced uses an example database that you can also download. Good Luck!

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax SQL Help! (A2K)

    Thanks for the prompt reply Dean. And that Bristol site is pretty good.

    What I need help with is the syntax of the following.


    This is the SQL of a query generated by the query grid. I want to use it as a record source in ADO.

    UPDATE tbl_Old_MatlPitXpp INNER JOIN tbl_New_MatlPitXpp ON tbl_Old_MatlPitXpp.AddOrder = tbl_New_MatlPitXpp.AddOrder SET tbl_New_MatlPitXpp.XPitPrX = [tbl_New_MatlPitXpp]![XPitPrX]+[Forms]![frmPriceInc]![txtPriceChg], tbl_New_MatlPitXpp.IncreaseApplied = True, tbl_New_MatlPitXpp.QuoDate = Date()
    WHERE (((tbl_New_MatlPitXpp.CODE)=[Forms]![frmPriceInc]![cboPits]) AND ((tbl_Old_MatlPitXpp.IncreaseApplied)=True));

    The part [Forms]![frmPriceInc]![txtPriceChg] refers to a text box on the form which would hold data like 0.80.

    I think you do something like

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Syntax SQL Help! (A2K)

    What do you mean, you want to use it as a recordsource in ADO? Your SQL is for an update query, and action queries don't return recordsets. If you mean you're trying to get the correct SQL to execute an update query, that's different.

    If you want to execute the query from code, you could build the SQL string like this (I use aliases to improve readability. You can use them or not):

    strSQL = "UPDATE DISTINCTROW tbl_Old_MatlPitXpp As Old INNER INNER tbl_New_MatlPitXpp As New ON Old.AddOrder = New.AddOrder " _
    & "SET New.XPitPrX = New.XPitPrX + " & [Forms]![frmPriceInc]![txtPriceChg] & " , " _
    & "New.IncreaseApplied = True, New.QuoDate = #" & Date() & "# " _
    & "WHERE (((New.CODE)=" & [Forms]![frmPriceInc]![cboPits] & ") AND " _
    & "((Old.IncreaseApplied)=True));"

    This assumes that CODE is a numeric field and that this code is not being run from within frmPriceInc. It it were built and executed from within that form, you would use the Me operator to refer to the controls. If the IncreaseApplied field is a Yes/No field, then don't put quotes around the True values or it will definitely fail.

    Once you built the SQL string, you could assign it to the CommandText property of the Command object and execute it.

    There are quite a few books on SQL, including a new one specifically oriented toward Jet SQL and SQL Server. That book is "SQL: Access to SQL Server", by Harkins and Reid.
    Charlotte

Posting Permissions

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