Question about using MysQL Code (LEFT, RIGHT, LENGTH, MAKETIME) in ACCESS VBA

[raynman1972]

Hello all,

I have the following piece of a query that is causing a Run-time error'3075', Syntax error (missing operator) in query expression. The query works directly in Access as a Pass Through and it works in MySQL Query Browser, but it won't in VBA for some reason and I suspect that it is one of the MySQL Functions (LEFT, RIGHT, LENGTH, MAKETIME or TIMESTAMP) that is really causing the error. Can anybody else confirm or deny?

strSQLa = " SELECT dps.ACDLITE_NAME,cca.SHORTNAME AS SITE " _
    & " ,(CASE WHEN LENGTH(dps.TIME) = 4 THEN TIMESTAMP(ddps.STATISTIC_DATE, MAKETIME(LEFT(dps.TIME,2),RIGHT(dps.TIME,2),0)) " _
    & " WHEN LENGTH(dps.TIME) = 3 THEN TIMESTAMP(ddps.STATISTIC_DATE, MAKETIME(LEFT(dps.TIME,1),RIGHT(dps.TIME,2),0)) " _
    & " WHEN LENGTH(dps.TIME) = 2 THEN TIMESTAMP(ddps.STATISTIC_DATE, MAKETIME(0,RIGHT(dps.TIME,2),0)) " _
    & " WHEN LENGTH(dps.TIME) = 1 THEN TIMESTAMP(ddps.STATISTIC_DATE, MAKETIME(0,0,0)) END) AS ACT_DATETIME " & vbNewLine

[orange]

The function MAKETIME is not a vba function. Pass through works because pass-thru is in the
native syntax of the "other " database.

Pass-thru to Oracle uses Oracle syntax etc.

[raynman1972]

The function MAKETIME is not a vba function. Pass through works because pass-thru is in the
native syntax of the "other " database.

Pass-thru to Oracle uses Oracle syntax etc.

Is there a workaround that you can suggest? Basically, I am hitting a MySQL db via a passthrough in my VBA code and I need to convert the time column I am getting from "1800" to "18:00".

[orange]

My thought is for you to run your Pass thru MySQL query and then work with the returned data with Access and vba.
I haven't worked with Pass throughs for a few years, but I did a lot of work against a corporate Oracle database from Acc2003.
We would run the pass through query (Oracle SQL syntax) and then process the returned data in Access. I'm sure I've forgotten many of the details, but that was the gist of it.