SQL Problem

[sacramento]

Hi to all:

I have been a problem with SQL when I try to order the database (ACCESS),by name like:

João

António

I Think the problem it was with (ã) and (ó)...and my question is:

This is normal?Which way can I resolve this?

Thanks

[si_the_geek]

It is normal I'm afraid, and the problem is the accented characters.

The reason this happens is because they are not seen as letters, but other ascii characters (just as - and £ would be). As sorting is done by ascii code, this provides odd results.

I'm afraid I dont know of a way to deal with it nicely, all I can think of is to have an extra column in the database which is a copy of the first, but without the accents. Alternatively you could replace the accented characters with their non-accented version in the Order By clause.

[sacramento]

Alternatively you could replace the accented characters with their non-accented version in the Order By clause.

And can you tell me the way I can do this,because this is my code in order Clause:

Private Sub Option1_Click(Index As Integer)
 
Dim aSQL As String
aSQL = "Select * from exposicao "
  
 If Index = 1 Then aSQL = aSQL & " ORDER BY registo"
 If Index = 2 Then aSQL = aSQL & " ORDER BY expositor"
 If Index = 3 Then aSQL = aSQL & " ORDER BY n_socio"
 If Index = 4 Then aSQL = aSQL & " ORDER BY stamfpo"
 If Index = 5 Then aSQL = aSQL & " ORDER BY stamfocip"
 If Index = 6 Then aSQL = aSQL & " ORDER BY nomesocio"
 
Set rsexposicao = db.OpenRecordset(aSQL)
Set Data1.Recordset = rsexposicao
 
 
End Sub

Thanks

[dglienna]

Do you have a question about this? This would be a little quicker, as only one condition would execute. Each IF statement would be evaluated in your code.

Option Explicit
 
Private Sub Option1_Click(Index As Integer)
  Dim aSQL As String
  aSQL = "Select * from exposicao "
 Select Case Index
 Case Is = 1
    aSQL = aSQL & " ORDER BY registo"
 Case Is = 2
    aSQL = aSQL & " ORDER BY expositor"
 Case Is = 3
    aSQL = aSQL & " ORDER BY n_socio"
 Case Is = 4
    aSQL = aSQL & " ORDER BY stamfpo"
 Case Is = 5
    aSQL = aSQL & " ORDER BY stamfocip"
 Case Is = 6
    aSQL = aSQL & " ORDER BY nomesocio"
 End Select
 Set rsexposicao = db.OpenRecordset(aSQL)
 Set Data1.Recordset = rsexposicao
End Sub

[sacramento]

This is the other code I have for find a name:

Private Sub Text2_Change()
Dim aSQL, Letra As String
Letra = Text2.Text & "*"
aSQL = "Select * from exposicao "
If Option1(1).Value = True Then
aSQL = aSQL & "Where registo Like '" & Letra & "'  Order By registo "
End If
If Option1(2).Value = True Then
aSQL = aSQL & "Where expositor Like '" & Letra & "'  Order By expositor "
End If
Set rsexposicao = db.OpenRecordset(aSQL)
Set Data1.Recordset = rsexposicao
Grelha
mostrar
End Sub

if I put for example in the box text2 "João" The VB returns a error!

Any way to resolve this?

Thanks

[si_the_geek]

What I meant was something like this:

Order By Replace(Replace(registo,'ã','a'),'ó','o') "

Note that the syntax you use to replace will vary by DBMS. (I think this will work for Access).

I have no idea why you get an error using Like, what is the error that is shown, and when does it get displayed (whcih line of code)?

[sacramento]

This is the line a get the error:

Run time error 3420

Set rsexposicao = db.OpenRecordset(aSQL)

I agree with you...I don't now why I have thie error...very strange


Thanks

[si_the_geek]

I don't know the meaning of all the error codes, so you might like to let us know what the message is too

It would also be useful to know what the value of aSQL is at the time, so put this just before it:

Debug.Print aSQL

..and post the SQL that is printed to the Immediate window (press Ctrl-G if it isn't visible).

[sacramento]

This is the message when I get the error:


Select * from exposicao Where nomesocio Like 'jo*' Order By nomesocio

[Hack]

You gave us the error number but what is the error message?

[sacramento]

The error is:

Error 3420
"Object invalid or no longer set"

[juliemac]

This is the message when I get the error:
Select * from exposicao Where nomesocio Like 'jo*' Order By nomesocio

Its definately the accented letter a thats doing it. Try the replace line in the above section. That MIGHT do it. Or... Repace the accented letters in the column names.

Good luck

[dglienna]

Or, create a new column without the characters in it, so you could also return the accented character field. I think the LIKE statement doesn't like accented characters.

[sacramento]

but create a column without accented it's not a easy way no? or not?
because this must be do when I save all the fields in the column and make a copy to that column...not a easy way no?????

[dglienna]

Can you find a field if you don't use Like? That way we'd know if it is a problem with Access (which might be able to be changed) or the LIKE statement (which likely can't be if we can't change Access.

[si_the_geek]

The error is:

Error 3420
"Object invalid or no longer set"

That sounds as if the db object is no longer valid, have you closed it somewhere else?

[sacramento]

Hi guys:

I had decided put a piece of the project here to all see the problem

When in the project please order by "nome sócio" and the tipe in the box "jo" and the project get the error we have speak here!


Thanks

[sacramento]

hi:
NO comments????