Retrieving in a textbox, via VBA, a value calculated by an aggregate function

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

Fig. 2

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).

Source: text adapted from StackOverflow.com (1), (2).

Author: Ovidiu.S

I am quite passionate about this professional area as if I know something - no matter how little - I want to share it with others.

Leave a Reply

Your email address will not be published. Required fields are marked *