Lookup Excel data on separate server with criteria, from Access application using VBA

From my access application I'm trying to lookup values in an Excel file. I thought I could like the file and simply
do a Dlookup on the query I created on the linked Excel file but it doesn't seem to be able to search the proper
value. I can see the data from the first row , but not the value I searched on!
I'm assuming I either have to use Vlookup or something else to the query for it to work!
I'm also guessing that Excel doesn't present the data to access as an external database would!

There might even be a way to use VBA to access the excel file directly without linking but not sure how to do that!

The Excel file is about 1700 records an it changes regularily ( minutes)

I have no code to show other than using a typical Dlookup!

         Dim xyz as string
         string xyz = DLookup("[Sector]", "Cell_query", "[network] = '" & Me.network.Value & "'")

thanks

Pete
Peter GrovesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Is network unique in the spreadsheet?  
Is Network a string?  If it isn't, remove the single quotes.
Do you see the network value you are looking for?

DLookup() is pretty inefficient but it should work as you are using it.  The trouble may be with left or right padding on the string field.  If the network is 1234bbbb (b = zls or "blank") will not = 1234)
Peter GrovesAuthor Commented:
network should be unique and is alphanumeric 7 character with never any spaces!

I put a break at the end of the code to see what the variable values are showing and they
just show the first entry of the data! It doesn't seem to search through!  I thought it was a
compatibility issue since the data is in an excel spreadsheet on a different server hard drive!
Opening the linked access file (Cell_query)  shows all the data!  

Pete
PatHartmanCommented:
just show the first entry of the data! It doesn't seem to search through!
Do you mean that it shows the first match?
Peter GrovesAuthor Commented:
Turns out the field name was simply misspelled!  Everything works fine!
Noobie error!  I just assumed that a missspelling would just give an error file does not exist!
Date looked valid when I checked the values in a breakpoint sorry !

Pete

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Peter GrovesAuthor Commented:
An error I made caused the problem , which I noticed later!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.