SQL COUNT request abd VBA ACCESS97

  • Thread starter Thread starter Bataille Jean-Paul
  • Start date Start date
B

Bataille Jean-Paul

Hi !

I want to use a SQL COUNT request in a VBA code but i don't succeed why and
how can go my way to success ?
I work on ACCESS 97 but i'm interested in the ACCESS2002 too.

Here under is my code (ACCESS 97)

sub TST ()

Dim strSQL As String
Dim RST As Recordset
Dim Val As Long

'THE SQL REQUEST
strSQL = "SELECT Count(Clé_Contrat) AS Nbr " & _
"FROM R_Contrat_Fluide_finale " & _
"WHERE (((R_Contrat_Fluide_finale.Clé2_SAP)=109));"

Set Bdd = CurrentDb
Set RST = CurrentDb.OpenRecordset(strSQL)
Val = RST(0)
MsgBox (Val)

End Sub


Thanks for your help,

And HAPPY NEW YEAR

JPB
 
The syntax looks correct, although you're setting a variable Bdd which you
haven't declared. (Since you're not using Bdd anywhere else in your code,
though, you can simply leave out that line)

What problem are you having using that code? Do you get an error message? If
so, what's the error? Does the code run, but you don't get the answer you
expect? Does smoke pour out of the diskette drive? It's very difficult to
help without knowing the problem!
 
Hi Jean-Paul

I notice that you are using accent aigu in your SQL statement. Some years
back I did have problems with an application... and after wasting a LOT of time
tracking down the possible cause(s) turned out that getting rid of the extended
characters in the application everything ran fine after that.

Best regards

Maurice St-Cyr
 
just quick thought. Is the ID in your where clause a number field? If
not, then you need some kind of quoting around the number in the where
clause.
"...SAP='109'" or
"..._SAP=" & chr$(34) & "109" & chr$(34).

I have come across many databases that use numeric ID's in a text field
that require this.
 
Back
Top