Full Version: Order By in Row Source of Combo Box.
UtterAccess Forums > Microsoft® Access > Access Forms
WongMeister
I have a combo box which I'm trying to write some code to limit the displayed values.
I have the following:
SELECT DISTINCT [MonthYear] FROM tblStaffing WHERE [MonthYear] IS NOT NULL;
The above works. However, when I try to add another condition (ORDER BY numMonth), it doesn't work:
SELECT DISTINCT [MonthYear] FROM tblStaffing WHERE [MonthYear] IS NOT NULL ORDER BY numMonth;
I've also tried the following which also does not work:
SELECT ORDER BY numMonth DISTINCT [MonthYear] FROM tblStaffing WHERE [MonthYear] IS NOT NULL;
What am I doing wrong/
Thanks.
jmcwk
IS MonthYear a Date/Time datatype ? If so have you tried formatting MonthYear to
MyFormat:Format([MonthYear],"mm/yy") and then sorting accordingly ?
WongMeister
Hi John,
onthYear is just a text field. numMonth is a numeric field.
It seems like when I try including BOTH a DISTINCT and ORDER BY clause, i get a conflict. I don't know if there's a way around this or not.
theDBguy
Hi,
That do you mean by "it doesn't work?" Can you show us an example of what values are stored in MonthYear and numMonth?
jmcwk
Just tested The below and this works ????
SELECT DISTINCT Table1.monthyear, Table1.nummonth
FROM Table1
WHERE (((Table1.monthyear) Is Not Null))
Orderd The below and this works ????
SELECT DISTINCT Table1.monthyear, Table1.nummonth
FROM Table1
WHERE (((Table1.monthyear) Is Not Null))
ORDER BY Table1.nummonth;
my monthyear field is text value(s) are entered as 02/08,02/08,01/09,12/09,01/09 etc. and nummonth is Number datatype value(s) are 2,2,1,12,1
jmcwk
Hello there DB ! Hope things are well with you and yours on this fine DAY !
WongMeister
Hi Shrek...
onthYear is just a text field, where I type in values such as "January 2008," "February 2008" and "July 2007"
numMonth is a numeric field where I have values such as 1,2,3,4 etc.
When I try attempt to click the dropdown, I get a message that says:
"ORDER BY CLAUSE (numMonth) conflicts with DISTINCT"
When I remove either the DISTINCT or ORDER BY clause, it works, but I can't keep both in the statement.
If this doesn't work, I may need to simply create a separate query as my row source, but it just doesn't seem efficient to have to create a query for a combo box.
Thanks.
WongMeister
Thanks John and Shrek for your time.
John... Your recommendation works perfectly:
SELECT DISTINCT [MonthYear],[numMonth] FROM tblStaffing WHERE [MonthYear] IS NOT NULL ORDER BY [numMonth];
Thanks again for your help an patience with me.
jmcwk
Not a problem theDBGuy and I were glad to help, Good Luck with your project !
theDBguy
Hi John,
Everything is hectic here, but I'm fine. Thank you. sad.gif
theDBguy
Wongmeister,
o see why you were having that problem, see this link
Good luck with your project.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.