MS Access Sort by ASCII value?

[Mrbeefy]

Hello.
I'm not a coder, or even a power user.

I've had a database of my CD collection in DBase for Dos for over a decade now. I'd like to convert it to something a little more modern, but I don't need too many bells an whistles. It seems that MS Access would be fine for my needs, but after importing the database, MS access doesn't sort it properly, and completely ignores the ASCII order.

Is there a way to make Access do this, and/or is there a better application for me?



an example of my database:

should sort in this order: *,A,`

MS sorts in this order: *,`,A

I want some records to fall at the end of the database, without inserting a leading space character in the other records.

 

[allanon]

We had a question similar to this last week. Here is what I would suggest you do.

1) pass this code to a module in your project:

Public Function CountStringValue(ByVal String1 As String) As Long
Dim i As Integer
On Error GoTo CountStringValueError

CountStringValue = 0
For i = 1 To Len(Trim(String1))
CountStringValue = CountStringValue + Asc(Mid(String1, i, 1))
Next i
Exit Function
CountStringValueError:
MsgBox (Err.Description)
End Function

2) Access the sorted table threw a query and the query should look something like this.

SELECT MyTable.*
FROM MyTable
ORDER BY CountStringValue(Table1.MySortField) ASC

 

[Mrbeefy]

I can't get that to work.

Like I said, I'm not a coder.

I pasted 1) into a new module.
and then tried to make 2) fit into the slots of query.

and the only ordering choices were ascending/descending, or no t sorted, so it freaked out trying to input "CountString..."

This is so frustrating.

Can anyone suggest a simple database that follows the rules of Ascii?

 

[allanon]

I tried this in access97 and it works no problem.

There is a typo in the query above in that it should read:

SELECT MyTable.*
FROM MyTable
ORDER BY CountStringValue(MyTable.MySortField) ASC

note that it was:

CountStringValue(Table1.MySortField)

there is no table1.

If this is still causing you problems then display the query code by clicking SQL in the top left and post it here along with the CountStringValue function. I will see why they are not working. I did try the above solution (without the typo) and it works.