Assigning result from query to variable

[mcdhappy80]

I have this SELECT query:

SELECT COUNT(tblZaposleni.intZaposleniID) AS BrDirektora
FROM tblPoslovi INNER JOIN tblZaposleni ON tblPoslovi.intPosaoID=tblZaposleni.intPosloviID
WHERE tblPoslovi.txtNazivPosla="Direktor";

The query returns the numeric value. The value range, for what I need, should be zero or one.

How do I assign the query result to a variable that I can use in forms Class Module for further calculations, or to the text box?

Can this be done like this without using a recordset?
If not how should the code with Recordset variant look like?

Thank You.

 

[HiTechCoach]

I would suggest trying to use DLookup() to retrieve the value from the query.

 

[Mr. B]

mcdhappy80,

I know you stated you wanted to try to do this without a recordset, but I just don't see any way you can do that. Especially when you have a Join in your sql statement.

Give this code a try:

'NOTE:
    'the declaration of a DAO recordset requires you to set
    'a reference to the Microsoft DAO 3.6 Object Library
    'to set the reference use: Tools/References ..
Dim rs As DAO.Recordset
Dim bytRetVal As Byte
Dim strSql As String
strSql = "SELECT COUNT(tblZaposleni.intZaposleniID) AS BrDirektora " _
       & "FROM tblPoslovi INNER JOIN tblZaposleni ON " _
       & "tblPoslovi.intPosaoID=tblZaposleni.intPosloviID " _
       & "WHERE tblPoslovi.txtNazivPosla=""Direktor"";"
rs = CurrentDb.OpenRecordset(strSql)
bytRetVal = rs.Fields("BrDirektora").Value
rs.Close
Set rs = Nothing
If bytRetVal = 0 Then
    'do something here
Else
    'do something else here
End If

HTH

 

[WayneRyan]

mcd,

Dim sql As String
Dim rst As DAO.Recordset

sql = "SELECT COUNT(tblZaposleni.intZaposleniID) AS BrDirektora " & _
      "FROM tblPoslovi INNER JOIN tblZaposleni ON " & _      "           "        tblPoslovi.intPosaoID=tblZaposleni.intPosloviID " & _
      "WHERE tblPoslovi.txtNazivPosla = 'Direktor'"

Set rst = CurrentDb.OpenRecordset(sql)
Me.txtYourTextBox = rst!BrDirektora
rst.close
Set rst = Nothing

oops, didn't see the prior response ...

Wayne