Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Form Fields filter dropdown issue between Access BE and SQL BE

Posted on 2014-02-14
10
Medium Priority
?
762 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 39

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 39

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 39

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 39

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 39

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

664 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