UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Specific Order, Access 2013    
 
   
lamore
post May 19 2019, 08:48 AM
Post#1



Posts: 106
Joined: 25-August 14



Hi All,
So I wrote the following query:
CODE
SELECT DISTINCT CGBoxStatusIn_history.CGBoxScanCode
FROM CGBoxStatusIn_history
WHERE (((CGBoxStatusIn_history.CGBoxScanCode) Like "45001*"));

and I get the following result:

CGBoxScanCode
45001-8
45001-7
45001-4
45001-3
45001-14
45001-13
45001-11

how can I write the query to have the results in the following order:
CGBoxScanCode
45001-3
45001-4
45001-7
45001-8
45001-11
45001-13
45001-14

so in other words in numeric order

Thanks in advance
Go to the top of the page
 
theDBguy
post May 19 2019, 09:51 AM
Post#2


Access Wiki and Forums Moderator
Posts: 75,598
Joined: 19-June 07
From: SunnySandyEggo


To sort the data as numbers, you will have to convert them into numbers at some point. Would these scan codes mean the same or different? 45001-8 and 45001-08

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
gemmathehusky
post May 19 2019, 11:39 AM
Post#3


UtterAccess VIP
Posts: 4,693
Joined: 5-June 07
From: UK


You would either need to split the scan code into 2 so you can sort the second part into a natural order
part1 part2
45001 1
45001 3
45001 5
45001 11


OR - treat it as a string, not a number, and make sure the second part is a consistent length, which is what you are trying to do, in effect.
part number
45001-01
45001-03
45001-05
45001-11





--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
RJD
post May 19 2019, 11:50 AM
Post#4


UtterAccess VIP
Posts: 9,672
Joined: 25-October 10
From: Gulf South USA


Hi: As the others have said, you will have to split the field to get the sort correct. But you will probably have a conflict with the DISTINCT if you do the split in a straight ORDER BY clause. So you might try this ...

CODE
SELECT D.CGBoxScanCode
FROM (SELECT DISTINCT CGBoxScanCode FROM CGBoxStatusIn_history
WHERE CGBoxScanCode Like "45001*") D
ORDER BY Left(D.[CGBoxScanCode],InStr(D.[CGBoxScanCode],"-")-1), CLng(Mid(D.[CGBoxScanCode],InStr(D.[CGBoxScanCode],"-")+1));

I included the first part of the code in the sort in case you might have multiple codes in the first part of the code.

I agree that it would be good to use two fields for the code, or modify the code as Dave suggested - but, alas, sometimes we don't have that choice as an immediate option ... so this is something you can try right away.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
lamore
post May 20 2019, 07:03 AM
Post#5



Posts: 106
Joined: 25-August 14



Hi Joe,
I used your code and it works great! So how can I incorporate a variable to work with it? So in the line of code:
CODE
WHERE CGBoxScanCode Like "45001*") D
how can I make it:
CODE
WHERE CGBoxScanCode Like VARIABLE) D



Again
Thanks for your help,
Lenny
Go to the top of the page
 
RJD
post May 20 2019, 10:36 AM
Post#6


UtterAccess VIP
Posts: 9,672
Joined: 25-October 10
From: Gulf South USA


Hi Lenny: Since we do not know how you are calling the query (form, report, just a query, ...) this is just a general suggestion. If you are using a form to call a report, for example, you can include a control on the form to enter the parameter. The value you enter into the control is the first section of the code (before the dash) or the first part of that section. Then the query will look like this ...

SELECT D.CGBoxScanCode
FROM (SELECT DISTINCT CGBoxScanCode FROM CGBoxStatusIn_history
WHERE CGBoxScanCode Like Forms!frmMyForm!txtCodePart & "*") D
ORDER BY Left(D.[CGBoxScanCode],InStr(D.[CGBoxScanCode],"-")-1), CLng(Mid(D.[CGBoxScanCode],InStr(D.[CGBoxScanCode],"-")+1));

... substituting your form name and control name for frmMyForm and txtCodePart.

If, however, you choose to just have the query prompt you for the value (less desirable, IMO), then you can write the query like this ...

SELECT D.CGBoxScanCode
FROM (SELECT DISTINCT CGBoxScanCode FROM CGBoxStatusIn_history
WHERE CGBoxScanCode Like [Enter First Part of Code] & "*") D
ORDER BY Left(D.[CGBoxScanCode],InStr(D.[CGBoxScanCode],"-")-1), CLng(Mid(D.[CGBoxScanCode],InStr(D.[CGBoxScanCode],"-")+1));

Or there are similar approaches if you are displaying in a form or in the query itself (less desirable still).

Try something like this ... or, if you are having trouble with this, perhaps you could post a cutdown (and zipped) version of your db for us to work with - along with an explanation of the problems you cannot resolve.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th June 2019 - 06:45 AM