SQL -> VBA Translation

[PC User]

I'm having trouble converting SQL to VBA. Can someone help?

Original SQL

SELECT tsubPermissionList.UserID, tblMainData.TrackingID, tblMainData.WorkOrder, tblMainData.ActionDescription, tblMainData.Facility, tblMainData.Status, tblMainData.ResponsibleParty, tblMainData.[WHS ID], tblMainData.[PM ID], tblMainData.ProgramID, tblMainData.Location, tblMainData.Branch, tblMainData.Unit, tblMainData.DueDate, tblMainData.ActualStartDate, tblMainData.ActualCompletionDate, tblMainData.Completed, tblMainData.LastModified, tblMainData.CreatedBy, tblMainData.CreatedWhen, tblMainData.EditedBy, tblMainData.EditedWhen, tblMainData.Comments, tblMainData.EmailSent, tblMainData.EmailSender, tblMainData.Selected, *

FROM tsubPermissionList INNER JOIN tblMainData ON tsubPermissionList.FullName = tblMainData.ResponsibleParty

WHERE (((tblMainData.WorkOrder) Like "*" & [Forms]![frmMainEntry]![txtCurrentWorkSearch2] & "*")) OR (((tblMainData.ActionDescription) Like "*" & [Forms]![frmMainEntry]![txtCurrentWorkSearch2] & "*")) OR (((tblMainData.Facility) Like "*" & [Forms]![frmMainEntry]![txtCurrentWorkSearch2] & "*")) OR (((tblMainData.Status) Like "*" & [Forms]![frmMainEntry]![txtCurrentWorkSearch2] & "*"));

My attempt to translate it to VBA.

strSelect = "SELECT DISTINCTROW tblMainData.TrackingID, tblMainData.WorkOrder, tblMainData.ActionDescription, tblMainData.Facility, tblMainData.ResponsibleParty, tblMainData.Status,  tsubPermissionList.UserID, tsubPermissionList.FullName, *"
    strFrom = " FROM tsubPermissionList"
    strJoin = " INNER JOIN tblMainData ON tsubPermissionList.FullName = tblMainData.ResponsibleParty"
    strWhere = " WHERE tblMainData.WorkOrder Like " & "" * "" & "'"" & [Forms]![frmMainEntry]![txtCurrentWorkSearch2] " & "'" & "*" & _
        " OR tblMainData.ActionDescription Like " & "" * "" & "'"" & [Forms]![frmMainEntry]![txtCurrentWorkSearch2] " & "'" & "*" & _
        " OR tblMainData.Facility Like " & "" * "" & "'"" & [Forms]![frmMainEntry]![txtCurrentWorkSearch2] " & "'" & "*" & _
        " OR tblMainData.Status Like " & "" * "" & "'"" & [Forms]![frmMainEntry]![txtCurrentWorkSearch2] " & "'" & "*"

Thanks,
PC

 

[PC User]

Sql -> Vba

I'm thinking that there might be an alternative to putting the entire SQL into VBA by referencing the query directly. Can someone look at this code and let me know if this is possible. I get an error in the FROM clause; however, can someone tell me if this can be done?

    strSelect = "SELECT * "
    strFrom = "FROM qsubSearchCurrentWorkOrders "
    strOrder = "ORDER BY " & col & " " & xorder
    
        If Not IsNull(Forms("frmMainEntry").Form.Controls("cboWOStatus")) Then
        Select Case Forms("frmMainEntry").Form.Controls("cboWOStatus")
            Case "Completed"
                strWhere = "  tblMainData.Status = 'Complete'"
            Case "Deliquent"
                strWhere = "  tblMainData.Status = 'Deliquent'"
            Case "Discontinued"
                strWhere = "  tblMainData.Status = 'Discontinued'"
            Case "In Progress"
                strWhere = "  tblMainData.Status = 'In Progress'"
            Case "Inactive"
                strWhere = "  tblMainData.Status = 'Inactive'"
            Case "Indefinite"
                strWhere = "  tblMainData.Status = 'Indefinite'"
            Case "Pending"
                strWhere = "  tblMainData.Status = 'Pending'"
            Case "Retired/Salvaged"
                strWhere = "  tblMainData.Status = 'Retired/Salvaged'"
            Case "Not Completed"
                strWhere = "  tblMainData.Status <> 'Complete'"
        End Select
    End If
     
         strSQL = strSelect & strFrom & strWhere & strOrder

Thanks,
PC

 

[Banana]

I"m thinking you're supposed to add a ";" at the end of SQL statement (e.g. your Order stirng.

 

[PC User]

Sql -> Vba

That is a good point and I did add it to the statement.

strSQL = strSQL & strSelect & strFrom & strWhere & strOrder & ";"

However, I still get the same error.
Error #3131; Syntax error in FROM clause.

Did you take a look at the alternate method that I'm trying?

Thanks,
PC

 

[pbaldy]

A debug.print on the string would help find the error, but offhand I don't see the word WHERE getting added anywhere.

 

[PC User]

Ok! That did it. Thanks very much. I added the WHERE clause and now it works.

    strSelect = "SELECT * "
    strFrom = "FROM qsubSearchCurrentWorkOrders "
    strOrder = "ORDER BY " & col & " " & xorder
    
        If Not IsNull(Forms("frmMainEntry").Form.Controls("cboWOStatus")) Then
        Select Case Forms("frmMainEntry").Form.Controls("cboWOStatus")
            Case "Completed"
                strWhere = " WHERE  tblMainData.Status = 'Complete'"
            Case "Deliquent"
                strWhere = "  WHERE  tblMainData.Status = 'Deliquent'"
            Case "Discontinued"
                strWhere = "  WHERE  tblMainData.Status = 'Discontinued'"
            Case "In Progress"
                strWhere = "  WHERE  tblMainData.Status = 'In Progress'"
            Case "Inactive"
                strWhere = "  WHERE  tblMainData.Status = 'Inactive'"
            Case "Indefinite"
                strWhere = "  WHERE  tblMainData.Status = 'Indefinite'"
            Case "Pending"
                strWhere = "  WHERE  tblMainData.Status = 'Pending'"
            Case "Retired/Salvaged"
                strWhere = "  WHERE  tblMainData.Status = 'Retired/Salvaged'"
            Case "Not Completed"
                strWhere = "  WHERE  tblMainData.Status <> 'Complete'"
        End Select
    End If
    
         strSQL = strSQL & strSelect & strFrom & strWhere & strOrder & ";"

Thanks very much for your help.

PC