Solved

Form Fields filter dropdown issue between Access BE and SQL BE

Posted on 2014-02-14
10
737 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 37

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 37

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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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 37

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 37

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 37

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

732 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