SQL COUNT request abd VBA ACCESS97

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
 
D

Douglas J. Steele

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!
 
S

StCyrM

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
 
H

Howhiareu

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top