SQL COUNT request in VBA

[Bataille Jean-Paul]

When i wrote DIM BDD as i don't get any proposal.


Thanks for your help
JPB

 

[Douglas J. Steele]

Given how you're trying to use it, it should be declared As Database.
However, you'll only get that as an option after you add the DAO reference,
as outlined in my other response.

If you do continue to have that object, then make the following change:

Set Bdd = CurrentDb
Set RST = CurrentDb.OpenRecordset(strSQL)

should be

Set Bdd = CurrentDb
Set RST = Bdd.OpenRecordset(strSQL)

 

[Bataille Jean-Paul]

Bonjour from FRANCE

With your help, Douglas, i add the MS DAO 3.6 object library, and then I've
found a solution :

the SQL request have some difficulty to understand the content of the fiels
SAP is a string - in fact actually an integer but i want to consider it as a
string- so i inckude the function CStr in order to convince SQL ans VBA :

strSQL = "SELECT Count(Clé_Contrat) AS Nbre " & _
"FROM R_Contrat_Fluid_finale " & _
"WHERE (((R_Contrat_Fluide_finale.Clé2_SAP)= " & CStr(Me!cb_SAP) & "));"



Thanks and enjoyed the New Year.
Jean-Paul B.

 

[Douglas J. Steele]

If the field is text, then using CStr isn't sufficient. You need to enclose
the value in quotes:

strSQL = "SELECT Count(Clé_Contrat) AS Nbre " & _
"FROM R_Contrat_Fluid_finale " & _
"WHERE R_Contrat_Fluide_finale.Clé2_SAP = '" & _
Me!cb_SAP & "'"

(where that's ' " before, and " ' " after)

or

strSQL = "SELECT Count(Clé_Contrat) AS Nbre " & _
"FROM R_Contrat_Fluid_finale " & _
"WHERE R_Contrat_Fluide_finale.Clé2_SAP = " & _
Chr$(34) & Me!cb_SAP & Chr$(34)


BTW, you may want to post your questions to a more appropriate group. This
one is for Macros, and your questions have nothing to do with Macros.