SQL code in VBA - problem in WHERE

[mbertuol]

Hello,

I need to have a query written in VBA and I am having problems with the WHERE paramater:

Private Sub Comando38_Click()

Dim strSQL As String, strOrder As String, strWhere As String

'This is the original SQL query
'SELECT Projetos.[Número], Projetos.[Nome], Projetos.Deadline, Projetos.DTPSimNao, Projetos.Sent
'FROM Projetos
'WHERE (((Projetos.[Nome do cliente]) <> "Terra") And ((Projetos.Sent) Is Null))
'ORDER BY Projetos.Deadline;

strSQL = "SELECT Projetos.[Número], Projetos.[Nome], Projetos.Deadline, Projetos.DTPSimNao, Projetos.Sent " & "FROM Projetos"
' This is where the problem lies: 
strWhere = "WHERE"
strOrder = "ORDER BY Projetos.[Nome] ASC;"

strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

Me.Lista915.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub

Can somebody help me?

Thank you

 

[Mile-O]

"WHERE (((Projetos.[Nome do cliente]) <> ""Terra"") And ((Projetos.Sent) Is Null)) "

Why don't you just make the query and save your database from becoming huge unnecessarily rather than opt for this inefficient SQL in VBA method?

 

[SQL_Hell]

here is your problem:

strWhere = "WHERE"
strOrder = "ORDER BY Projetos.[Nome] ASC;"

strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

change this to:

strWhere = "WHERE"
strOrder = "ORDER BY Projetos.[Nome] ASC;"

strWhere = strWhere + "Mid(strWhere, 1, Len(strWhere) - 5)"

I also agree with mbertuol's suggestion, it would be better in a Query as you are not required to loop through a record set

 

[mbertuol]

Thank you both for your suggestions. It did work.

I have to code in VBA to offer the users different forms to order the listbox. The code I have sent is just one of the several commands.

Thank you!