I want to create a SELECT with Max

user_18354311 0
Viewed 58

I created a code for select with max, but I get an error: using ORDER BY DESC, but I would like SELECT() with MAX()

DATA= Field Name CMV= Table Name (ACCESS)

Set banco = New ADODB.Recordset
'DEFININDO INSTRUÇÃO A VARIÁVEL
sql = " SELECT MAX(DATA) FROM CMV"
sql = sql & " WHERE DEPOSITANTE= '" & nDEPOSITANTE & "'"

'CONECTAR AO BANCO DE DADOS
cx.Conectar
    
'EXECUTAR A SQL
On Error Resume Next
banco.Open sql, cx.Conn

nDATA3 = banco.Fields("DATA")
        
'DESCONECTAR PARA LIBERAR MEMÓRIA
cx.Desconectar
vba excel sql
1 Answers

I think your error is attempting to access the column MAX(DATA) as Fields("Data"), you have to give it an alias like:

SELECT MAX(DATA) as MAX_DATA
...

and then refer to it by this name

 nDATA3 = banco.Fields("MAX_DATA")

Try this, but, honestly, your error might be somewhere else and hidden because 1. you are using ON ERROR RESUME NEXT, which would hide the error, and 2. you are not telling us where it fails and what the error is.

user_116950490
Related