If it is necessary to display the result of a query that uses certain functions (SUM, AVERAGE, MIN, MAX, etc.) in a textbox, when you press a button, it can be done relatively easily, by using a Recordset variable.
Private Sub Command98_Click()
Dim rst As DAO.Recordset
Dim rez_sql As String
Dim d_data_cur As Date
Dim s_data_cur As String
d_data_cur = DateValue(Me!txt_data_zilei)
s_data_cur = Format(d_data_cur, "yyyy\/mm\/dd")
rez_sql = "SELECT Sum(DateDiff('n',[ora_act_start],[ora_act_sf])) AS ore_zi " & _
"FROM t_zi_lucru INNER JOIN q_start_act ON t_zi_lucru.zi_id = q_start_act.zi_id " & _
" WHERE zi_lucru =#" & s_data_cur & "#;"
Set rst = CurrentDb.OpenRecordset(rez_sql)
Me.txt_total_lucru.value = rst!ore_zi
rst.Close
Set rst = Nothing
End Sub
In another example, let’s suppose we are interested to display the maximum value of a numeric field. For this, we will use a String type variable (str_max_gr) that will store the query result, then it will be retrieved in Recordset type variable (rst) as follows:
Private Sub cmd_Adaug_gr_Click()
Dim str_max_gr As String
Dim rst As DAO.Recordset
str_max_gr = "SELECT Max(nr_gr) AS max_nr_gr FROM t_gr_ident_gr"
MsgBox str_max_gr
Set rst = CurrentDb.OpenRecordset(str_max_gr)
MsgBox rst!max_nr_gr
Me.testVBA.Value = rst!max_nr_gr
End Sub
As you can see in the figure no 2, the result appears in the textbox (17); for comparison between the two variables (String and Recordset) I inserted two messages to see different result (syntax, respectively, value).
