Query not returning anything from access...

[chaostheory]

I have a spreadsheet, a ever gorwing complex spreadsheet. Anyhow, i have a query in access that is pulling data from a table. The database is returning results. But the query when i put it into excel, is simply not returning anything. I have it messageboxing me the sql query im passing to access and it looks right to me, but i just get the hourglass, the column headers, and no data. Here is what i got.

The only difference is the Where statement in access looks like

WHERE (((dbo_View_TK_TicketInfo.LotNumber) Like "*80525*"))
and the one in my sql statement in excel looks like
"WHERE (((dbo_View_TK_TicketInfo.LotNumber) Like " & lotNum & ")) "
and lotNum = '*80525*'

Screenshot attached of SQL statement being passed to access.

Sub lotRetrieval()
Dim varConn As String
Dim varSQL As String
Dim lotNum As String
Dim count As Integer
Dim numLots As Integer
Dim p As Integer, q As String, x As Integer
Dim totalRows As Integer
Dim colHeader As String, colHeader2 As String
     Application.ScreenUpdating = False
     Range("a2:K30000").ClearContents
     numLots = Application.WorksheetFunction.CountA(Range("O:O"))
    For x = 2 To numLots
        count = Application.WorksheetFunction.CountA(Range("A:A"))
        lotNum = Range("O" & x)
[COLOR=seagreen]      'shows up as 80525 which is correct[/COLOR]
        lotNum = "'*" & lotNum & "*'"
 [COLOR=seagreen]      'changes it to    '*805258*'  which is what i need for sql right since i have to use the regular quotes at the beginning of the line...[/COLOR]
        varConn = "ODBC;DBQ=Z:\_AC_LBP\PPAP\2009 PPAP\Database\DataWarehouse.mdb;Driver={Driver do Microsoft Access (*.mdb)} "
        varSQL = "SELECT dbo_View_TK_TicketInfo.PIN, dbo_View_TK_TicketInfo.LoadDttm, dbo_View_TK_TicketInfo.Green_Ticket_Number, dbo_View_TK_TicketInfo.Product_Code, dbo_View_TK_TicketInfo.Pieces, dbo_View_TK_TicketInfo.Car, dbo_View_TK_TicketInfo.Fired_Ticket_Number, dbo_View_TK_TicketInfo.LotNumber, dbo_View_TK_TicketInfo.UnloadDttm, dbo_View_TK_TicketInfo.UnloadQty, dbo_View_TK_TicketInfo.FinQty " & _
        "FROM dbo_View_TK_TicketInfo " & _
        "WHERE (((dbo_View_TK_TicketInfo.LotNumber) Like " & lotNum & ")) " & _
        "ORDER BY dbo_View_TK_TicketInfo.PIN;"
        MsgBox (varSQL)
 
        With ActiveSheet.QueryTables.Add(Connection:=varConn, Destination:=Range("A" & count + 1))
            .CommandText = varSQL
            .Name = "Query-39008"
            .FieldNames = False
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlOverwriteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = False
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .Refresh BackgroundQuery:=False
        End With
Next x
Application.ScreenUpdating = True
End Sub

 

[Alc]

Is LotNumber a number or text? If it's a number, using quotes around it would cause problems.

If you rmove the where clause from the query, do all the records get displayed as expected?

 

[chaostheory]

There is no way im trying it without the Where statement, i would pull in way too many records, id fill the spreadsheet i think.

I revised my previous post as you replied.

The lotNum is stored as Text in access and String in Excel. I had that problem first with a data type mismatch error, but fixed it.

 

[chaostheory]

screw it i tried it anyhow, Yes it works without the Where statement.

It stopped pulling in at 54,000 records

The Lot number is stored in the database like this 37-80565-0000
hence the '*80565*' part of the statement. It has to be something to do with the number part. Even though it is being stored as text in the database, is Access considering it a number for the query? It can't can it? It should be saying if the text in lot number contains *80565* then pull the record which is i believe how i have it written :/

 

[Alc]

No, you're right.
I didn't know the format of the data, I was just going by the name of the field and thought that could be causing the problem.

 

[chaostheory]

This is highly frustrating. If i do this

"WHERE (((dbo_View_TK_TicketInfo.LotNumber) = '37-80525-0000')) "

It will return the lot number in excel
but if i use

"WHERE (((dbo_View_TK_TicketInfo.LotNumber) like '*80525*')) "

Then it doesnt work. What is different about the Like statement that i am doing wrong

 

[Brianwarnock]

I have no knowledge or experience to bring to this but as Like is not working why not try a different approach, as all of the data is apparently the same format use Mid to extract the 5 characters and compare for an exact match.

Brian

 

[Alc]

I was just going to suggest trying Instr().

 

[chaostheory]

That is what i ended up doing.

Since i found out the format is always going to be the same, and im reading the numbers from a list. I simply put the whole number in there and used = '37-80525-0000' instead of like *80525* like i was originally trying to do.

Its very strange that like and = dont work the same syntax even though the syntax matches up with access perfectly

 

[Brianwarnock]

I assumed that the bits either side of the - could change but were not relevent to the search, otherwise why use Like.

Brian