Stone Coast Web Design's Weblog

I've done this a few times at work and I always seem to forgot how I did it previously.  In this case, I needed to remove all of the text from a SQL statement from the ORDER BY clause forward.

So a typical SQL statement like this:

SELECT * FROM tableA ORDER BY ID

needed to look like this:

SELECT * FROM tableA

strSqlText = "SELECT * FROM tableA ORDER BY ID"

'This returns the ordinal position (first character of ORDER BY
intOrderByCount = InStr(strSqlText, "ORDER BY")

'This removes the O in ORDER BY
intOrderByCount = intOrderByCount - 1

'This takes only the Left part of the SQL statement and removes everything after intOrderByCount
strSqlText = Left(strSqlText, intOrderByCount)

I'm sure there are better ways of doing this but this way worked for me