entering a calculated feildn from a query into a form.

  • Thread starter Thread starter jon
  • Start date Start date
J

jon

Hi.
I have a form where the user will select a date and a time and when the user
clicks the button (this is were I am having fun ) it runs a query and places
a calculated field into to form so the user and do further work with it.

I am having trouble coding the button.

I have a form SRS_Input which has 2 fields in it SRSDate & SRSTime
The query uses these 2 field to find a record of temperature with 4 fields
in and calculates the average of the 4 field, called QurAvgTemp.

Thanks

Jon
 
Tom

The code I have in is not right as I am unsure how to run the query and then
pull the value out of query and put it in the form.
As this code opens the query ( the figures are correct in the query) so the
user can see it which I do not want.
The query is called SelectDate
the result field is qurAvgTemp

the field on the form is called AvgTemp

The code I have is

Private Sub butAvg_Click()
On Error GoTo Err_butAvg_Click

Dim stDocName As String

stDocName = "SelectDate"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Forms!SRS_Input!AvgTemp.Visible = True

AvgTemp = ' *** I don't know what code to put here to pull the
result from the query ****

Exit_butAvg_Click:
Exit Sub

Err_butAvg_Click:
MsgBox Err.Description
Resume Exit_butAvg_Click

End Sub

Thanks

Jon
 
Hi Jon,
AvgTemp = ' *** I don't know what code to put here to pull the
result from the query ****
Click to expand...

You need to approach this in a different manner. You can use the domain
aggregrate function DAvg to calculate your average in the domain, or you open
a recordset in VBA code. Here is more on using domain aggregate functions
(they all work in a similar manner):

DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm

So, an example would look something like this:

Option Compare Database
Option Explicit

Private Sub butAvg_Click()
On Error GoTo ProcError

AvgTemp = DAvg("FieldName", "TableOrQueryName", "Criteria")

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure butAvg_Click..."
Resume ExitProc
End Sub



Here is a method that uses a DAO recordset:

Option Compare Database
Option Explicit

Private Sub butAvg_Click()
On Error GoTo ProcError

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("SelectDate", dbOpenSnapshot)

If rs.RecordCount > 0 Then
AvgTemp = rs("AvgTemp")
End If

ExitProc:
'Cleanup
On Error Resume Next
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure butAvg_Click..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Although you mentioned completely removing two versions of Access, and then
re-installing Access 2002, you didn't indicate how you went doing this. Here
is a KB article for Office XP (sorry, I don't see a similar article for
Office 2003):

How to manually remove the Microsoft Office XP Developer
installation from your computer
http://support.microsoft.com/kb/827428

After following all of the steps in this article, try re-installing Office
XP (or Access 2002). After the installation, make sure to install the latest
service pack (SP-3) along with the latest service pack for the JET database
engine (SP-8). Use this KB article as a guide:

How to keep a Jet 4.0 database in top working condition
http://support.microsoft.com/?id=303528


Good Luck!

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Hi Tom
Still not got there.
I have tried the Davg and dlookup but I keep getting an error can not find
field "Temp" or "selectDate" when I am looking at a query
here is a list of the some of the attempts I have tried

' Me![AvgTemp] = DLookup([SelectDate]![AvgTemp], [SelectDate],
[SelectDate]![TempDate] = Me![TempDateCombo] And [SelectDate]![TempTime] =
Me![TempTimeCombo])

'Me![Text34] = DLookup([temp]![CMM_06], "[temp]")

' AvgTemp = DLookup([selectdate]![AvgTemp], [selectdate])
'Me![AvgTemp] = DLookup([Temp]![CMM_06] + [Temp]![CMM_07] +
[Temp]![CMM_08] + [Temp]![CMM_10], [Temp], [SelectDate]![TempDate] =
Me![TempDateCombo] And [SelectDate]![TempTime] = Me![TempTimeCombo])

'AvgTemp = DLookup([Temp]![CMM_06] + [Temp]![CMM_07] + [Temp]![CMM_08] +
[Temp]![CMM_10] / 4, [Temp], Me![TempDateCombo] = [Temp]![TempDate] &
Me![TempTimeCombo] = [Temp]![TempTime])

I have tried just to bring in a fields to a combo box from a table or a
query bit still get the error from the domain argument
the reason I wanted the average is I have 4 fields in a table and need the
average of those fields at a particular time

Any more advise please.
I also do not understand DAO recordset (more reading for me)

Thanks

Jon
 
Done

used
AvgTemp = DLookup("[AvgTemp]", "SelectDate")
to look in the query.
Thanks Tom




jon said:
Hi Tom
Still not got there.
I have tried the Davg and dlookup but I keep getting an error can not find
field "Temp" or "selectDate" when I am looking at a query
here is a list of the some of the attempts I have tried

' Me![AvgTemp] = DLookup([SelectDate]![AvgTemp], [SelectDate],
[SelectDate]![TempDate] = Me![TempDateCombo] And [SelectDate]![TempTime] =
Me![TempTimeCombo])

'Me![Text34] = DLookup([temp]![CMM_06], "[temp]")

' AvgTemp = DLookup([selectdate]![AvgTemp], [selectdate])
'Me![AvgTemp] = DLookup([Temp]![CMM_06] + [Temp]![CMM_07] +
[Temp]![CMM_08] + [Temp]![CMM_10], [Temp], [SelectDate]![TempDate] =
Me![TempDateCombo] And [SelectDate]![TempTime] = Me![TempTimeCombo])

'AvgTemp = DLookup([Temp]![CMM_06] + [Temp]![CMM_07] + [Temp]![CMM_08] +
[Temp]![CMM_10] / 4, [Temp], Me![TempDateCombo] = [Temp]![TempDate] &
Me![TempTimeCombo] = [Temp]![TempTime])

I have tried just to bring in a fields to a combo box from a table or a
query bit still get the error from the domain argument
the reason I wanted the average is I have 4 fields in a table and need the
average of those fields at a particular time

Any more advise please.
I also do not understand DAO recordset (more reading for me)

Thanks

Jon

Tom Wickerath said:
Hi Jon,


You need to approach this in a different manner. You can use the domain
aggregrate function DAvg to calculate your average in the domain, or you
open
a recordset in VBA code. Here is more on using domain aggregate functions
(they all work in a similar manner):

DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm

So, an example would look something like this:

Option Compare Database
Option Explicit

Private Sub butAvg_Click()
On Error GoTo ProcError

AvgTemp = DAvg("FieldName", "TableOrQueryName", "Criteria")

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure butAvg_Click..."
Resume ExitProc
End Sub



Here is a method that uses a DAO recordset:

Option Compare Database
Option Explicit

Private Sub butAvg_Click()
On Error GoTo ProcError

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("SelectDate", dbOpenSnapshot)

If rs.RecordCount > 0 Then
AvgTemp = rs("AvgTemp")
End If

ExitProc:
'Cleanup
On Error Resume Next
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure butAvg_Click..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
Click to expand...
Click to expand...
 
Hi Jon,

I see in your follow-up post that you found a solution, so that's good. If
you can post the SQL statement for your query, there may be a way of
calculating this average directly, by just referencing the table instead of
having it dependent on another object (query). To view the SQL statement,
open your query in design view. Then click on View > SQL View. If you want to
pursue this option, copy the SQL statement and post it to a reply. If it is
fairly simple, we can likely eliminate the need for the query.
I also do not understand DAO recordset
Click to expand...

I realize that you may not understand the DAO recordset method it, but did
you try?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

jon said:
Hi Tom
Still not got there.
I have tried the Davg and dlookup but I keep getting an error can not find
field "Temp" or "selectDate" when I am looking at a query
here is a list of the some of the attempts I have tried

' Me![AvgTemp] = DLookup([SelectDate]![AvgTemp], [SelectDate],
[SelectDate]![TempDate] = Me![TempDateCombo] And [SelectDate]![TempTime] =
Me![TempTimeCombo])

'Me![Text34] = DLookup([temp]![CMM_06], "[temp]")

' AvgTemp = DLookup([selectdate]![AvgTemp], [selectdate])
'Me![AvgTemp] = DLookup([Temp]![CMM_06] + [Temp]![CMM_07] +
[Temp]![CMM_08] + [Temp]![CMM_10], [Temp], [SelectDate]![TempDate] =
Me![TempDateCombo] And [SelectDate]![TempTime] = Me![TempTimeCombo])

'AvgTemp = DLookup([Temp]![CMM_06] + [Temp]![CMM_07] + [Temp]![CMM_08] +
[Temp]![CMM_10] / 4, [Temp], Me![TempDateCombo] = [Temp]![TempDate] &
Me![TempTimeCombo] = [Temp]![TempTime])

I have tried just to bring in a fields to a combo box from a table or a
query bit still get the error from the domain argument
the reason I wanted the average is I have 4 fields in a table and need the
average of those fields at a particular time

Any more advise please.
I also do not understand DAO recordset (more reading for me)

Thanks

Jon
Click to expand...
 
Thanks Tom
I read the DAO and it did not sink in but I will try again (it does not help
that at the moment I am working a night Shift)

My SQL statement is
SELECT Temp.Count_temp, Temp.TempDate, Temp.TempTime, Temp.CMM_06,
Temp.CMM_07, Temp.CMM_08, Temp.CMM_10,
([CMM_06]+[CMM_07]+[CMM_08]+[CMM_10])/4 AS AvgTemp
FROM Temp
WHERE (((Temp.TempDate)=[forms].[SRS_Input].[TempDateCombo]) AND
((Temp.TempTime)=[forms].[SRS_Input].[TempTimeCombo]));


ATB

Jon


Tom Wickerath said:
Hi Jon,

I see in your follow-up post that you found a solution, so that's good. If
you can post the SQL statement for your query, there may be a way of
calculating this average directly, by just referencing the table instead
of
having it dependent on another object (query). To view the SQL statement,
open your query in design view. Then click on View > SQL View. If you want
to
pursue this option, copy the SQL statement and post it to a reply. If it
is
fairly simple, we can likely eliminate the need for the query.
I also do not understand DAO recordset
Click to expand...

I realize that you may not understand the DAO recordset method it, but did
you try?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

jon said:
Hi Tom
Still not got there.
I have tried the Davg and dlookup but I keep getting an error can not
find
field "Temp" or "selectDate" when I am looking at a query
here is a list of the some of the attempts I have tried

' Me![AvgTemp] = DLookup([SelectDate]![AvgTemp], [SelectDate],
[SelectDate]![TempDate] = Me![TempDateCombo] And [SelectDate]![TempTime]
=
Me![TempTimeCombo])

'Me![Text34] = DLookup([temp]![CMM_06], "[temp]")

' AvgTemp = DLookup([selectdate]![AvgTemp], [selectdate])
'Me![AvgTemp] = DLookup([Temp]![CMM_06] + [Temp]![CMM_07] +
[Temp]![CMM_08] + [Temp]![CMM_10], [Temp], [SelectDate]![TempDate] =
Me![TempDateCombo] And [SelectDate]![TempTime] = Me![TempTimeCombo])

'AvgTemp = DLookup([Temp]![CMM_06] + [Temp]![CMM_07] + [Temp]![CMM_08]
+
[Temp]![CMM_10] / 4, [Temp], Me![TempDateCombo] = [Temp]![TempDate] &
Me![TempTimeCombo] = [Temp]![TempTime])

I have tried just to bring in a fields to a combo box from a table or a
query bit still get the error from the domain argument
the reason I wanted the average is I have 4 fields in a table and need
the
average of those fields at a particular time

Any more advise please.
I also do not understand DAO recordset (more reading for me)

Thanks

Jon
Click to expand...
Click to expand...
 
You must log in or register to reply here.

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

Similar Threads

Calculated field in a table 6
How do I calculate and store results? 3
Calculating averages on a form 3
Form Calculations 2
why is calculation is wrong 4
Opening form and going to a specific field in the middle of the from 1
Calculating a person's age 2
Access Field won't show as currency in the form, shows just a number (but works fine in the query) 1

Back
Top