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: 2760
  • Last Modified:

Microsoft Access query - dropdown filter problem

I have recently converted a database to SQL Server. Now some of the columns in a query do not give the option to filter on a particular value. I have not explained this very well, so I recommend looking at the screenshot attached. This displays the dropdown filter that I require, but it does not happen for every field, and I can't understand why.
EE-screenshot.docx
0
rick_danger
Asked:
rick_danger
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
ste5anSenior DeveloperCommented:
Please post screenshots as images. Either embed them in your message using the image icon in the message toolbar or attach the image as image. Don't warp it in a container.

Thus just a guess: The normal problem is that Access can only filter text columns, when they contain less then 256 characters.

Please post the table DDL.
0
 
rick_dangerAuthor Commented:
Don't know how to do any of those things. It's attached to my question - you should be able to download it. However I think you understand my problem, so the screenshot won't really help.

My point is that it works when the tables are held in an Access back end database, but when they are in SQL Server, it doesn't work.  Are there any obvious solutions?
0
 
ste5anSenior DeveloperCommented:
Not without further details.

p.s.
EE message toolbar
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can post screenshots as images embedded in documents, but please realize that many Experts won't download and view those. You'll generally get much better (and faster) responses if you embed your images directly, as suggested by Ste5an - but you don't have to.

So you moved the tables to SQL Server, and you some of the filtering options are not available?

I checked with one of my linked SQL databases, and I can see the Text Filter option (although my Sort options are disabled for Long Text fields). The "Clear Filters" item is disabled for all of my columns, but then I don't have any filters set, so that should be disabled.

But from your image, it looks like the "Text Filters" option is not there. Note that not all Data types offer all filters. See this page for more information:

http://office.microsoft.com/en-us/access-help/apply-a-filter-to-view-select-records-in-an-access-database-HA010341691.aspx#_Toc254891386

The section titled "Select and Apply a Filter Type" has a subsection named "Common Filter Types". This gives more insight into the types of filters you can apply. For example, any field that is a calculated value in your query would not give you any filtering options.

Can you confirm that you're filtering on a standard datatype, and that the Filtering options should show on that datatype?
0
 
rick_dangerAuthor Commented:
Thanks for your reply.

My point is that these filters all work on the Access back end database, but not on the SQL Server database, as per my previous post. However, I will check the pages that you reference. It just seems odd that it used to work, but does not work any longer.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The Text Filters shown in Datasheet view are an Access UI convention. Many things that work fine in Access stop working  (or work differently) when you link to alternate data sources. Access has a lot of control over the data and such when you're housing your data in an ACCDB or MDB database. Access has very little when you house your data in other stores.

Access also allows users to do things they really shouldn't be doing - for example, directly interact with live data, as in a Table or Query datasheet view. Most server-based databases have no such interface, therefore there has never been a need to do that. Once you link the data into Access, Access will try to implement the standard UI conventions, but if the source platform doesn't allow it, Access cannot force it.

That said, for the most part SQL Server acts very much like Access data in Datasheet view. You might review your settings under Access Options to insure that everything looks okay ...
0
 
rick_dangerAuthor Commented:
I've requested that this question be deleted for the following reason:

no satisfactory answer
0
 
Anthony PerkinsCommented:
no satisfactory answer
Re-opening the question to allow the author to close the question more appropriately as they were specifically told that they cannot use Text Filters with MS SQL Server and therefore this question falls under The experts told me “you can not do that”. What do I do now?
0
 
Anthony PerkinsCommented:
The following comments should be awarded points:  http:#a40269934 and http:#a40269958
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now