Why is VBA truncating my string

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?

Answer 1

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?

Answer 2

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

Answer 3

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?