Display average value from query in label

[Cosmicnoodle]

I have a form that I want to use in order to work out a global figure for average travel time for my engineers (data coming from another database).

I have a form with two text boxes, which allow the user to define a date range. This links into my query which works fine and returns all records from that date range.

Now comes the troubling part!

On the click of a command button I want the label to change and display the average for my work time column form my query (which by the way is in decimal format).

Can anyone help me? I have tried searcing but to no avail

 

[jaydwest]

Try this on the Click event

Me![LabelFieldName].Caption = "THE AVERAGE IS " & Format(X/Y, "#,##0.00")

 

[Cosmicnoodle]

I do not have the numbers, I need some code that will run a query (that I have working fine) and average all the values in a column returned in that query.

Basically, I have a table from a call handling database that has all of my engineers visits logged. I have a query that simply selects a date range based on user input into two textboxes. I have a form that contains nothing but two text boxes (date from and date to), a label (that I want to show the overall value) and a command button.

As far as I can tell I need to create some code that does the following after clicking a command button on the form:
1: Run query based on user input
2: Average the figues in the travel_time column returned from query
3: Change the label to display the average instead of being blank.


Simple! Nearly....

 

[jaydwest]

I don't know how much experience you have writing code but I will give you a high level explanation. If you need more, let me know.

1. Create an aggregate query that calcuates the average of the values you select based on the date range. Suggest a parameter Query. It runs faster and produces less bloat.

2. create a recordset based on the aggregate query. It should result in one record with a field something like avgOfValues

3. The assign this value to the label caption

Me![LabelControl].caption = Format(myRst!AvgOfValues, "#,##0.00")

 

[Cosmicnoodle]

this is the 1st time i have tried to use an aggregate query and its proving a nightmare!

I have my query set up and running smoothly but thats as far as i can get.
Any change of a BASIC demo? just one or two records with 1 column or something - just to give me the just aas i am lost!

Cheers

 

[Cosmicnoodle]

using the following SQL statement I have got the average for all values in the table
SELECT Avg([Travel_Time]) AS AvgTravel_Time
FROM tbl_Travel_Time;

Now how do I apply this to my query below?

SELECT tbl_Travel_Time.AutoID, tbl_Travel_Time.Travel_Time, tbl_Travel_Time.Date_Of_Visit
FROM tbl_Travel_Time
WHERE (((tbl_Travel_Time.Date_Of_Visit)>=[Forms]![frm_Average_Travel_Time]![tbo_datefrom] And (tbl_Travel_Time.Date_Of_Visit)<[Forms]![frm_Average_Travel_Time]![tbo_DateTo]));


And finally, how do I get the avg value to show in a form?

 

[jaydwest]

strSQL = "SELECT Avg([Travel_Time]) AS AvgTravel_Time
FROM tbl_Travel_Time
WHERE (tbl_Travel_Time.Date_Of_Visit BETWEEN [Forms]![frm_Average_Travel_Time]![tbo_datefrom] AND Forms]![frm_Average_Travel_Time]![tbo_DateTo]);"


--------------
Dim rstAvg as DAO.Recordset

set rstAvg = currentdb().openrecordset(strSQL)

me![LabelControlName].caption = NZ(Format(rstAvg!AvgTravel_Time, "#,##0.00"), null)

 

[Cosmicnoodle]

The SQL Statement works perfectly, but the caption change code gives me the following errord when placed in the cmd_click() private sub (is this the correct place for the code?)

I get the following error "cannot find the database table or query '#'"

And visual basic highlights the following code:

Private Sub Command15_Click()

Dim rstAvg As DAO.Recordset

Set rstAvg = CurrentDb().OpenRecordset(strSQL)

Me![tbo_avg].Caption = Nz(Format(rstAvg!AvgTravel_Time, "#,##0.00"), Null)

End Sub




I know this is probably me being really dumb, but I just can't seem to get this to work...

 

[RuralGuy]

I have not followed all of this thread but for your code to work you need to Dim and set "strSQL" inside your SubRoutine:

Private Sub Command15_Click()

Dim rstAvg As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Avg([Travel_Time]) AS AvgTravel_Time " & _
         "FROM tbl_Travel_Time " & _
         "WHERE (tbl_Travel_Time.Date_Of_Visit " & _
         "BETWEEN [Forms]![frm_Average_Travel_Time]![tbo_datefrom] " & _ 
         "AND Forms]![frm_Average_Travel_Time]![tbo_DateTo]);"

Set rstAvg = CurrentDb().OpenRecordset(strSQL)

Me![tbo_avg].Caption = Nz(Format(rstAvg!AvgTravel_Time, "#,##0.00"), Null)

End Sub

 

[Cosmicnoodle]

I think I must be really dumb... that code looks correct to me, with all syntax in place and proper but it still does not work.
I get the error: "Syntax error in query expression: (tbl_Travel_Time.Date_Of_Visit " & _
"BETWEEN [Forms]![frm_Average_Travel_Time]![tbo_datefrom] " & _
"AND Forms]![frm_Average_Travel_Time]![tbo_DateTo])"

Then when I debug, the foloowing is highlighted...

Set rstAvg = CurrentDb().OpenRecordset(strSQL)

Does this mean anything to anyone!?

 

[RuralGuy]

My bad! I left off the leading "["

strSQL = "SELECT Avg([Travel_Time]) AS AvgTravel_Time " & _
         "FROM tbl_Travel_Time " & _
         "WHERE (tbl_Travel_Time.Date_Of_Visit " & _
         "BETWEEN [Forms]![frm_Average_Travel_Time]![tbo_datefrom] " & _ 
         [COLOR=Red]"AND [b][[/b]Forms]![frm_Average_Travel_Time]![tbo_DateTo]);"[/COLOR]

 

[Cosmicnoodle]

Guess what... didnt work!

New error = " 'Run time error 3061' Too few parameters, expected 2"

highlighting : Set rstAvg = CurrentDb().OpenRecordset(strSQL) in my code


Im going to keep trying to tweak it but i am not exactly a pro so may fail.... help needed!

 

[RuralGuy]

The code posted only works if form frm_Average_Travel_Time is open.

 

[Cosmicnoodle]

doesnt seem to make a difference?

Still get the same errors...

 

[RuralGuy]

Earlier you said:

The SQL Statement works perfectly...

I guess we will have to look deeper. Let's try this:

strSQL = "SELECT Avg([Travel_Time]) AS AvgTravel_Time " & _
         "FROM tbl_Travel_Time " & _
         "WHERE (tbl_Travel_Time.Date_Of_Visit " & _
         "BETWEEN #" & [Forms]![frm_Average_Travel_Time]![tbo_datefrom] & "#" & _ 
         "AND #" & [Forms]![frm_Average_Travel_Time]![tbo_DateTo]) & "#;"

 

[Cosmicnoodle]

that brings up a syntax error, simply highlighting the command15 click function

 

[RuralGuy]

My bad. One more time:

strSQL = "SELECT Avg([Travel_Time]) AS AvgTravel_Time " & _
         "FROM tbl_Travel_Time " & _
         "WHERE tbl_Travel_Time.Date_Of_Visit " & _
         "BETWEEN #" & [Forms]![frm_Average_Travel_Time]![tbo_datefrom] & "# " & _
         "AND #" & [Forms]![frm_Average_Travel_Time]![tbo_DateTo] & "#;"

 

[Cosmicnoodle]

YAY!!!
It worked!!!!


Thanks to all those that have helped - i realise its been a pain in the arse, but its all worth it!

 

[RuralGuy]

Glad I could help!

 

[Cosmicnoodle]

Right,
as perfect as this is... I need to display the average travel time in hours/mins rather than as a decimal result.
How do I convert the code below to display a result in hh:mm format?


Private Sub Cmd_GetAverage_Click()

Dim rstAvg As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Avg([FSR_Travel_Time]) AS AvgTravel_Time " & _
"FROM SCFSR " & _
"WHERE SCFSR.FSR_Start_Date " & _
"BETWEEN #" & [Forms]![frm_Average_Travel_Time]![tbo_datefrom] & "# " & _
"AND #" & [Forms]![frm_Average_Travel_Time]![tbo_DateTo] & "#;"

Set rstAvg = CurrentDb().OpenRecordset(strSQL)

Me![lbl_Avg].Caption = Nz(Format(rstAvg!AvgTravel_Time, "#,##0.00"), Null)

End Sub



Also just as a side project, is it poisble to display a status bar message to show that it is calculating? On large sets of data, it takes approx 10 secs to load the avg value into my label, I want to alert the user to be patient!