Solved

Form Fields filter dropdown issue between Access BE and SQL BE

Posted on 2014-02-14
10
697 Views
Last Modified: 2014-02-24
I am using Windows & 64X and Access 2010 32X
I have a form in Datasheet view where the dropdown for filter displays on a field are different in Regular access backend link from SQl backend link connection.
See attached files.  With Access BE I get the regular dropdown with selections, with SQL I just get A-z or Z-a Filters.

There is one more thing, this forms data comes from four tables to display a record.

I think it is just a setting, but have not been able to locate it.
Like to get the SQL to look and act like the Access 2010 BE dropdown.
Can anyone help?
BE-assignee-Filter-dropdown.JPG
SQl-assignee-filter-dropdown.JPG
BE-followup-date-dropdown.JPG
Followup-date.JPG
0
Comment
Question by:doublex
  • 5
  • 5
10 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I've never seen specific filters like that in Access (I'm using A2010) although I've seen them in Excel.  This is what I get.  Perhaps Access decides what to present based on the table size.  The BE these pictures came from is ACE but I have others that are SQL server and they're the same.
Filters-Text.jpg
Filters-Date.jpg
Filters-Number.jpg
0
 

Author Comment

by:doublex
Comment Utility
In Access 2010, They are called Check box filters.
Like I said if the BE is access it show it like it shoueld.  But when I have an SQL be it show it like the photos I uploaded.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I can't even find the property that turns them on.  I had high hopes for
"Filter Lookup options for ... Database.  But, checking/unchecking the three options doesn't change my filters.
0
 

Author Comment

by:doublex
Comment Utility
I guess going to SQL you loose how it will filter the Field.  From my examples you can see the Difference between Access 2010 and SQL 2008.  I just thought that there may be a reason of what is causing this difference and that there may be a solution, to resolve it?
0
 

Accepted Solution

by:
doublex earned 0 total points
Comment Utility
Found the Solution for filter Checkbox on a field.

Pat here is the solution for the Dropdown field filters on a form with the Backend linked to SQL.
In Options under current database near the bottoms is a filter lookup options for "NAME " database. see photo attached

Check the ODBC fields and then you will get the Checkbox filter list.
 Just updated my user and they are happy now.
ODBC-filterswitch-for-Dropdown-c.JPG
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Thanks for posting back.  Those were the options I was referring to in my earlier post.  I still can't make them work for me no matter how many times I flip them, shut down, and reopen.  Oh well.  It doesn't much matter anyway since I would never use them, especially with a SQL Server BE.  To get the best results with a "real" database back end, you need to use queries with criteria that severely limits the rows/columns returned so you need to have the user supply his criteria ahead of time and then run the query.  That doesn't leave much of an option for local filtering.  

You really want the database server to do the querying.  You don't want Access sucking down the contents of entire tables from the server.  That will make enemies of your local DBA and network Admin and they will curse you and your progeny as well as say bad things about Access.  So, now that you have solved the mystery, I suggest that you don't actually use them.
0
 

Author Closing Comment

by:doublex
Comment Utility
Found the Solution for filter Checkbox on a field.

Pat here is the solution for the Dropdown field filters on a form with the Backend linked to SQL.
In Options under current database near the bottoms is a filter lookup options for "NAME " database. see photo attached

Check the ODBC fields and then you will get the Checkbox filter list.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I figured out why I didn't see them.  I was right-clicking on the field to bring up the right-click menu which gives normal sorting and filtering options.  To get the checkboxes, you have to click the drop down arrow in the column header.  Why they are different is a mystery.
0
 

Author Comment

by:doublex
Comment Utility
Look at my Answer above,  If you are using SQL tables with ODBC, Make sure that you turn on switch i show in the attachment.  It is working grreat now.  I have over 70 user connectingwithout and issues.  And they are happy now.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
It wasn't the settings that was causing the confusion, it was where I was clicking on the form to see the options.  When you right-click into the column, you get one set of sorting / filtering options but when you left-click into the header, you get a different set - the checkboxes.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now