SQL Sorting Problem

D

DS

I'm trying to sort the following data....
1,2,3,4,5,6,7,8,9,10,11,12,13,14...etc and A1,A2,B1,B2,B3,B4,C1 etc...
This is the code I'm using...

With Me.ListTransfer
.RowSource = "SELECT PCenter.TableID, PCenter.TableName,
PCenter.TInUse, PCenter.Locked, PCenter.Active, PCenter.TTypeID " & _
"FROM PCType RIGHT JOIN PCenter ON PCType.TTNumber =
PCenter.TTypeID " & _
"WHERE (((PCenter.TInUse) = 0) " & _
"And ((PCenter.Locked) = 0) " & _
"And ((PCenter.Active) = -1) " & _
"And ((PCenter.TTypeID) Like 1)) Or (((PCenter.TInUse) = 0) " & _
"And ((PCenter.Locked) = 0) " & _
"And ((PCenter.Active) = -1) " & _
"And ((PCenter.TTypeID) Like 7)) " & _
"ORDER BY Val(PCenter.TTypeID),Val(PCenter.TableName);"
.Requery

It works, well almost...the problem is that the A1 is ending up at the
bottom of the sort. As such.
1
2
3
......
10
11
12
B1
B2
B3
B4
C1
A1

Any suggestions welcomed!
Thanks
DS
 
S

Steve Schapel

DS,

The Val() function returns the numerical value of data, up to where it
finds a non-digit. So Val("A1") and Val("B1") are the same, i.e. 0.

Try it like this (untested!)...
ORDER BY Val(IIf([TTypeID] Like "#*",[TTypeID],Mid([TTypeID],2)))
 
D

DS

Steve said:
DS,

The Val() function returns the numerical value of data, up to where it
finds a non-digit. So Val("A1") and Val("B1") are the same, i.e. 0.

Try it like this (untested!)...
ORDER BY Val(IIf([TTypeID] Like "#*",[TTypeID],Mid([TTypeID],2)))
Click to expand...
I noticed that in my fooling around they came p all 0.
Si I tried This...TableName being the actual field holding the info and
table type being either type 1 or 7. I want the 1 first ad the 7 second.

"ORDER BY (PCenter.TTypeID),Val(IIf([TableName] Like
""#*"",[TableName],Mid([TableName],1)));"

This still returned the same thing.
I tried differnt combinations and even got more of a mess. i'll keep
trying! Thanks for the input!
DS
 
S

Steve Schapel

DS,

The expression you tried is different from my suggestion in 2
significant ways:
Like "#*" instead of Like ""#*"" ... why did you put in the extra ""s?
Mid([TableName],2) instead of Mid([TableName],1) ... why did you
change this from 2 to 1?
 
D

DS

DS said:
Steve said:
DS,

The Val() function returns the numerical value of data, up to where it
finds a non-digit. So Val("A1") and Val("B1") are the same, i.e. 0.

Try it like this (untested!)...
ORDER BY Val(IIf([TTypeID] Like "#*",[TTypeID],Mid([TTypeID],2)))
Click to expand...
I noticed that in my fooling around they came p all 0.
Si I tried This...TableName being the actual field holding the info and
table type being either type 1 or 7. I want the 1 first ad the 7 second.

"ORDER BY (PCenter.TTypeID),Val(IIf([TableName] Like
""#*"",[TableName],Mid([TableName],1)));"

This still returned the same thing.
I tried differnt combinations and even got more of a mess. i'll keep
trying! Thanks for the input!
DS
Click to expand...
This Works,
With Me.ListTransfer
.RowSource = "SELECT PCenter.TableID, PCenter.TableName,
PCenter.TInUse, PCenter.Locked, PCenter.Active, PCenter.TTypeID " & _
"FROM PCType RIGHT JOIN PCenter ON PCType.TTNumber =
PCenter.TTypeID " & _
"WHERE (((PCenter.TInUse) = 0) " & _
"And ((PCenter.Locked) = 0) " & _
"And ((PCenter.Active) = -1) " & _
"And ((PCenter.TTypeID) Like 1)) Or (((PCenter.TInUse) = 0) " & _
"And ((PCenter.Locked) = 0) " & _
"And ((PCenter.Active) = -1) " & _
"And ((PCenter.TTypeID) Like 7)) " & _
"ORDER BY
(PCenter.TTypeID),Val(PCenter.TableName),Left([TableName],1);"
.Requery
End With
Thanks Steve, It got me going!
DS
 
D

DS

Steve said:
DS,

The expression you tried is different from my suggestion in 2
significant ways:
Like "#*" instead of Like ""#*"" ... why did you put in the extra ""s?
Mid([TableName],2) instead of Mid([TableName],1) ... why did you change
this from 2 to 1?
Click to expand...
Because the Single " didn't work in the SQL statement. When I changed
it to a double it accepted that. I changed the 2 to 1 because I ws
getting not the right results. Thanks for help though. It was enough
to get me running in the right direction. Many thanks!
DS
 
S

Steve Schapel

DS,

I am surprised to hear that this works. I would expect this to put all
the ones with the letter prefix ahead of those without.
 
S

Steve Schapel

DS,

Ah, ok, well here's what I would suggest then...
"ORDER BY (PCenter.TTypeID),[TableName] Like '#*',IIf([TableName] Like
'#*',Val([TableName]),Left([TableName],1) & Val(Mid([TableName],2)))"

I am pretty sure that will take care of all eventualities, including if
there are 2 digits in the prefixed values, e.g. A13.
 
D

DS

Steve said:
DS,

Ah, ok, well here's what I would suggest then...
"ORDER BY (PCenter.TTypeID),[TableName] Like '#*',IIf([TableName] Like
'#*',Val([TableName]),Left([TableName],1) & Val(Mid([TableName],2)))"

I am pretty sure that will take care of all eventualities, including if
there are 2 digits in the prefixed values, e.g. A13.
Click to expand...
I'll give it a try, I was suprised the other way worked as well!
Thanks
DS
 
D

DS

DS said:
Steve said:
DS,

Ah, ok, well here's what I would suggest then...
"ORDER BY (PCenter.TTypeID),[TableName] Like '#*',IIf([TableName]
Like '#*',Val([TableName]),Left([TableName],1) &
Val(Mid([TableName],2)))"

I am pretty sure that will take care of all eventualities, including
if there are 2 digits in the prefixed values, e.g. A13.
Click to expand...
I'll give it a try, I was suprised the other way worked as well!
Thanks
DS
Click to expand...
No that didn't work, It did this...
1
10
11
12
13
14
2
3
4
5
6
A1
B1
B2
B3
C1

DS
 
S

Steve Schapel

DS,

Sorry, too hasty, and thinking it was too easy :) Should have tried it
out myself first! Looks like Access continues to treat the result of
the Val() function as text if there is any text in the same column.

Don't know if there is an easier way, but this seems to do the job...
"ORDER BY [TableName] Like '#*', Val([TableName]),
Left([TableName],1), Val(Mid([TableName],2))"
 
D

DS

Steve said:
DS,

Sorry, too hasty, and thinking it was too easy :) Should have tried it
out myself first! Looks like Access continues to treat the result of
the Val() function as text if there is any text in the same column.

Don't know if there is an easier way, but this seems to do the job...
"ORDER BY [TableName] Like '#*', Val([TableName]), Left([TableName],1),
Val(Mid([TableName],2))"
Click to expand...
That works! Thank you for your persistance!
Thanks
DS
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL Returns No Value 2
VBA Coding Help for Beginner 0
SQL Syntax Problem 4
VBA for Dependents shortcut menu 0
Need Help with a Formula 4
Data Validation and drop-down list using IF, AND and what else? 8
Need help with a formula 8
Very strange behaviour of VLOOKUP in Excel 2010 4

Top