Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 740
  • Last Modified:

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*'
0
Ruckafella
Asked:
Ruckafella
  • 6
  • 5
  • 3
2 Solutions
 
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
 
GRayLCommented:
I run A2003, I just tested that Like clause and it works.  
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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
 
TheTullCommented:
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
 
RuckafellaAuthor 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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now