Sorting Problem

D

DS

I'm trying to sort a text field, it has both numbers and alphas in it,
this doesn't seem to do t. Any input appreciated.
Thanks
DS

"ORDER BY Val(tblChecks.ChkAlias)ASC;"
gives me...
Apple
Band
Cat
1
10
11
2
3
4
5

I need...
Apple
Band
Cat
1
2
3
4
5
6
7
8
9
10
11
 
D

Douglas J. Steele

Works fine for me (although in order to get the text fields at the front, I
had to use "ORDER BY Val(tblChecks.ChkAlias), tblChecks.ChkAlias")
 
D

DS

Douglas said:
Works fine for me (although in order to get the text fields at the front, I
had to use "ORDER BY Val(tblChecks.ChkAlias), tblChecks.ChkAlias")
Click to expand...
Hi Douglas,
Here is the code....perhaps I'm doing something else wrong?

With Me.ListAll
..RowSource = "SELECT tblChecks.TabID, tblChecks.ChkAlias,
tblChecks.CheckID, " & _
"tblCheckDetails.CDMenuID, tblChecks.ChkGuests, tblChecks.ChkPaid,
tblChecks.ChkServer," & _
"tblChecks.ChkPrinted, tblChecks.ChkDividedCheck, tblChecks.ChkTypeID,
tblChecks.ChkCancelled, tblChecks.ChkFXSep " & _
"FROM (tblTabs INNER JOIN tblChecks ON tblTabs.TabID = tblChecks.TabID)
" & _
"LEFT JOIN tblCheckDetails ON tblChecks.CheckID =
tblCheckDetails.CDCheckID " & _
"GROUP BY tblChecks.TabID, tblChecks.ChkAlias, tblChecks.CheckID, " & _
"tblCheckDetails.CDMenuID, tblChecks.ChkGuests, tblChecks.ChkPaid,
tblChecks.ChkServer, " & _
"tblChecks.ChkPrinted, tblChecks.ChkDividedCheck, tblChecks.ChkTypeID,
tblChecks.ChkCancelled, tblChecks.ChkFXSep " & _
"HAVING (((tblChecks.ChkPaid) = 0) " & _
"And ((tblChecks.ChkPrinted) = 0) " & _
"And ((tblChecks.ChkTypeID) Not Like 5) " & _
"And ((tblChecks.ChkDividedCheck) = 0) " & _
"And ((tblChecks.ChkCancelled) = 0) " & _
"And ((tblChecks.ChkFXSep) = 0)) " & _
"ORDER BY Val(tblChecks.ChkAlias)ASC;"
..ColumnCount = 5
..ColumnWidths = "0 in;2 in;0 in;0 in;0 in"
..Requery
End With
 
D

Douglas J. Steele

Other than the missing space between "Val(tblChecks.ChkAlias)" and "ASC",
that looks as though it should work (other than the fact that you won't be
sure what order the alphabetic entries will be in: they'll all evaluate to
0). The ASC isn't actually required, since that's the default with ORDER BY.
 
D

DS

Douglas said:
Other than the missing space between "Val(tblChecks.ChkAlias)" and "ASC",
that looks as though it should work (other than the fact that you won't be
sure what order the alphabetic entries will be in: they'll all evaluate to
0). The ASC isn't actually required, since that's the default with ORDER BY.
Click to expand...
OK Douglas heres te weird part. It works fine in a Query but when I
apply it an Unbound listbox on an Unbound form. It sorts wrong?

Thanks
DS
 
D

Douglas J. Steele

DS said:
OK Douglas heres te weird part. It works fine in a Query but when I apply
it an Unbound listbox on an Unbound form. It sorts wrong?
Click to expand...

Try saving it as a query, and using the query as the RowSource.
 
D

DS

Douglas said:
Try saving it as a query, and using the query as the RowSource.
Click to expand...
I did that still the same problem....but I did this, I did a compact and
repair, that worked! Go figure.
Thanks
DS
 
D

DS

DS said:
I did that still the same problem....but I did this, I did a compact and
repair, that worked! Go figure.
Thanks
DS
Click to expand...
Sorry Douglas I missed the Alpha Part. How can I get the Alphas to sort
in Order?
Thnaks
DS
 
D

Douglas J. Steele

DS said:
Sorry Douglas I missed the Alpha Part. How can I get the Alphas to sort
in Order?
Click to expand...

What I'd suggested was

ORDER BY Val(tblChecks.ChkAlias), tblChecks.ChkAlias
 
D

DS

OK did that. Works great in a Query. But when I copy the code and use
it as a rowsource, it refuses to sort. Tried Compact and repair, didn't
work this time. Why on Earth should it not work???
Thnaks
DS
 
D

Douglas J. Steele

Did you try my suggestion of using the query as the row source, not the SQL?
In other words, if you've saved that SQL as qryMyQuery, put qryMyQuery as
the row source.
 
D

DS

Douglas said:
Did you try my suggestion of using the query as the row source, not the SQL?
In other words, if you've saved that SQL as qryMyQuery, put qryMyQuery as
the row source.
Click to expand...
QUERY as rowsource works fine. It seems to be the SQL statement that is
the culprit. Am I hitting somekind of limitation here?
Thanks
DS
 
D

Douglas J. Steele

DS said:
QUERY as rowsource works fine. It seems to be the SQL statement that is
the culprit. Am I hitting somekind of limitation here?
Click to expand...

Nothing obvious occurs to me, but at least you have a work-around.
 
D

DS

True, In any case here is the code.
Thanks
DS

With Me.ListAll
..RowSource = "SELECT tblChecks.TabID, tblChecks.ChkAlias,
tblChecks.CheckID, " & _
"tblCheckDetails.CDMenuID, tblChecks.ChkGuests, tblChecks.ChkPaid,
tblChecks.ChkServer, " & _
"tblChecks.ChkPrinted, tblChecks.ChkDividedCheck, tblChecks.ChkTypeID,
tblChecks.ChkCancelled, " & _
"tblChecks.ChkFXSep, Val(tblChecks.ChkAlias) AS TS, tblChecks.ChkAlias " & _
"FROM (tblTabs INNER JOIN tblChecks ON tblTabs.TabID = tblChecks.TabID)
" & _
"LEFT JOIN tblCheckDetails ON tblChecks.CheckID =
tblCheckDetails.CDCheckID " & _
"GROUP BY tblChecks.TabID, tblChecks.ChkAlias, tblChecks.CheckID,
tblCheckDetails.CDMenuID, " & _
"tblChecks.ChkGuests, tblChecks.ChkPaid, tblChecks.ChkServer,
tblChecks.ChkPrinted, " & _
"tblChecks.ChkDividedCheck, tblChecks.ChkTypeID, tblChecks.ChkCancelled,
tblChecks.ChkFXSep " & _
"HAVING (((tblChecks.ChkPaid)=0) " & _
"AND ((tblChecks.ChkPrinted)=0) " & _
"AND ((tblChecks.ChkDividedCheck)=0) " & _
"AND ((tblChecks.ChkTypeID) Not Like 5) " & _
"AND ((tblChecks.ChkCancelled)=0) " & _
"AND ((tblChecks.ChkFXSep)=0)) " & _
"ORDER BY Val(tblChecks.ChkAlias), tblChecks.ChkAlias;"
..ColumnCount = 14
..ColumnWidths = "0 in;2 in;0 in;0 in;0 in;0 in;0 in;0 in;0 in;0 in;0
in;0 in;0 in;0 in"
..Requery
End With
 
D

Douglas J. Steele

That's a pretty long SQL string. I can't find a size limit, but there may
very well be one.

Try using an Alias to shorten things:

..RowSource = "SELECT B.TabID, B.ChkAlias, B.CheckID, " & _
"C.CDMenuID, B.ChkGuests, B.ChkPaid, B.ChkServer, " & _
"B.ChkPrinted, B.ChkDividedCheck, B.ChkTypeID, B.ChkCancelled, " & _
"B.ChkFXSep, Val(B.ChkAlias) AS TS, B.ChkAlias " & _
"FROM (tblTabs AS A INNER JOIN tblChecks AS B ON A.TabID = B.TabID) " & _
"LEFT JOIN tblCheckDetails AS C ON B.CheckID = C.CDCheckID " & _
"GROUP BY B.TabID, B.ChkAlias, B.CheckID, C.CDMenuID, " & _
"B.ChkGuests, B.ChkPaid, B.ChkServer, B.ChkPrinted, " & _
"B.ChkDividedCheck, B.ChkTypeID, B.ChkCancelled, B.ChkFXSep " & _
"HAVING (((B.ChkPaid)=0) " & _
"AND ((B.ChkPrinted)=0) " & _
"AND ((B.ChkDividedCheck)=0) " & _
"AND ((B.ChkTypeID) Not Like 5) " & _
"AND ((B.ChkCancelled)=0) " & _
"AND ((B.ChkFXSep)=0)) " & _
"ORDER BY Val(B.ChkAlias), B.ChkAlias;"
 
D

DS

Douglas said:
That's a pretty long SQL string. I can't find a size limit, but there may
very well be one.

Try using an Alias to shorten things:

.RowSource = "SELECT B.TabID, B.ChkAlias, B.CheckID, " & _
"C.CDMenuID, B.ChkGuests, B.ChkPaid, B.ChkServer, " & _
"B.ChkPrinted, B.ChkDividedCheck, B.ChkTypeID, B.ChkCancelled, " & _
"B.ChkFXSep, Val(B.ChkAlias) AS TS, B.ChkAlias " & _
"FROM (tblTabs AS A INNER JOIN tblChecks AS B ON A.TabID = B.TabID) " & _
"LEFT JOIN tblCheckDetails AS C ON B.CheckID = C.CDCheckID " & _
"GROUP BY B.TabID, B.ChkAlias, B.CheckID, C.CDMenuID, " & _
"B.ChkGuests, B.ChkPaid, B.ChkServer, B.ChkPrinted, " & _
"B.ChkDividedCheck, B.ChkTypeID, B.ChkCancelled, B.ChkFXSep " & _
"HAVING (((B.ChkPaid)=0) " & _
"AND ((B.ChkPrinted)=0) " & _
"AND ((B.ChkDividedCheck)=0) " & _
"AND ((B.ChkTypeID) Not Like 5) " & _
"AND ((B.ChkCancelled)=0) " & _
"AND ((B.ChkFXSep)=0)) " & _
"ORDER BY Val(B.ChkAlias), B.ChkAlias;"
Click to expand...
Douglas I just found the reason. And by do I feel stupid! I had 2
rowsources on the command button, because way at the bottom there was an
IF statement! Once again Thank you for your time, patience and help!
DS
 
Top