Solved How to sort numbers that contain slashes? (1 Viewer)

[Samz01]

Hello!

is there a way to sort numbers with slashes "/" in them ? eg 130/22 , 222/21 , 155/20 , 12/21, 120/19 etc ?
the number before the slash represent the patient number while the number after the slash is the year he/she got registered/diagnosed in.

so for the example earlier the sort should be like this :
120/19
155/20
012/21
222/21
130/22

Many thanks!

 

[NauticalGent]

Use the Replace() function is a query and sort that field.

 

[theDBguy]

Hi. Welcome to AWF!

So, you want to sort by year? If so, you'll have to add an expression for your sorting.

 

[Samz01]

I'll try what you suggested
thank you

 

[Gasman]

I would split by / and sort by required fields.
I do not think split() can be used in a query, so you will need to wrap it in a function.

 

[NauticalGent]

so you will need to wrap it in a function.

Access - Using Split() in a Query | DEVelopers HUT

How to use the Split function in a query in Microsoft Access.

www.devhut.net

 

[Gasman]

Thanks John, must have been another function then, but that makes it easier anyway.

 

[The_Doc_Man]

Well, for starters, you shouldn't be sorting that because it isn't one field. It is TWO fields because the left and right parts mean different things. If this is the way you originally get the number, you should take pains to separate the two numbers and put them in two different fields. Then that sort becomes trivial as a single simple-field operation. AND if for some strange reason you later need to print that number-slash-number sequence, there is such a thing as concatenation for output formatting.

NEVER try to work on two data elements at once if they are easily separable. AND that format of nnn/yy IS easily separable in any of several different ways.

 

[NauticalGent]

Thanks John, must have been another function then, but that makes it easier anyway.

I thought is was similar to one we talked about a couple of years back...? At any rate, I don't know WHY someone at MS hasn't figured out a way to use it a query. I reckon there is a reason, but I have no idea why...

 

[Gasman]

I thought is was similar to one we talked about a couple of years back...? At any rate, I don't know WHY someone at MS hasn't figured out a way to use it a query. I reckon there is a reason, but I have no idea why...

Ah, I did not look at the link just read the title, so I was correct after all.
With my memory, I would not remember that far back.

 

[NauticalGent]

Ah, I did not look at the link just read the title, so I was correct after all.
With my memory, I would not remember that far back.

You're getting lazy Paul!

 

[NauticalGent]

You're getting lazy Paul! Don feel bad, I totally missed the part where the OP said the last two digits were the year.

Wait til Pat sees this...we know how she feels about "mooshing" data elements!

 

[Gasman]

You're getting lazy Paul!

Using my phone.

 

[GPGeorge]

Let me muddy the waters a bit. Those values are not, not NOT numbers. They are strings consisting of digits and slashes. As has been pointed out multiple times, if you want to sort by either patient number, year or both, you can do that, but only if you clean up the field in question.

 

[gemma-the-husky]

I agree with GPGeorge

given you have one example of 012/21, when you split this you get a patient number of 12, not 012 which may be significant in terms of your system.

I would be inclined to keep a separate patient id, and a date, and concatenate the two to get this sort of thing. 134/2022

If not I would make the patient id a fixed length string, but 3 numbers is unlikely to be enough. If you made it a fixed five number string, you would be able to manage records from 00001 to 99999, and they will always align and sort correctly, but now you have the minor issue of not being able to increment a patient ID to get the next patient ID, although that's relatively simple.

If you have mixed length strings you get strange sorting where shorter length strings get sorted incorrectly.

11
110
111
112 ...
119
12
120
121 etc

 

[Pat Hartman]

OK, I've seen it. At least you folks already told the OP that mushing data is wrong so I don't have to. See, that was easy.

I don't know WHY someone at MS hasn't figured out a way to use it a query. I reckon there is a reason, but I have no idea why...

The "why" is because the split function returns an array. SQL does not support arrays in ANY RDBMS that I have ever used. Nor does the original definition of SQL support arrays. Why? because SQL is a declarative language. It is not procedural or event driven. You specify what should happen, not how it should happen. Additionally, arrays are handled differently in all the host languages. So, don't hold your breath.

 

[spaLOGICng]

Use the Replace() function is a query and sort that field.

You did not tell him what to replace!

 

[spaLOGICng]

Hello!

is there a way to sort numbers with slashes "/" in them ? eg 130/22 , 222/21 , 155/20 , 12/21, 120/19 etc ?
the number before the slash represent the patient number while the number after the slash is the year he/she got registered/diagnosed in.

so for the example earlier the sort should be like this :
120/19
155/20
012/21
222/21
130/22

Many thanks!

Assuming you do not have access to the table or query where this values are being sourced from, which is where I would correct the matter if I could.

But from a novice perspective, I would Replace the "/" with a "." then use the CSng() to convert it to number then perform the sort on that.

Kind Regards!

 

[Gasman]

Assuming you do not have access to the table or query where this values are being sourced from, which is where I would correct the matter if I could.

But from a novice perspective, I would Replace the "/" with a "." then use the CSng() to convert it to number then perform the sort on that.

Kind Regards!

Well that is not going to work, as they want to sort by the year, then patient number?
So unless you are going to sort on the decimal portion and then the integer portion, that would not work?
Split sounds so much easier to me. In fact a member on another forum has done exactly that, in a more complicated situation.

Public Function GetValueFromDelimString(sPackedValue As String, nPos As Long, Optional sDelim As String = "|")
Dim sElements() As String
sElements() = Split(sPackedValue, sDelim)
If UBound(sElements) < nPos Then
GetValueFromDelimString = ""
Else
GetValueFromDelimString = sElements(nPos)
End If
End Function

With the query looking like this:

INSERT INTO ITDataAll ( FileName, [1099 Y/N], SetId, VendorId, Location, Name1, Name2, Address1, Address2, Address3, Address4, City, State, Zip, TIN_Type, TIN, Withhold_Name1, Withhold_Name2, WH_Address1, WH_Address2, WH_Address3, WH_Address4, WH_City, WH_State, WH_Zip, WH_Code, Paid_Amt, Pay_Date, [Check] )
SELECT IT_Orig_Data.FileName, GetValueFromDelimString([AllFields],0) AS 1099YN, 
  GetValueFromDelimString([AllFields],1) AS SetId, 
  GetValueFromDelimString([AllFields],2) AS VendorID, 
  GetValueFromDelimString([AllFields],3) AS Location, 
  GetValueFromDelimString([AllFields],4) AS Name1, 
  GetValueFromDelimString([AllFields],5) AS Name2, 
  GetValueFromDelimString([AllFields],6) AS Address1, 
  GetValueFromDelimString([AllFields],7) AS Address2, 
  GetValueFromDelimString([AllFields],8) AS Address3, 
  GetValueFromDelimString([AllFields],9) AS Address4, 
  GetValueFromDelimString([AllFields],10) AS City, 
  GetValueFromDelimString([AllFields],11) AS State, 
  GetValueFromDelimString([AllFields],12) AS Zip, 
  GetValueFromDelimString([AllFields],13) AS TIN_Type, 
  GetValueFromDelimString([AllFields],14) AS TIN, 
  GetValueFromDelimString([AllFields],15) AS Withhold_Name1, 
  GetValueFromDelimString([AllFields],16) AS Withhold_Name2, 
  GetValueFromDelimString([AllFields],17) AS WH_Address1, 
  GetValueFromDelimString([AllFields],18) AS WH_Address2, 
  GetValueFromDelimString([AllFields],19) AS WH_Address3, 
  GetValueFromDelimString([AllFields],20) AS WH_Address4, 
  GetValueFromDelimString([AllFields],21) AS WH_City, 
  GetValueFromDelimString([AllFields],22) AS WH_State, 
  GetValueFromDelimString([AllFields],23) AS WH_Zip, 
  GetValueFromDelimString([AllFields],24) AS WH_Code, 
  GetValueFromDelimString([AllFields],25) AS Paid_Amt, 
  GetValueFromDelimString([AllFields],26) AS [Date], 
  GetValueFromDelimString([AllFields],27) AS [Check]
FROM IT_Orig_Data;

Access VBA Copy table fields into another table

I'm trying to copy two fields from SampleData ("AllFields", "FieldName") into SampleDataAll ("1099YN", "SetId"). All fields are text. This works if I just copy one field rstTableName.Fields("AllFields") and change the Insert Into just one field "Insert Into SampleDataAll (1099YN) ". However...

www.accessforums.net

 

[spaLOGICng]

Well that is not going to work, as they want to sort by the year, then patient number?
So unless you are going to sort on the decimal portion and then the integer portion, that would not work?
Split sounds so much easier to me. In fact a member on another forum has done exactly that, in a more complicated situation.

Public Function GetValueFromDelimString(sPackedValue As String, nPos As Long, Optional sDelim As String = "|")
Dim sElements() As String
sElements() = Split(sPackedValue, sDelim)
If UBound(sElements) < nPos Then
GetValueFromDelimString = ""
Else
GetValueFromDelimString = sElements(nPos)
End If
End Function

With the query looking like this:

INSERT INTO ITDataAll ( FileName, [1099 Y/N], SetId, VendorId, Location, Name1, Name2, Address1, Address2, Address3, Address4, City, State, Zip, TIN_Type, TIN, Withhold_Name1, Withhold_Name2, WH_Address1, WH_Address2, WH_Address3, WH_Address4, WH_City, WH_State, WH_Zip, WH_Code, Paid_Amt, Pay_Date, [Check] )
SELECT IT_Orig_Data.FileName, GetValueFromDelimString([AllFields],0) AS 1099YN,
  GetValueFromDelimString([AllFields],1) AS SetId,
  GetValueFromDelimString([AllFields],2) AS VendorID,
  GetValueFromDelimString([AllFields],3) AS Location,
  GetValueFromDelimString([AllFields],4) AS Name1,
  GetValueFromDelimString([AllFields],5) AS Name2,
  GetValueFromDelimString([AllFields],6) AS Address1,
  GetValueFromDelimString([AllFields],7) AS Address2,
  GetValueFromDelimString([AllFields],8) AS Address3,
  GetValueFromDelimString([AllFields],9) AS Address4,
  GetValueFromDelimString([AllFields],10) AS City,
  GetValueFromDelimString([AllFields],11) AS State,
  GetValueFromDelimString([AllFields],12) AS Zip,
  GetValueFromDelimString([AllFields],13) AS TIN_Type,
  GetValueFromDelimString([AllFields],14) AS TIN,
  GetValueFromDelimString([AllFields],15) AS Withhold_Name1,
  GetValueFromDelimString([AllFields],16) AS Withhold_Name2,
  GetValueFromDelimString([AllFields],17) AS WH_Address1,
  GetValueFromDelimString([AllFields],18) AS WH_Address2,
  GetValueFromDelimString([AllFields],19) AS WH_Address3,
  GetValueFromDelimString([AllFields],20) AS WH_Address4,
  GetValueFromDelimString([AllFields],21) AS WH_City,
  GetValueFromDelimString([AllFields],22) AS WH_State,
  GetValueFromDelimString([AllFields],23) AS WH_Zip,
  GetValueFromDelimString([AllFields],24) AS WH_Code,
  GetValueFromDelimString([AllFields],25) AS Paid_Amt,
  GetValueFromDelimString([AllFields],26) AS [Date],
  GetValueFromDelimString([AllFields],27) AS [Check]
FROM IT_Orig_Data;

Access VBA Copy table fields into another table

I'm trying to copy two fields from SampleData ("AllFields", "FieldName") into SampleDataAll ("1099YN", "SetId"). All fields are text. This works if I just copy one field rstTableName.Fields("AllFields") and change the Insert Into just one field "Insert Into SampleDataAll (1099YN) ". However...

www.accessforums.net

Ah, yes, I assumed left to right, but I now see the value after the slash is the primary, so yes, split would work.