SQL statement help needed for two conditions.

[vishal120]

Hi ,

I actually need help on the below sql statement. I am actually downloading data from access database to excel workbook based on two conditions. I am using the select statement where two conditions shall meet.

This is the part of the code where i am having compile error syntax error :

Set rs = CreateObject("ADODB.Recordset")
        
       rs.Open = "SELECT Sample_Auto_ref , Ref_Client, Ref_Karina, Saison, Marketing_Manager, Merchandiser, Client, Depart, Theme, Desc, Type_Echantillion,Taille,Qty,Keep_KI,Type_Lavage,Colori_Gmt_Dyed,Valeur_Ajouter,Date_request_Merc,Date_Livraison FROM Tbl_Sample_details order by Date_request_Merc desc WHERE Sample_Auto_ref = '" & Range("f1").Value & " & Merchandiser='" & Range("f3").Value ", cn, , , adCmdText

many thanks for the kind help in advance

 

[Joe4]

The WHERE clause goes before the ORDER BY clause in a SQL statement.

 

[V_Malkoti]

Try changing your SQL query as:

"SELECT Sample_Auto_ref , Ref_Client, Ref_Karina, Saison, Marketing_Manager, Merchandiser, Client, Depart, Theme, Desc, Type_Echantillion,Taille,Qty,Keep_KI,Type_Lavage,Colori_Gmt_Dyed,Valeur_Ajouter,Date_request_Merc,Date_Livraison FROM Tbl_Sample_details WHERE Sample_Auto_ref = '" & Range("f1").Value & "' and Merchandiser='" & Range("f3").Value "' order by Date_request_Merc desc"

You had ORDER BY before WHERE (as Joe mentioned), & instead of AND, and missing single quotes around string values.

 

[vishal120]

Hi ,

thanks for the quick reply, I have try as advise but is still having the errors as comile errors :

the code is as such:

rs.Open = "SELECT Sample_Auto_ref , Ref_Client, Ref_Karina, Saison, Marketing_Manager, Merchandiser, Client, Depart, Theme, Desc, Type_Echantillion,Taille,Qty,Keep_KI,Type_Lavage,Colori_Gmt_Dyed,Valeur_Ajouter,Date_request_Merc,Date_Livraison FROM Tbl_Sample_details WHERE Sample_Auto_ref = '" & Range("f1").Value & "' and Merchandiser='" & Range("f3").Value & "' order by Date_request_Merc desc", cn, , , adCmdText

thanks for advise

 

[Joe4]

You can also write the SQL code out to a message box to see if the code you are building looks as expected. i.e.

Dim mySQL as String
[COLOR=#333333]mySQL = "SELECT Sample_Auto_ref , Ref_Client, Ref_Karina, Saison, Marketing_Manager, Merchandiser, Client, Depart, Theme, Desc, Type_Echantillion,Taille,Qty,Keep_KI,Type_Lavage,Colori_Gmt_Dyed,Valeur_Ajouter,Date_request_Merc,Date_Livraison FROM Tbl_Sample_details WHERE Sample_Auto_ref = '" & Range("f1").Value & "' and Merchandiser='" & Range("f3").Value & "' order by Date_request_Merc desc"
MsgBox mySQL[/COLOR]

To verify it is good code, try running exact same code in Access and see if it works.

The way I usually approach this is to go to Access first, and come up with working SQL code. Once you have that, then mirror building that in Excel VBA. You can use MsgBox to see how close your code is to what it needs to look like.

 

[V_Malkoti]

You don't need an equals operator in that statement because SQL is an argument of the Open method. Here is the syntax:

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

Code examples about opening connection and recordset objects here: https://support.microsoft.com/en-us/kb/168336

 

[vishal120]

Hi ,

Thanks a lot for the help. I have been able to make it to work with the below code. Sharing it just in case someone else will be needing.

      ssql1 = "select Sample_Auto_ref,"
     ssql1 = ssql1 & "  Ref_Client,"
     ssql1 = ssql1 & "  Ref_Karina,"
     ssql1 = ssql1 & " saison, "
     ssql1 = ssql1 & " Marketing_Manager, "
     ssql1 = ssql1 & " Merchandiser, "
     ssql1 = ssql1 & " Client, "
     ssql1 = ssql1 & " Depart, "
     ssql1 = ssql1 & " Theme, "
     ssql1 = ssql1 & " Desc, "
     ssql1 = ssql1 & " Type_Echantillion, "
     ssql1 = ssql1 & " Taille, "
     ssql1 = ssql1 & " Qty, "
     ssql1 = ssql1 & " Keep_KI, "
     ssql1 = ssql1 & " Type_Lavage, "
     ssql1 = ssql1 & " Colori_Gmt_Dyed, "
     ssql1 = ssql1 & " Valeur_Ajouter, "
     ssql1 = ssql1 & " Date_request_Merc, "
     ssql1 = ssql1 & " Date_Livraison "
     ssql1 = ssql1 & " FROM tbl_sample_details WHERE"
     ssql1 = ssql1 & " SAISON='" & Range("E1").Value & "'  and Merchandiser='" & Range("G1").Value & "'order by Date_request_Merc desc"

thanks again and good day to all.

By the way how to mark the thread as solved.

 

[Joe4]

By the way how to mark the thread as solved.

You just did. There is nothing more to do.