code not finding match

[dharkangel]

I have some code that I am trying to execute but I'm just not getting the correct results. I have modified the data so that it should find a match and it is not. When it gets to this piece of code: "If barcDocReport.RecordCount > 0 Then" it shows up as "0" in my MsgBox code. Is there something I am missing between comparing data from a .csv file and a query pulling from a linked table in Access? The linked table is coming from a SQL Server from Sharepoint.

Thanks,

Address is equal to a url: (ex.

http://servername/directory1/directory2/fileName.doc)

fileName = Trim(Mid(Address, InStrRev(Address, "/") + 1))

MsgBox "fileName: " & fileName

'set object for Barcelona Report csv file
barcDocReport.ActiveConnection = myConnection

MySQL2 = "SELECT BarcelonadocReport.Document FROM BarcelonadocReport WHERE TRIM(BarcelonadocReport.[Document])='& TRIM(fileName) &';"

barcDocReport.Open MySQL2, , adOpenStatic


If barcDocReport.RecordCount > 0 Then
MsgBox "FileName Match: " & barcDocReport.Fields("Document").Value
End If

barcDocReport.Close
Set barcDocReport = Nothing

End If

barcDPCheckPointDeliverables.MoveNext

 

[JoeAtWork]

RecordCount is often not a reliable property, especially if you haven't moved to the last record. If I want to confirm if any records were returned, I check both BOF and EOF, such as:

        If Not(barcDocReport.BOF AND barcDocReport.EOF) Then
            MsgBox "FileName Match: " & barcDocReport.Fields("Document").Value
        End If

 

[JoeAtWork]

Sorry, I misread what you were saying. Thought you were having trouble detecting if any records were returned.

Your SQL statement looks correct to me.

The only thing that it might be from what I can tell, SQL Server doesn't use TRIM, instead you usually use a combination of LTRIM and RTRIM. ADO syntax usually matches more closely with SQL Server than Access SQL syntax.