Solved

Form Fields filter dropdown issue between Access BE and SQL BE

Posted on 2014-02-14
10
712 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 35

Expert Comment

by:PatHartman
ID: 39860863
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
ID: 39861675
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 35

Expert Comment

by:PatHartman
ID: 39861757
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:doublex
ID: 39868521
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
ID: 39871077
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
 
LVL 35

Expert Comment

by:PatHartman
ID: 39871190
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
ID: 39882050
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 35

Expert Comment

by:PatHartman
ID: 39882545
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
ID: 39882839
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 35

Expert Comment

by:PatHartman
ID: 39882894
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How can I create a Message box thru a Macro action for yes / No? 26 58
migrate a SQL 2008 to 2016, 2 28
Webservices in T-SQL 3 31
Text file into sql server 5 23
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

776 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