I am looking for some help.

I am using Access as a front end and MySQL 5.0 as a back end.

I have created a query that runs in VBA, this query works fine untill I add a line that uses the Sum(Function) see below.

    strSQL = "SELECT " _
    & "tblInventoryTransactions.IMItemCode, " _
    & "tblInventoryTransactions.RicoeInventory, " _
    & "SUM(tblInventoryTransactions.Received) as sReceived " _
    & "From " _
    & "tblInventoryTransactions " _
    & "Where " _
    & "tblInventoryTransactions.IMItemCode = 'HELP56140902' " _
    & "Group By " _
    & "tblInventoryTransactions.IMItemCode, " _
    & "tblInventoryTransactions.RicoeInventory " _
    & "Order By " _
    & "tblInventoryTransactions.IMItemCode Asc, " _
    & "tblInventoryTransactions.RicoeInventory Asc"

When I do a debug.print I get this "SELECT tblInventoryTransactions.IMItemCode, tblInventoryTransactions.RicoeInventory, SUM(tblInventoryTransactions.Received) as sReceived From tblInventoryTransactions Where tblInventoryTransactions.IMItemCode = 'HELP56140902' Group By tblInventoryTransactions.IMItemCode, tblInventoryTransactions.RicoeInventory Order By tblInventoryTransactions.IMItemCode Asc, tblInventoryTransactions.RicoeInventory Asc"

If I paste this into a Passthrough query it works, if I past it into a Query using Navicat it works, but when I run it from VBA it looks like it is working but no records are returned. If I remove the Sum() it works but I need the Sum.

Any help would be appreciated.

I am thinking this might have something to do with Jet supporting ANSI 89, and you using something a little more advanced, like 92. Can you recast the VBA SQL in 89 syntax and see if that works?


mmcdonal

Thanks for your responce but I am sending this as a passthrough Query, so that should bypass Jet and ODBC for that matter.
See Below for code.
Dim cnn As ADODB.Connection
Dim rst_PersonDetails As ADODB.Recordset
Dim strSQL As String
Dim intInventory As Long
Dim strIMItemCode As String

    Set cnn = New ADODB.Connection
    Set rst_PersonDetails = New ADODB.Recordset

    strSQL = "SELECT " _
    & "tblInventoryTransactions.IMItemCode, " _
    & "tblInventoryTransactions.RicoeInventory, " _
    & "SUM(tblInventoryTransactions.Received) as sReceived " _
    & "From " _
    & "tblInventoryTransactions " _
    & "Where " _
    & "tblInventoryTransactions.IMItemCode = 'HELP56140902' " _
    & "Group By " _
    & "tblInventoryTransactions.IMItemCode, " _
    & "tblInventoryTransactions.RicoeInventory " _
    & "Order By " _
    & "tblInventoryTransactions.IMItemCode Asc, " _
    & "tblInventoryTransactions.RicoeInventory Asc"

   cnn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
    & "SERVER=172.20.40.147;" _
    & "DATABASE=Cust_Dev;" _
    & "USER=stephej;" _
    & "PASSWORD=;" _
    & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384 + 524288


    cnn.Open
    rst_PersonDetails.CursorLocation = adUseServer

    Set rst_PersonDetails = cnn.Execute(strSQL, adCmdText)

If Jet and Access are out of the equation, it might be time to post at MySQL forum or search their web site for the answer. This would seem to work on, say, an MS SQL server.

Just for drill I did an echo of your finished syntax. How does it look?

SELECT tblInventoryTransactions.IMItemCode, tblInventoryTransactions.RicoeInventory, SUM(tblInventoryTransactions.Received) as sReceived From tblInventoryTransactions Where tblInventoryTransactions.IMItemCode = 'HELP56140902' Group By tblInventoryTransactions.IMItemCode, tblInventoryTransactions.RicoeInventory Order By tblInventoryTransactions.IMItemCode Asc, tblInventoryTransactions.RicoeInventory Asc


mmcdonal