By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,521 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,521 IT Pros & Developers. It's quick & easy.

selection multi criteria access and vba

P: 2
hey i need ur help please it's my first time working with vba and access and i want to program an access form for multi search i have the type of contract as a combo box if i select type of contrat as "credit IMMO" it displays automatically an other zone of txt,and i need a code that according to the type of contract selected and the other conditions the result of the query have to be inserted in the table destined for this type of contract; i tried this peace of code for the first type of contract but it doesn't work :/ and i'm really sorry for my English :)



Expand|Select|Wrap|Line Numbers
  1. Private Sub btselect_Click()
  2. Dim sql As String
  3. Select Case (Me.cmbrechtype)
  4. Case "crédit IMMO"
  5. sql = "Insert Into [Prêts IMMO Part] Values (Select Num_contrat, Num_personne, Num_instruction, Nom_client, catégorie_client,region, groupe, agence, date_debut_contrat, montant_prêt, objet_prêt, résidence_bien,durée_résiduelle, montant_CRD, Encours, Date_mer0, note_personne, moteur_notation From contrat Where type_contrat = 'crédit immo');"
  6. If (Me.txtdatesup <> "") Then
  7. sql = sql & " AND Date_debut_contrat < #" & Me.txtdatesup & "#"
  8. End If
  9.  
  10. If (Me.txtdateinf <> "") Then
  11. sql = sql & " AND Date_debut_contrat > #" & Me.txtdateinf & "#"
  12. End If
  13.  
  14. If (Me.txtnotesup <> "") Then
  15. sql = sql & " AND Note_personne < #" & Me.txtnotesup & "#"
  16. End If
  17.  
  18. If (Me.txtnoteinf <> "") Then
  19. sql = sql & " AND Note_personne > #" & Me.txtnoteinf & "#"
  20. End If
  21.  
  22. If (Me.txtmtsup <> "") Then
  23. sql = sql & " AND Montant_prêt > #" & Me.txtdatesup & "#"
  24. End If
  25.  
  26. If (Me.txtmtinf <> "") Then
  27. sql = sql & " AND Montant_prêt < #" & Me.txtdateinf & "#"
  28. End If
  29. sql = sql & ";"
  30. DoCmd.RunSQL sql
  31. End Select
  32. End Sub
Jun 10 '15 #1
Share this Question
Share on Google+
3 Replies


Seth Schrock
Expert 2.5K+
P: 2,951
Please use CODE tags when posting code per Site Rules.

When you set the INSERT part of the SQL variable, you have a semi-colon at the end of that string and then you add criteria later with all your If-Then statements. The semi-colon marks the end of the query in SQL, so having text after that makes the query fail. Remove that semi-colon and it looks like it should work.
Jun 10 '15 #2

P: 2
thank you i did what u said but it doesn't work :/ this is what i did

Expand|Select|Wrap|Line Numbers
  1. Private Sub btselect_Click()
  2. Dim sql As String
  3. Select Case (Me.cmbrechtype)
  4. Case "crédit IMMO"
  5. sql = "Insert Into [Prêts IMMO Part] Values (Select Num_contrat, Num_personne, Num_instruction, Nom_client, catégorie_client,region, groupe, agence, date_debut_contrat, montant_prêt, objet_prêt, résidence_bien,durée_résiduelle, montant_CRD, Encours, Date_mer0, note_personne, moteur_notation From contrat );"
  6. sql = sql & " Where type_contrat='crédit IMMO'"
  7.  
  8. If (Me.txtdatesup <> "") Then
  9. sql = sql & " AND Date_debut_contrat < #" & Me.txtdatesup & "#"
  10. End If
  11.  
  12. If (Me.txtdateinf <> "") Then
  13. sql = sql & " AND Date_debut_contrat > #" & Me.txtdateinf & "#"
  14. End If
  15.  
  16. If (Me.txtnotesup <> "") Then
  17. sql = sql & " AND Note_personne < #" & Me.txtnotesup & "#"
  18. End If
  19.  
  20. If (Me.txtnoteinf <> "") Then
  21. sql = sql & " AND Note_personne > #" & Me.txtnoteinf & "#"
  22. End If
  23.  
  24. If (Me.txtmtsup <> "") Then
  25. sql = sql & " AND Montant_prêt > #" & Me.txtdatesup & "#"
  26. End If
  27.  
  28. If (Me.txtmtinf <> "") Then
  29. sql = sql & " AND Montant_prêt < #" & Me.txtdateinf & "#"
  30. End If
  31. sql = sql & ";"
  32. DoCmd.RunSQL sql
  33. sql = sql & " AND Date_debut_contrat < #" & Me.txtdatesup & "#"
  34.  
  35.  
  36. End Select
  37. End Sub
Jun 10 '15 #3

Seth Schrock
Expert 2.5K+
P: 2,951
You didn't do what I said because the semicolon is still there. You have (broken up for ease of reading):
Expand|Select|Wrap|Line Numbers
  1. sql = "Insert Into [Prêts IMMO Part] 
  2. Values (Select Num_contrat, Num_personne, Num_instruction, 
  3. Nom_client, catégorie_client, region, groupe, agence, 
  4. date_debut_contrat, montant_prêt, objet_prêt, résidence_bien,
  5. durée_résiduelle, montant_CRD, Encours, Date_mer0, 
  6. note_personne, moteur_notation From contrat );"
At the end of this is a semicolon. This needs removed.

I also realized that you don't need the word VALUES when entering from a SELECT query. Also, since you aren't specifying column names, then you need to make sure that you have the proper number of columns being entered into [Prets IMMO Part]. So at this point your query should be
Expand|Select|Wrap|Line Numbers
  1. sql = "Insert Into [Prêts IMMO Part] 
  2. Select Num_contrat, Num_personne, Num_instruction, 
  3. Nom_client, catégorie_client, region, groupe, agence, 
  4. date_debut_contrat, montant_prêt, objet_prêt, résidence_bien,
  5. durée_résiduelle, montant_CRD, Encours, Date_mer0, 
  6. note_personne, moteur_notation From contrat
Jun 10 '15 #4

Post your reply

Sign in to post your reply or Sign up for a free account.