Unable to export records to Excel when LIKE used in Query

[aragorn54915]

I have a form that displays records based upon user defined values. This form has a button that allows the user to send the data to an Excel file. This works great until the query uses LIKE in the select statement. The only data that is showing up in the Excel spread sheet are the field names. From the tests that I have run, it appears that the Do Until rsNA.EOF Loop is being skipped. Does anyone have any ideas on why this section of code would be skipped when using a query that uses the LIKE statement?

    Dim rsNA As ADODB.Recordset
    Dim objExcel As Excel.Application
    Dim objBook As Excel.Workbook
    Dim objSheet As Excel.Worksheet
    Dim strSQLNA As String
    Dim col, row, rsCount As Integer
    Dim fld As ADODB.Field
    Dim EmpID As String
 
    EmpID = Environ$("Username")
    ' Connect to Excel
    Set objExcel = New Excel.Application
    ' Open workbook
    objExcel.Workbooks.Open "C:\Documents and Settings\b64365\Desktop\Global Spend\Template.xlsm"
    'Open worksheet
    Set objSheet = objExcel.ActiveSheet
 
    objExcel.Visible = True
 
    strSQLNA = "SELECT * FROM " & EmpID & "qryNAPOOrdersFiltered"
    Set rsNA = New ADODB.Recordset
    rsNA.Open strSQLNA, CurrentProject.Connection
 
    For col = 0 To rsNA.Fields.Count - 1
        Set fld = rsNA.Fields(col)
        objSheet.Cells(4, col + 1) = fld.Name
        objSheet.Cells(4, col + 1).Font.Bold = True
    Next col
 
    ' NA data sent to Excel
    row = 5
    Do Until rsNA.EOF
        With objSheet
            .Cells(row, "A").NumberFormat = "@"
            .Cells(row, "B").NumberFormat = "@"
            .Cells(row, "E").NumberFormat = "@"
            .Cells(row, "F").NumberFormat = "@"
            .Cells(row, "G").NumberFormat = "@"
            .Cells(row, "H").NumberFormat = "@"
            .Cells(row, "I").NumberFormat = "@"
            .Cells(row, "K").NumberFormat = "@"
            .Cells(row, "L").NumberFormat = "@"
            .Cells(row, "M").Style = "Currency"
            .Cells(row, "P").Style = "Currency"
        End With
        For col = 0 To rsNA.Fields.Count - 1
            objSheet.Cells(row, col + 1) = rsNA.Fields(col).Value
        Next col
        rsNA.MoveNext
        row = row + 1
    Loop
 
    For col = 0 To rsNA.Fields.Count - 1
        objSheet.Columns(col + 1).EntireColumn.AutoFit
    Next col
 
    rsNA.Close
    Set rsNA = Nothing

 

[SimonB1978]

Hi,

The only thing I can tell is that if the "Do Until rsNA.EOF" statement get skipped your recordset IS at EOF, and consequently contains no records to display.

Now, I don't know what the LIKE operators has to do with that since I don't see it. ( is it in qryNAPOOrdersFiltered ?)

Oh, I think there is a problem with your query string. Once concatenated it will read: SELECT * FROM JOEqryNAPOOrdersFiltered. That is assuming your username is JOE. Seems weird to me, is that OK?

Simon B.

 

[aragorn54915]

The reason that you do not see the entire structure of the query in the code is because it is being created on the click of a button on a previous form. The select statement that is being created for the query is shown below. This query is being custom built for each user based upon the selections they wish to search for. In order to keep the queries seperate for each user they are named with the users employee ID number then the name of the query. I know that the queries are working because once they are created, a new form is displayed showing the results. At this point the user is given the option to export the results to an Excel spread sheet. However, the export does not work when the query is created with the Like statement. All other versions of the query work as expected.

SELECT *
FROM qryNAOrders
WHERE (((qryNAOrders.[Vendor Name]) Like '*An*'));

Since records are being displayed in the query, I think that I might try sending the data to a temporary table to see if the export issues still exists.

Thanks for your input and questions.

 

[SimonB1978]

The only thing I could suggest is that you could insert a breakpoint and check that your recordset actually contains records. If your loop get skipped then that is a recordset problem, and Excel has nothing to do with it...

I just did a little search and it seems that ADO uses % as wildcard instead of * . Maybe you could try that...

Simon B.

 

[aragorn54915]

When I first designed the creation of the query, I used %, but that did not work. The only way that I could get the query to work with the Like statement was to use *.

I tested the recordset per your suggestion and you are correct. At the point in the code where it calls the query it shows that the recordset does not contain any data. This is were my confusion is. I can open the query in Access and see that there are a number of records but when the query is called in the SELECT statement all of the records disappear.

Upon further testing the problem is with the use of * vs %. In order for the query to display the records in an Access form it needs the * while eporting it to Excel requires the %. Looks like I need to make some changes in order to make both pieces work. Thanks for your suggestions.