473,836 Members | 2,132 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Access - Not able to Sort or Filter a Column B/C of Control Source?

11 New Member
I have a column of data that is in a form called Assigned To. This control is a combobox and the control source for this control is as follows:
Expand|Select|Wrap|Line Numbers
  1. SELECT [ID],
  2.  [Contact Name] 
  3. FROM [Contacts Extended] 
  4. ORDER BY [Contact Name];
[Contacts Extended] is a query that is based on my Contacts table and in the query it combines the First Name and Last Name of the individual into a single item of data as it they are stored in separate discrete fields on the table. This is what is in the field property of the Contact Name contains within the[Contacts Extended] query:
Expand|Select|Wrap|Line Numbers
  1. Contact Name: 
  2. IIf(IsNull([Last Name]),
  3.    IIf(IsNull([First Name]),
  4.       [Company],[First Name]),
  5.    IIf(IsNull([First Name]),
  6.       [Last Name],
  7.       [First Name] & " " & [Last Name]))
I appreciate anyone's insight into what may be happening here and I've attached a picture of all data properties for this control.

Attached Images
File Type: jpg properties.jpg (36.0 KB, 14416 views)
Aug 25 '12 #1
10 32127
NeoPa
32,584 Recognized Expert Moderator MVP
You've done a pretty good job of providing the supporting info for the question, but unfortunately not described the problem very well. Clearly you've taken the time to prepare it, so I won't send it back to be redone, but if you could describe what actually happens when you try to sort or filter on this field that would give us something to work with. Maybe it's the workings of the ComboBox that are behaving strangely in some way? If so, give us those details and we can look at it more directly for you. While you're posting, it would also help to know the version of Access you're working on.
Aug 25 '12 #2
bkyzer
11 New Member
If I open the form call Issues in datasheet view that has the field Assigned To and try to sort or filter the data, it simply doesn't return any results. Usually when trying to filter (by right clicking on the column)I'd have the checkboxes available of the contents within that column and I oould filter by the individual(s) name. In this case nothing appears. When I try to sort with either the AtoZ or ZtoA sort criteria nothing happens as well. All of the other sort/filter/ criteria options work as expected for the other items. I'm currently using Access 2007 for this project.

Thanks for your advice.
Aug 25 '12 #3
NeoPa
32,584 Recognized Expert Moderator MVP
I find this, if anything, more confusing than before.

If you sort or filter you get no results? This implies that there are results originally, but when you try to sort on this column they all disappear. Is that really what you mean? That would be extraordinary to say the least.

Next you talk about CheckBoxes. This is right out of the blue. I have no clue from your posts what you're referring to here. The first post was so well explained - yet this one seems to have been written without any consideration for making sense.

Then you say sorting has no effect. That isn't consistent with what you said earlier of course - but maybe your earlier comment was just missing a lot of what was needed to make sense. Most of what you say that does make sense I can't rely on because it contradicts something-else you've also said.

I'd like to help, but with this level of communication it really isn't possible. I've taken onboard that you're running the form in Datasheet mode, which is important as many things are disabled and don't run as designed in that mode. Everything else is just confusing frankly, and I have little idea what it is you're trying to express.
Aug 26 '12 #4
bkyzer
11 New Member
Ok. So after some additional screenshots, I think I've got enough visual data to work from. The attached powerpoint has 3 slides with differing examples of functionality.

Slide#1: An example on a completely separate controle that is a combobox with it's source as a Value List working as expected with regard to sort / filter functionality.

Slide#2: The current state of my Assigned To control that I've been asking about and not having the ability to sort / filter the information in the form based on the properties set.

Slide#3: The same Assigned To control with one change. The bound column is changed from 1 to 2. This produces the ID of my contacts into the form instead of their name which is produced by the SQL statement and supporting query. However, when I make this change, I do have the sort / filter functionality that I'm looking for. However, my question is how do I get this functionality when the bound column is set to 1 and I'm viewing the actual names in the control and not the ID.

Hope this additional information helps in my request.
Attached Files
File Type: pptx ComboBox sort - filter.pptx (1.52 MB, 706 views)
Aug 26 '12 #5
NeoPa
32,584 Recognized Expert Moderator MVP
I'm going to look at this. Mainly because it's clear that you're putting effort into responding to me. I can respect that.

What I find so confusing is why you seem unable to give clear and simple answers to what is fundamentally a very simple question - Explain what happens when you try to sort and/or filter - as just saying it doesn't work is of very little use. It's not anything that should require graphics or anything complicated, and seeing the PPTX file attached leaves me worried that we again have something quite different from what we actually need. Nevertheless, I'll look at it. As I said earlier, as a mark of respect for your obvious efforts to get it right. Unfortunately, I cannot guarantee to take it any further if there is still no reliable explanation for what the problem actually is.
Aug 26 '12 #6
NeoPa
32,584 Recognized Expert Moderator MVP
That did throw some light onto something I admit, although the text indicated there were properties shown below, so naturally I spent some time trying to see if the lower picture, which is too small and indefinite on my screen to be able to read clearly, was what you'd indicated, before realising the properties were displayed quite clearly above the text. Slide #1 seemed unconnected with the other two and too different to make comparisons sensible. #2 & #3 however indicate that it does seem to be treating the incoming data quiite differently, as you commented. I'll say here and now, that I have no easy answers for you. There is a lot here I'm not familiar with, as I haven't used 2007 or 2010 hardly at all yet. Also, the behaviour of Access in this scenario is certainly not what I would expect.

That said, there are a couple of issues that may prove worth looking at in more detail.
  1. Probably the least likely cause, but see what happens when you remove the DefaultValue from the control. On the assumption that the data required is actually the ID rather than the text (If that isn't the case then you have far bigger problems than just what you're describing.), the default specified only makes sense and could match a value in slide #3. I doubt it would interfere so heavily as to cause what you see, but it certainly won't help.
  2. I suppose that brings me next on to the issue of what the value of this control should be. In an RDBMS it makes no sense to store the text value in more than one place (According to Codd's theories of Normalisation - See Database Normalisation and Table Structures), so I would suggest that's an issue that needs to be reviewed before this one you've raised.
  3. The unpredictable nature of the value in the query field [Contact Name] is possibly related, but I would actually be quite surprized if that were the issue (except it's probably the most likely explanation as all the others are even less likely). If it weren't for the possibility of first and last name both being blank, I would suggest the following code in its place :
    Expand|Select|Wrap|Line Numbers
    1. Contact Name: ([First Name] + ' ') & [Last Name]
    The following might work for you to include the Company, but won't give exactly what you had before :
    Expand|Select|Wrap|Line Numbers
    1. Contact Name: (([First Name] + ' ') & [Last Name]) + ' - ') & [Company]

I know this is not much of an answer for you Blake, but it's the best I have I'm afraid.
Aug 26 '12 #7
TheSmileyCoder
2,322 Recognized Expert Moderator Top Contributor
First: I am using Access 2010.

I have tried setting up a form with a combobox that I believe to be similar to yours.
Combobox:
RowSource="SELE CT tbl_Contacts.ID , tbl_Contacts.tx t_FullName FROM tbl_Contacts ORDER BY tbl_Contacts.tx t_FullName; "
Bound Column:1
Column Count:2
Column Widths: 0cm;3cm

I trust that you are familiar with the above setup and know what it means, and how it will display in a NORMAL form (I.e. not datasheet). The combobox is bound to the ID column but displays the Name.

This worked all fine for me in form view, and using the same form but now switching to datasheet view I was still able to sort/filter by the names.


The main difference as I see it, is that the rowsource for my Combobox is a simpler query then yours.


First, Try simplifying the query and see if that resolves teh problem, and if it does, then maybe we can help you write a different query that work better.
Aug 27 '12 #8
sqldave
2 New Member
I had a similar problem sorting people's names stored as FullName. The SQL was simple without a combo box (SELECT tblResumes.Full Name
FROM tblResumes ORDER BY tblResumes.Full Name;). To fix the problem I created a new column in the table, named it StaffName and copied all of the names into it. The same SQL construct worked with StaffName.

My guess is that the table is corrupt because out of about 2000 tables in this database it is only one that gives me any trouble. Now I need to reference the new field in a few hundred forms and reports to get rid of the problem. Perhaps there was a flaw in Access a few years ago, who knows.

I would be curious to know how bkyser solved the problem. Fortunately for me I only had 40 names in the table.
Jun 3 '18 #9
twinnyfo
3,653 Recognized Expert Moderator Specialist
Sqldave,

You could try (now that you have the field StaffName) deleting the FullName Field and then renaming StaffName to FullName. This might just “trick” the Access Table into thinking that all is well?

I’ve had stranger things happen. But yours is a new case of Access idiosyncrasies.
Jun 4 '18 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

7
6292
by: damjanu | last post by:
Hi All; I need little help. I have a datasheet form. I allow user to do 'filter by selection'. My form contains a column with values. As user changes selections, I want to calculate totals. I can do this the first time the form loads.
2
2708
by: Sharon | last post by:
I've had an Access 2000 app running successfully for many months on both Windows XP and Windows 2000. Recently when my Windows 2000 users call a particular report, they get first a dialog requesting a "Replace" parameter. If the user clicks OK, the report opens, but only displays #Error. When the user tries to close the report, he is next presented with the choice of halting the code in break mode or not. Regardless of his choice, the app...
9
7434
by: Jack | last post by:
In the control source of a textbox, is there a way to refer to a column of a combobox? For example: =.Column(2) Thanks, Jack
2
41671
by: Bob | last post by:
I've got a bound report with a query as the record source. I'm adding a total to the footer and have inserted a text box to display it. I'm attempting to set the control source of the text box to a different sum aggregate query using the expression builder. When I select the query and it's single field, single row field it inserts the following in the control source box for the text box. =! I've tested the query (...
4
2724
by: VivN | last post by:
I want to use an expression as the control source for a text box in a report (Access 2000). Whilst I have sucessfully used these simple ones =TimeToSingle(TotalHours(,)) =TimeToSingle(BHolHours(,,)) I get problems when I include a text parameter Scale (control just displays #error)
2
4920
by: jerry.ranch | last post by:
I've been using row source with the QBE for my list and combo boxes..when would I use control source? jerry
2
2920
by: Horst JENS | last post by:
Hi group, have problems with combobox-control in a form (access 2003). I added VBA-Code to on-Change event of the control. If the user choose an value from the control, the filter of the form change to that value. so far it works. problem is, it only works if the user change the (value of the) control with the mouse. As soon as he starts pressing the keyboard, i get errors.
4
13948
by: Lou O | last post by:
Is it possible to use the row (index) of a list box as control source Property for a text box? Example: Text1.ControlSource Property is set to "= List1.Column(0,2)" in design view. When I open the form, Text1 does not display the value of List1.Column(0,2) Am I missing something?
7
30636
by: ncsthbell | last post by:
Working on converting some old MS2000 access applications to MS2007. I have many datasheets in the application and I have noticed that on all of them the column headers have a down arrow with sort/filter options. This arrow is covering up some of the letters on the column descriptions. I have built-in listboxes on my forms that allow the users to select the criteria they need so I do not need the drop down sort/filters on the columns. My...
0
1397
by: Elaine Huseby | last post by:
I have a form "PartsAndInvoices that has all the parts in the database listed in split form view. I want the user to use Access 2007's filter section on the ribbon to choose mutiple filters. Me.Filter may equal (.="dnr1940"). I tried pass ing me.filter to the report but get all the records in the report not the custom filtered set. Private Sub cmdPartsNInvoiceReport_Click() Dim strFilter As String strFilter = "" If Me.Filter = ""...
0
9656
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10575
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10241
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9358
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7774
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5642
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5812
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4443
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4001
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.