Hi there, please can someone help me with this. I have a VARchar field for NAMES and I need it order numbers correcly and I cant seem to work out the right SQL systax.

My sql is this

SQL = SQL & " Select Distinct" & vbCrLf
    SQL = SQL & "     galleries.`gal_name`," & vbCrLf
    SQL = SQL & "     galleries.`gal_group`," & vbCrLf
    SQL = SQL & "     galleries.`gal_user`," & vbCrLf
    SQL = SQL & "     galleries.`gal_pw`" & vbCrLf
    SQL = SQL & " From" & vbCrLf
    SQL = SQL & "     galleries   galleries" & vbCrLf
SQL_Ext = SQL_Ext & " Order by" & vbCrLf
    SQL_Ext = SQL_Ext & "     galleries.gal_name  Asc" & vbCrLf
SQL_GroupOnly = SQL_GroupOnly & " Order by" & vbCrLf
    SQL_GroupOnly = SQL_GroupOnly & "     galleries.gal_name  Asc" & vbCrLf

This is working FINE BUt it gives me this

Lauras First (1)
Lauras First (10)
Lauras First (11)
Lauras First (2)
Lauras First (3)
Lauras First (4)
Lauras First (5)
Lauras First (6)
Lauras First (7)
Lauras First (8)
Lauras First (9)


WHAT I NEED IS THIS

Lauras First (1)
Lauras First (2)
Lauras First (3)
Lauras First (4)
Lauras First (5)
Lauras First (6)
Lauras First (7)
Lauras First (8)
Lauras First (9)
Lauras First (10)
Lauras First (11)

i know i can use +0 to set the numeric value to a number. But I cant get it right

Can anyone help me please ....

Thanks in advance

Geno

what is the actual sql query? i see a sqlgrouponly and sql ext, but don't know which is used

also what is this (1)? is that a gallery name, group, or what?

HI dickersonka, thank you for helping me

ok .. the (1) and (2) is what the user has placed into the Field as part of the NAME and that is what I am sorting on.

So really the NAME field data is

Lauras First (1)
Lauras First (2)

These are the names of the galleries


the actul page filters all records on a user name after login and then displays the GALLERY NAMEs

the NAME field is ordered asc

When I first wrote this I didnt take into account there would be mulipul galleries of the same name but a user would give a

Gal 1
gal 2
gal 3

etc

So I need to try and add an order string to resolve the NUMERIC elements in the text field into numeric. then the ORDER should work correctly and display any numeric data in NORMAL ORDER.

Using +0 should do it but I cant get the syntax right.

Any help is much apprecated

I hope you understand my problem better now.

Cheers

Geno

you will need to parse out the "(#)" because you have the rest of the string to deal with, it is going to make your order by pretty messy, you sure you aren't able to separate (1) into a column?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.