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

[bkyzer]

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:

SELECT [ID],
 [Contact Name] 
FROM [Contacts Extended] 
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:

Contact Name: 
IIf(IsNull([Last Name]),
   IIf(IsNull([First Name]),
      [Company],[First Name]),
   IIf(IsNull([First Name]),
      [Last Name],
      [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.

[NeoPa]

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.

[bkyzer]

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.

[NeoPa]

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.

[bkyzer]

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.

[NeoPa]

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.

[NeoPa]

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 :
   

   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 :
   

   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.

[TheSmileyCoder]

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="SELECT tbl_Contacts.ID, tbl_Contacts.txt_FullName FROM tbl_Contacts ORDER BY tbl_Contacts.txt_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.

[sqldave]

I had a similar problem sorting people's names stored as FullName. The SQL was simple without a combo box (SELECT tblResumes.FullName
FROM tblResumes ORDER BY tblResumes.FullName;). 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.

[twinnyfo]

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.

[sqldave]

Thanks twinnyfo, that seems to have done the trick. Your expertise is much appreciated.

BTW, I suspect that it was the same problem that the OP had. Unfortunately there was a mention of a combobox, which I suspect had nothing to do with the problem. Have a great day!