EXCEL - VBA - SQL - Doubt in the query using CAST

Question

Hello, First I apologize to ADM if there is any error in formatting the question, I am novice here rsrs.

I have a question in the QUERY query using MAX and CAST

Goal: Return the highest value in the ORDER column by forcing it to be an integer number, where in the LINPRD column it is "A"

Expected Result: 5

Error occurring on code line = RS.Open sql, DB

Follow my routine

Sub Pesquisa_SQL_CAST ()
 
Dim DB As New ADODB.Connection 'Variável de Conexão ou Caminho para o banco de dados
Dim RS As New ADODB.Recordset 'Variável para abrir conexão
Dim sql As String
Dim Final_loop As Integer

With DB
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Properties("Data Source") = ThisWorkbook.FullName
        .Properties("Extended Properties") = "Excel 12.0 Xml; HDR=YES; IMEX=1"
        .Mode = adModeRead
        .CursorLocation = adUseClient
        .Open
End With

sql = "SELECT MAX(ORDEM) FROM [Planilha3$A1:B6] WHERE [LINPRD] = 'A' AND CAST(ORDEM AS INTEGER) = '%'"

RS.Open sql, DB

Final_loop = RS(0)

End Sub

The table in Planilha3$A1:B6 is simplified like this;

I tried to use Cast as well;

sql = "SELECT MAX(CAST(ORDEM AS INTEGER)) FROM [Planilha3$A1:B6] WHERE [LINPRD] = 'A' AND CAST(ORDEM AS INTEGER) = '%'"

but also results in error.

I just succeed in the search, not using Cast this way;

sql = "SELECT MAX(ORDEM) FROM [Planilha3$A1:B6] WHERE [LINPRD] = 'A'"

But I would like to use Cast, to force the search as an INTEGER, since, there is the possibility that some cell in the ORDER column is typed as TEXT.

Taking advantage of 2 more rsrs questions.;

1º - How to reference a named formatted table or just a named region in FROM

2º - CAST and DISTINCT can even be used in SQL query by VBA, or I should use some corresponding respectively.

Note: "MICROSOFT ACTIVEX DATA OBJECTS 6.1 LIBRARY" is enabled in "References - Vbaproject"

Thanks in advance, to all who can help.

------------//------------------------//------------------------//------------------------//------------------------//------------

Personal for those who have the same problem I managed to have a satisfactory solution;

Logic = I make a decreasing ordination by converting the ORDER column into a whole boldness of the CINT (ORDER) command, and I also bring in Integer

Follows;

sql = "SELECT CInt(ORDEM) FROM [Planilha3$A1:B6] WHERE [LINPRD] = 'A' ORDER BY CInt(ORDEM) DESC"

The routine went this way;

Sub Pesquisa_SQL_CAST ()
 
Dim DB As New ADODB.Connection 'Variável de Conexão ou Caminho para o banco de dados
Dim RS As New ADODB.Recordset 'Variável para abrir conexão
Dim sql As String
Dim Final_loop As Integer

With DB
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Properties("Data Source") = ThisWorkbook.FullName
        .Properties("Extended Properties") = "Excel 12.0 Xml; HDR=YES; IMEX=1"
        .Mode = adModeRead
        .CursorLocation = adUseClient
        .Open
End With

sql = "SELECT CInt(ORDEM) FROM [Planilha3$A1:B6] WHERE [LINPRD] = 'A' ORDER BY CInt(ORDEM) DESC"

RS.Open sql, DB

Final_loop = RS(0)

End Sub

I could not use the CAST had to adapt using the CINT, (which may be CDBL or any other)

If anyone has a better or more practical solution, please share.

I still haven’t been able to reference a formatted named table or just a named region in FROM, who can help would be great.

Thank you very much.