Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL problem (Access 2003/XP)

    Hi everybody:

    This is annoying! I'm building my SQL strings in the usual way, and they are correct. However, Access is inserting hard line breaks in the middle of words so that the resultant sql string doesn't run! Anybody know how to fix this?

    Thanks in advance for your help.

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

    Re: SQL problem (Access 2003/XP)

    What is "the usual way"?

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL problem (Access 2003/XP)

    For example:
    dim strSQL as string

    strSQL = "INSERT INTO tblRptBal_wk ( [Legal Name], InvestmentId, AsOfDate, FundId, FundName, BegBalNet, EndBalNet, PercentReturn, YTDret, NAV )" _
    & " SELECT DISTINCT Investments.Investment, Investments.InvestmentId, ClosedDate AS AsOfDate, Investments.FundId, Funds.FundName, 0 AS BegBalNet, 0 AS EndBalNet, 0 AS PercentReturn, 0 AS YTDRet, 0 AS NAV" _
    & " FROM (((People INNER JOIN InvestmentContacts ON People.PersonId = InvestmentContacts.PersonId) INNER JOIN InvestContactReportType ON People.PersonId = InvestContactReportType.PersonId) INNER JOIN Investments ON InvestContactReportType.InvestmentId = Investments.InvestmentId) INNER JOIN Funds ON Investments.FundId = Funds.FundId" _
    & " WHERE People.PersonId = " & lngPersonID & " And Investments.IsClosed = True And Year([ClosedDate]) = Year(#" & dtAsOf & "#) And Month([ClosedDate]) <= Month(#" & dtAsOf & "#) And InvestContactReportType.ReportTypeId = " & btMo & " And InvestmentContacts.DeletedPerson = False And InvestmentContacts.DeletedFund = False" _
    & " AND Investments.Investment = " & Chr(34) & strLegal & Chr(34) & "" _
    & " ORDER BY Investments.Investment;"

    DoCmd.RunSQL strSQL - This line produces an error. When I type ?strSQL in the Immediate pane, it displays:

    INSERT INTO tblRptBal_wk ( [Legal Name], InvestmentId, AsOfDate, FundId, FundName, BegBalNet, EndBalNet, PercentReturn, YTDret, NAV ) SELECT DISTINCT Investments.Investment, Investments.InvestmentId, ClosedDate AS AsOfDate, Investments.FundId, Funds.FundName, 0 AS BegBalNet, 0 AS EndBalNet, 0 AS PercentReturn, 0 AS YTDRet, 0 AS NAV FROM (((People INNER JOIN InvestmentContacts ON People.PersonId = InvestmentContacts.PersonId) INNER JOIN InvestContactReportType ON People.PersonId = InvestContactReportType.PersonId) INNER JOIN Investments ON InvestContactReportType.InvestmentId = Investments.InvestmentId) INNER JOIN Funds ON Investments.FundId = Funds.FundId WHERE People.PersonId = 2128637703 And Investments.IsClosed = True And Year([ClosedDate]) = Year(#7/31/2004#) And Month([ClosedDate]) <= Month(#7/31/2004#) And InvestContactReportType.ReportTypeId = 6 And InvestmentContacts.DeletedPerson = False And InvestmentContacts.DeletedFund = False AND Investments.Investment = "Andrew Cook" ORDER BY Investments.Inves
    tment;

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

    Re: SQL problem (Access 2003/XP)

    Although an SQL string can be up to 64,000 characters long, you're hitting some kine of other limit here. The maximum length of a line in the Immediate window is 1,024 characters. Your string is just over 1,024 characters long.

    You can shorten the string by shortening the table names, or by using an alias for one or more table names in the SQL. For example, use "... INNER JOIN InvestmentContacts AS T ..." and replace all other occurrences of InvestmentContacts with T.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL problem (Access 2003/XP)

    Thanks, Hans, that worked.

    So, (I ask) what use is the 64,000 SQL character limit?

    Just asking . . .

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

    Re: SQL problem (Access 2003/XP)

    The 64,000 characters is the limit for the SQL statement of a stored query (one you see in the Queries section of the database window)

  7. #7
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL problem (Access 2003/XP)

    Okay, thanks!

Posting Permissions

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