Form Combo Box Drop Down Sorting

ldbryant55

New member
Local time
Today, 06:02
Joined
Oct 18, 2018
Messages
8
Hello, I'm stumped. I have a drop down combo box in a form that lists projects by number, pulled from a project number field in the base table. It used to list the numbers in order, 1, 2, 3, etc., all of a sudden, it is now listing them like this: 1, 10, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 11, 110, 111, 112, etc. (see the pattern?)

I've tried several solutions, including adding an SQL "ORDER BY ProjectNumber", reformatting the field in the original table, reformatting the field in the form, etc. Right now, the field in the table is set as an integer, and the field in the form is set to format "blank". Like I said, I've tried all kinds of combinations, (standard, fixed, general number, etc.), but it still sorts the same way. What can I do to fix this?
 
you have them as STRINGS.
you should set the data type to NUMBER. then it will sort correctly.
 
Thanks! How can I reset them? In the table? In the form?
 
If they are really numbers then they should be stored as numbers. So change the datatype in the table.
 
I do have the field classified as a number in the table. What can I change to help this situation? Thanks!
 
What's the combo rowsource, there must be something changing it to make it think it's a text field.
 
The rowsource has this: SELECT tblProject.ProjectID FROM tblProject UNION SELECT '*' FROM tblProject ORDER BY ProjectID

The table (tblProject) has the field (ProjectID), which is the number I'm trying to list in the drop down on the form. There are several drop downs on the form, for various categories.
 
Has that happened since you added the UNION clause?
Why is it even there?
 
Honestly, I can't remember. I developed this many years ago, and I want to say that union clause was something I had to add to get the form to work correctly, but I can't remember now. I can try removing it, and see if that makes a difference. What would you suggest?
 
Ok, I removed the "Union Select" from the code, and it did fix the sorting issue, but caused a different issue. There is a "Reset" button on the form, that upon click will reset all of these combo box drop downs to their default text which is "*". When I try to use the reset button now, it gives me an error: "Runtime error 2113 Value you entered isn't valid for this field". When I then go to "Debug", it highlights the ProjectID line in the SQL command:

Private Sub cmdReset_Click()

Me![cboBuyer] = "*"
Me![cboPlant] = "*"
Me![cboSupplier] = "*"
Me![cboCommodity] = "*"
Me![cboFirstTier] = "*"
Me![cboProductLine] = "*"
Me![cboProjectBasis] = "*"
Me![cboProjectID] = "*"
Me![cboProbability] = "*"
Me![cboProjectType] = "*"
Me![cboPriority] = "*"
Me![cboComplete] = "*"

End Sub
 
The ProjectID is not text though, it is a number?
I'm not sure what you should be setting it to, Null perhaps.?

Otherwise what has changed to make the ProjectID appear to be text?
 
I don't know what changed, it just started listing that way one day recently. Yes, to answer your question, ProjectID is a number. Any suggestions would be greatly appreciated. Thanks.
 
Try setting it to Null

I don't know what changed, it just started listing that way one day recently. Yes, to answer your question, ProjectID is a number. Any suggestions would be greatly appreciated. Thanks.
 
That should do it:
Code:
[COLOR=Red][COLOR=Black]Me![cboProjectID] =[/COLOR] [B]Null [/B][/COLOR]
 
By using the Union with the "*" Access will convert the resultant results to Text as * can't be a number, hence the sorting issue.
 

Users who are viewing this thread

Back
Top Bottom