Determining if Excel values equal Access table values

One aspect of my Excel-based project involves comparing the operator-entered part number (in Excel) to a predetermined list of part numbers in one column of an Access database table. Right now, my program is telling me that every part number entered in the spreadsheet (50+) does not match any part number in the database, which I've verified to be incorrect. I've checked that both the spreadsheet part number and the database part number are of the string datatype. To the best of my knowledge, my looping logic seems valid and robust. To the best of my knowledge, there are no hidden characters in either the database cells or in the spreadsheet cells causing this apparent mismatch. I'm completely stumped at this point as to why my program doesn't detect any matches between the spreadsheet and the database table. Below is the Sub containing the code for checking that the part numbers match:

Please Login or Register  to view this content.

I apologize if this is posted in the wrong thread section. This issue seems to be a hybrid issue between Excel and Access with (to me) more of the issue on the Access end.

Thanks for your help.

I don't think I want to make a query in Access though, although I'm not fully certain. Unfortunately, I'm quite the Access newbie. At the very least, I don't think running a parameter query is ideal for my situation. I don't want the user to be prompted to type in a part number and then have Access search through the table to see if there is a match. In my case, the user enters all the data beforehand into Excel and the Excel program is then supposed to check those entered part numbers against the existing part numbers in Access once the program is running. Perhaps it could work if, instead of prompting the user for a part number each time, the query instead took each entry already entered in Excel as the parameter? Would I then still be using a parameter query? If not, doesn't the SQL statement in my code above already create a select query against which I should be able to then compare my excel values? Basically, all I need to do is compare each cell in one column in Excel to each entry in one field of an Access table (1:many). Can it be done without Access queries or with a Select query instead? Thanks for your help.

A couple of things

* Your sql isn't being executed, it should look like this:

Please Login or Register  to view this content.

* SQL isn't case sensitive, whereas Excel is, you're also not removing trailing spaces from the Access field so you may need to do:

Please Login or Register  to view this content.

* Have a look at the filter command on the recordset, it may be faster

*I've found for things like this, it's sometimes faster to stick the recordset into an array and iterate that

Thanks for the responses, but unfortunately none of the aforementioned solutions have fixed the issue with my program. I am still not getting any part number matches.

OK, I can upload my project and my database if that's appropriate. How would I go about doing that? BTW, instead of trying to read into a program database, I've simplified things by now having the program read from a database I created. Only a couple lines of code were minutely changed. Thanks.

P&Q Tracking Rev 7.xlsm
P&Q_Tracking_Data_Storage.zip

Ok great, attached are the zipped Access database and the Excel program. There is no confidential information in either application. For the Excel file, the applicable code is found in Module1 under the sub CheckPN(). Thanks for your help. Please let me know if you have any further questions.

Any ideas as to what specifically could be causing the error?

Thanks for your help.

I found my problem at long last. I needed to add .MoveFirst after my For loop declaration and I needed to set my Do Loop condition = True. Now my code loops correctly.