Why is VBA truncating my string RRS feed

  • Question

  • I have the following code:

    Sub GroupByDay()

    SQLStr = "SELECT DISTINCTROW MeterReadings.MeterNum, " _
    & "Format$([MeterReadings].[ReadDateTime],'Short Date') AS Read_time, " _
    & "Max(MeterReadings.Reading) AS [Max Of Reading] " _
    & "FROM MeterReadings " _
    & "GROUP BY MeterReadings.MeterNum, Format$([MeterReadings].[ReadDateTime],'Short Date') " _
    & "ORDER BY MeterReadings.MeterNum, Format$([MeterReadings].[ReadDateTime],'Short Date');"

    DoCmd.RunSQL SQLStr

    I keep getting an error telling me that my SQLStr is not a valid SQL statement.  After considerable searching, I finally found that my statement is being truncated, so I am not getting the full statement.  Any idea why?

    Monday, March 27, 2017 2:32 PM

All replies

  • Hi Paul,

    Where is it being truncated? Even if it wasn't, I don't think your code will work anyway because I think RunSQL is only for action queries, and your SQL is a SELECT query. What are you hoping to accomplish with your code?

    Monday, March 27, 2017 3:13 PM
  • I don't know what makes you think your SQL string is being truncated, but I can tell you for sure that if you execute RunSQL with a SELECT query, you will get error 2342, "A RunSQL action requires an argument consisting of an SQL statement."  I know the message doesn't specifically say that a SELECT statement is invalid here, but in fact the SQL statement must be an action query.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Edited by Dirk Goldgar Monday, March 27, 2017 4:02 PM stated wrong error message. Oops!
    Monday, March 27, 2017 3:53 PM
  • Both theDBGuy and Dirk point out that "DoCmd.RunSQL" is an Action Query. SELECT doesn't work here.

    You might want to post on the purpose of that sub-routine so that others can suggest alternatives.

    Seems like you want to view the records? 

    Tuesday, March 28, 2017 12:54 AM