I need to get an average of 2 variables eg.
target low eg 4
target high eg 8
target (to have the avg) result 6
I have been doing the following but dont work ..
=Avg([Target Low].[Target High]) result #error
=Avg([Target Low]+[Target High]) result 12
=Avg([Target Low]&[Target High]) result 48
=Avg([Target Low]:[Target High]) will not work
I wanna is creating a table like this (Students Degree)
DepartDegree2Degree1Name ID
Then I have to create a Form depend on this table and have to
Add a (command button) to update the (average) if was 49 or 48 to 50
I have uploaded it
Is it possible to access the values on one form and use them on another? For example, I have heard that the code might look something like this:
Form1.Text1.Text
When I've tried this, it doesn't work. Someone mentioned something about declaring them as Public, but they're not variables, they're actual record values on another form. Any suggestions?
Thanks!
Mike
I have been trying (unsuccessfully) to get an unbound text box on a form to show the average of four other text boxes on that form. A bit of searching led me to the code below as a possible solution, but it isn't working.
Code:
Dim nbrFieldsUsed As Integer
Dim nbrTotal As Double
nbrFieldsUsed = 0
nbrTotal = 0
If Not IsNull(Me!txtEWT_STD_1) Then
nbrFieldsUsed = nbrFieldsUsed + 1
nbrTotal = nbrTotal + Me!txtEWT_STD_1
End If
[code]....
My thinking was to have this code on the 'After Update' event (or maybe On Change?) of each of the four text boxes which would then give a running average in the box that shows the average?
I am using the following function:
=Avg(IIf([Final]="TST-TST OK RTN" Or [Final]="NFF-TST & NFF" Or [Final]="RTN-RETURN" Or [Final]="SCL-SCR LOCALLY" Or [Final]="SCR-SCR RETURN" Or [Final]="BER-BER RTN CST",Nz([Cost Of New],0),0))
I have a subform with a total of 18 records. 2 records meet the criteria above. This function is dividing by 18 (total number of records) instead of 2 to get the average. How can I get it to divide by the right number of records?
Before I go any further w/this current application, I want to make sure this will work.
I have an access database w/a few forms that will sit on a shared drive on a network. Each user will also have their own username and password because each user will have a certain level of access to what they are allowed to see. The problem I am facing is that if I put the users ID into the global variables module, and two users log on at the same time, then access seems to somehow use both of the IDs when running queries. This makes sense that all uses can see it, since it's Global... but I need a way for simultaneous users to have a persistant unique id so I can query data that's only meant for them. I found this example of code that might remedy the situation by passing variables between the forms.
Call the code below in frmOne to pass the variable.
DoCmd.OpenForm "frmTwo", acNormal, , , , acWindowNormal, "Count=2"
Call the code below in frmTwo to get the variable.
Dim i as Integer
i = CInt(GetTagFromArg(Me.OpenArgs, "Count" ))
Will I end up w/the same result? Will all users be able to see this variable as well?
I have a simple date stamp that works great in a private sub within a form. (error handling removed for clarity)
Code:
Private Sub btnDateStamp_Click()
' UserInit is global variable
Me!Notes.SetFocus
Me!Notes = Chr$(13) & Date & " - " & Time() & " - " & UserInit & _
" -" & vbCrLf & Me!Notes
[code]...
I am rewriting it as Module function that is Called from various forms, to save space. The function receives the parameters varFormName and varControlName. I wish to write the results of the function back to a memo field on the form.I am stumped at the get go by the need to refer to the Forms controls with a full reference instead of the Me command.
Code:
Function DateStamp(varFormName As String, varMemoName As String)
'varInitials, varFormName, varMemoName are global variables
Forms!varFormName.Controls!varMemoName.SetFocus ' Error here
'Me!Notes = Chr$(13) & Date & " - " & Time() & " - " & varInitials & _
[code]...
how to refer to the forms control's with their full reference, from within the Module's function, the rest will fall into place.
I'm using Access 2007.
I have a customer table attached to a form.
I want to put a command button next to the customer's email address so that the user simply presses the button and Outlook will be started with the customer's email address automatically inserted from the table.
I am playing around with the Hyperlink Address function and whilst this seems to do what I want, I can't figure out how to get the customer's email address from the tabel and insert it......
I've inherited an Access 2010 database that needs tweaking. I have a form, let's call it CALLED_FORM, that is loaded from various places - ie there are quite a few places that do the following
DoCmd.OpenForm "CALLED_FORM", , , , , , coupleofarguments
This works great when called from most places but if it is called from 1 specific form there is a likelihood that some code is executed that shouldn't be.
So I want to be able to do the following
In CALLED_FORM I want to be able to skip some code if the previous form is say FORMX. For all other forms I want it executed. E.g.
If PREVFORM <> "FORMX" then
code
more code
even more code
End If
Is there a system variable for PREVFORM.
I know I can change coupleofarguments to threearguments and pass something over in that to show which form it has been called from but that is a bit difficult.
I am trying take all the data from one field "TOTAL" and Average it out and print it in one of my forms. I would also want this data to update automatically as the totals are updated. I am attaching some screen shots of what I am talking about The first pic is the totals field that i want to have averaged up. The second pic is where I want it to be printed out.
View 7 Replies View RelatedI need to use DAvg to get an average for a series of records. The DAvg is run from a textbox control source.
The following works:
=DAvg("[memberRating]","review","[catNo]=50")
However instead of specifying 50 I want to use the value from another textbox on the same form called... tbCatNo
For reasons I cant go into I cannot use VB or or a query to do the average.
I want to calculate the average of 16 anodes from text box into the "average drop" box and simultaneously want the data to be saved in the table too as one of the fields. Also, How to load form view while the database loads?
View 10 Replies View RelatedHi guys!!!!
I try to find an answer in the forum about "Average Fields",but ican't
I am confused:(
I wan't to export Avg Of the fields like in the panel below:
Hi, here is a query which returns rolling sum for past5days.The resulting table has threee columns,namely,
MyDateFiled ,MyValue,Past5DaySum
SELECT tblData.MyDateField, tblData.MyValue,
(SELECT SUM(a.MyValue)
FROM tblData As a
WHERE DateDiff("d",a.MyDateField,tblData.MyDateField)
Between 0 and 4)
AS Past5DaySum
FROM tblData;
Someone help me in modifying the above query ,so that ,a Type(1,2,3) can be assigned to each Past5DaySum
based on the following criteria.Is,it possible to use IIF function for three variables ?,so as tp get a new table(Mydate,MyValue,PastDaySum,Type)
Type,LeanPeriod,GoodPeriod
1, A<5 , A<20
2, 5<=A<=10, 20<=A<=40
3, A>10, A>40
LeanPeriod=Jun to Nov
GoodPeriod=Dec to May
Hi there, is it at all possible to use variables in sql? Please consider the following snippet:
... (WHERE [DateDue] < Date())...
The idea is to return a recordset where the DateDue is prior to the current date. But sometimes I need the opposite, ie a recordset where DateDue is in the future: ... (WHERE [DateDue] > Date())...
Right now I have 2 long queries which are exactly the same, except for the operator < for the first case above and the operator > for the second.
I of course did not want to repeat the queries so I tried:
Dim op As String
op = Me.txtOp 'txtOp is invisible and gets its value (either "<" or ">" ) thru
'a button click
... (WHERE [DateDue] & op & Date())...
I also tried:
... (WHERE [DateDue] & forms!frmInvoices!txtOp & Date())...
But to my "surprise" both failed.
Can it work? what should I correct or do differently here? I might need this kind of trick for other routines too, so I'd like to avoid copying the same query, just for the operator change.
I hope one of you seasoned sqlers can tell me more about it, I appreciate.
Regards,
Jaime
I want to create a field named ErrorCases that sums the rows meeting the following conditions: The row has a ValidityReason = 2 and for the same row ExpValidity is either null or 3. I want a count of the rows meeting these condtions.
Here is the formula I have constructed that does not work.
ErrorCases: Sum(IIf(([ValidityReason]="2" And [ExpValidity] Is Null) Or ([ValidityReason]="2" And [ExpValidity]="3"),1,0))
Any suggestions?
I am trying to incorporate the following code into my code:
Code:If Not QueryExists("Q_ABC") Then Set qdf = db.CreateQueryDef("Q_ABC") Else Set qdf = db.QueryDefs("Q_ABC") End If
Difference is that I want to use a variable to provide the query name...
Code:dim queryname as variantqueryname = "Q_ABC" If Not QueryExists(queryname) Then Set qdf = db.CreateQueryDef(queryname) Else Set qdf = db.QueryDefs(queryname) End If
but this does not work... but neither does this...
Code:dim queryname as variantqueryname = "Q_ABC" If Not QueryExists("queryname") Then Set qdf = db.CreateQueryDef("queryname") Else Set qdf = db.QueryDefs("queryname") End If
nor does this...
Code:dim queryname as variantqueryname = "Q_ABC" If Not QueryExists queryname Then Set qdf = db.CreateQueryDef queryname Else Set qdf = db.QueryDefs queryname End If
Can someone please show me how this is to be done?? I feel that it must be something simple...
Thanks a lot (in advance)!
The following code recognizes the value of 'TheYear' after running the line of code (ie. "#11/13/2007#"). The problem is it doesn't seem to recognize it in the SQL, but rather asks for a value when I reach the SQL code line. How can I get it to substitute automatically? THANKS!
DoCmd.SetWarnings False
TheYear = "#11/13/" & Year(Now()) & "#"
DoCmd.RunSQL "Update [Employees Trained List] SET [Employees Trained List].[This Years Training Date] = Null WHERE (([Employees Trained List].[This Years Training Date])< TheYear );"
DoCmd.SetWarnings True
I have a query that finds an average. How can I get the average to only show two numbers after the decimal?
View 4 Replies View RelatedI apologise for my ignorance, but I’m very new to Access.
I have a database of dates, that I need to analyse.
I have created a Form called "DateRange" with 2 date fields;
Text1 = Date From
Text2 = Date to
Command1 = Preview Report
My Query has 2 fields;
Slotdate = all the dates (show as 20051210)
Actdur = Actual Duration (show as numbers 1 or 12 or -3 etc)
The SQL View is;
SELECT slotapp.slotdate, slotapp.actdur
FROM slotapp
WHERE (((slotapp.slotdate) Between [Forms]![DateRange]![Text1] And [Forms]![DateRange]![Text2]));
I just want to calculate an average of Actual Duration
So that my report displays the average duration between the date ranges.
Any assistance in this matter would be greatly appreciated
I've looked thru a lot of posts, but can't seem to find the solution. It seems like this should be something I could figure out, but so far have not.
I have a table that is showing a production number for each day. What I'm trying to show is the best 5 day average production over a period of time.
Thanks,
Tom
Could someone please tell me how to work out the age of someone using a query or report and the average age of everyone?? I also need to know how to put on a report the total number of people satisfying the search criteria. It also says i must obtain a single record for each person and to do this i need to change a query property to allow only unique records to be displayed? do u know what this property is??
Please help!!
Thank You
I have started holding variables in text boxes on forms - (variables needed at module level for that form).
Is this a correct thing to do.
The benefits I have are not loosing the value should some error occur - and being able to reference it from another form.
Thanks
Paul
I am currently making a project as part of my Computing course. The project is a running diary, where users can log in and record their running times(Amongst other things). As part of the system i am going to include a calorie calculator, to do so i have to work out the BMI (Body Mass Index). To do this you have to divide the weight by the square of the height. The weight and height are stored in the tables.
I am planning on creating a form for this conversion which will refer to a query. When the users log-in their ID is stored in a Public Variable called "UserID".
What i would like to do is query the database for their height and weight using this variable, but i don't know how to go about doing this.
Does anyone have any suggestions?
Many Thanks
Hi
Currently i use one variable as indicated below from a continuous sub form field to open another form
Dim linkcriteria As String
Dim frmRoom As String
frmRoom = "frmRoom "
linkcriteria = "[HeadCloseID] = " & Me.HeadCloseID
DoCmd.OpenForm frmRoom , , , linkcriteria
however, after restructuring some tables, i need to open the form with respect to 2 variables instead of the previous one.
hence my question is can, how, and what code would i use to do this
many thanks in advance
I have an access form called forma. It has a command button. When I click a command button, FormB opens up and FormA closes. The FormB also has a command button. When I click the command button of Form B, it closes and opens FormC. Like this there are total 30 forms. What I need is. I want to count the no. of clicks of the command button in total. Actually each button is a correct option of A multiple choice question. I tried to declare a public Variable called total in the declareation of the first form and tried to use it directly in the succeding forms. It does not capture the values. How can I increase the value of the varialbe declared in the first form even after the form closes and capture till end. Actually I also should be able to send the value in a table. Please Help Me.!
View 2 Replies View Related