Like SQL query in Access error

Hello experts,

I'm having issues trying to run a like query in MS Access.  When I run the query it continues to give me an "Unknown" error.  The table that I'm querying from is a linked to an excel spreadsheet.  I'm running the query in Access through the query module.  Please help!  

SELECT Everyone.[First Name], Everyone.[Last Name], Everyone.[Entering Grade], Everyone.[Parent/Guardian Name], Everyone.[Tuition Options]
FROM Everyone
WHERE Everyone.[Tuition Options] Like '*Option A*'
RuckafellaAsked:
Who is Participating?
 
TheTullConnect With a Mentor Commented:
If you try selecting just the top 5 records or so then, do they come back ok before it gets a chance to crap out on you?  This sounds like some bug where Access can't handle using the LIKE function when linking to Excel.
0
 
TheTullCommented:
Have you tried replacing the * characters with %?  I know at least in T-SQL the syntax has you use %, so like this:

where everyone like '%option a%'
0
 
GRayLCommented:
That won't work in Access.  Just to make sure all spelling is correct, run the query without the WHERE clause.  If it returns records you are good to go.  Now replace the WHERE clause  with:

WHERE Everyone.[Tuition Options] Like "*Option A*";
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
GRayLCommented:
I run A2003, I just tested that Like clause and it works.  
0
 
RuckafellaAuthor Commented:
GRayL,

I'm running Access 2007, and it still didnt work.  I erased the WHERE clause and the query returned all rows.  I added an equal WHERE clause and the query returned the  appropriate records.  Its just when I run the LIKE query that everything goes wrong.  I tried your query and it still gives me the "Unknown" error.
0
 
RuckafellaAuthor Commented:
More specifically, what happens is that it will return the correct records and display for about two seconds before the "Unknown" error pops up.  After the Unknown error comes, all the returned values display as "#Name?"
0
 
GRayLCommented:
then I suggest your db is 'busted'.

Try creating a new database, and import all the objects from the old one into the new one - use File, Get External Data, Import, and follow the Wizard.
0
 
GRayLCommented:
Before that you should try a Compact & Repair in Tools
0
 
RuckafellaAuthor Commented:
Compact and Repair didnt do anything to help.  I dont think the database is "busted".  Every other query I run works fine, except the LIKE.  Do you think it may have something to do linked tables?  I would think I should be able to query a linked table.
0
 
GRayLCommented:
Linked to what  - SQL Server?  If so, because the table is linked, SQL Server is providing the link with all the data and Jet should be able to apply the link criteria as if it were an Access table - I think!
0
 
RuckafellaConnect With a Mentor Author Commented:
TheTull,

It looks like you're on to something there.  When I do a Top 5 query with the exact same WHERE clause, it works fine.  Where do you think the problem is?
0
 
RuckafellaAuthor Commented:
Thanks for the hint to use the Top select query.  I was able to locate records that were in the excel spreadsheet that were causing problems.  The was data in excel that was too long for access to handle and this is where there error was coming from.
0
 
TheTullCommented:
Ah, glad to see you got it working.  Sometimes those little checks can really help bring things to light.
0
 
RuckafellaAuthor Commented:
Thanks, solution lead me in the correct direction to find the issue with the excel spreadsheet
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.