• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 801
  • Last Modified:

Form Fields filter dropdown issue between Access BE and SQL BE

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
doublex
Asked:
doublex
  • 5
  • 5
1 Solution
 
PatHartmanCommented:
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
 
doublexAuthor Commented:
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
 
PatHartmanCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
doublexAuthor Commented:
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
 
doublexAuthor Commented:
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
 
PatHartmanCommented:
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
 
doublexAuthor Commented:
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
 
PatHartmanCommented:
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
 
doublexAuthor Commented:
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
 
PatHartmanCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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