we have a field in a report, and currently, there is a text box with a control source of
=IIf([Flag1]="Capital",[Reserve]-Sum([Text52]),"")
They want me to add to it, where if [Text106]="0",[Reserve]-[Text52], otherwise it's [Text106]="0",[Reserve]-[Text106].I am able to do this second part OK with
=IIf([Text106]="0",[Reserve]-[Text52],[Reserve]-[Text106])
But then I lose the [Flag1]="Capital" part.
Just wondering if anybody could help me on this. I have encountered a problem in producing a report based on my main form (recordsource: tblStudents) and subform (recordsource:tblStudentsClasses), which are joined through their respective StudentID fields. My problem pertains to the fact that I just couldn’t seem to find a way to make my report produce a calculated value using an unbound control. I need it to show the average grade a student gets every semester by multiplying his individual class grades against its corresponding credit units and then add them together, and divide them against the sum total of that particular semester’s credit units. For example, a grade of 1.0 for psychology 101 multiplied by that subject’s 3 credit units + a grade of 2.0 for chemistry 101 multiplied by that subject’s 5 credit units and then divided by the sum total of those two subject’s credit units of 8.
I kinda find it complicated because each semester is made up of between 1 to 10 classes, so it would be inappropriate to just put the one average computation on a footer since each semester has to have an average grade of its own. Tried lots of procedure already and couldnt make anyone of them work. Another fact is that a credit unit of –3 and grade of 5.0 shouldn’t be included in the computation at all. And some of my data are supplied by combo boxes in the subform – cboCreditUnits and cboGrades (rowsources: tblClasses and tblGrades). How should I go about this all? Any ideas will be highly appreciated.
Thanks!
I have a report in which a textbox generates numerical values and letter values. I want to...On report load - if textbox = numbers then hide otherwise show if it contains letter values.
View 9 Replies View RelatedI have a frmOpenReport which has 7 combo boxes linked to 7 Query's.
I have found code to add selected values from one combo box from a button 'Open Report' to a report but struggling to find how to select selected values from all comboboxs and add to a report.
My code so far.
Option Compare Database
Option Explicit
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.HLO.ItemsSelected.Count = 0 Then
[Code] ....
I am very new to access. I have made a database that holds certain company information.
As part of a process a company must fill in and complete form and send to me and once they have I enter a 'yes' to the form and then link directly to said form.
I am wanting to be able to run a report to be able to only see the companies where a certain field has not been filled in.
For example I have a field for doc 698, if I have had this form back I enter a yes in this field, if I havent had this back I leave it blank and these blanks are what I want to pull off in the report.
I have made queries and then just filtered to show blanks but I would like to have in report form.
Objective to have a report generated from a query (Based on a Storage Table) in the record-source however need to make adjustments to each record based on the query output ie User Id is outputted as number as it needs to be for everything else but on the report loading needs to run an sql query on that ID number to get the name and then replace that value with the name and so on for each record and each appropriate column
Problem:I can either change All of the records outputted with the first records info or nothing at all. i need this process to occur individually on each outputted record
I am trying to get calculated fields in my report to work but zero values in the data are throwing up #Div/0! and #Error! and #Num!
The main data fields I am trying to work with are:
VehicleReading
PreviousReading
VehicleLitres
My aim is to track mileage and consumption for a fleet of vehicles and show daily and weekly averages in mileage and consumption and then to flag 20% increase in consumption.
I have a calculated field in my report named Kilometers,
= [VehicleReading]-[PreviousReading]
And also a calculated field named Consumption,
=[Kilometers]/[VehicleLitres]
My sum and average calculation fields in the group footers are only working where there are values above zero in the Kilometer and VehicleLitres and Consumption fields.
In reality there are some days where there has been no travel, so some records will have a zero for the Kilometers field and there are also days when there has been no refueling so there is a zero for the VehicleLitres field.
I have tried using the expression builder to create an IIf function but to no avail. I have tried copying the syntax suggested by the Expression builder:
«Expr» IIf («expr». «truepart». «falsepart»)
However I keep getting error messages and despite troubleshooting and looking on the internet I can't seem to find a solution to this.
I am quite new to Access and have been working with a sample DB from Microsoft. I have been looking for some ways to filter a report based on the values in some comboboxes, and have followed the instructions on a website (can't post link yet).
I also looked at this file (**can't post link yet**) and it seems like everything works fine here. So I started a new DB with my own table, and tried to do the same thing as in this file.
Everything works fine.
But when I try all these steps in my own DB (the sample DB from Microsoft), it doesn't work.
When I filter by "Status" (f.ex: "In Progress" or "Not Started"), it seems to work fine.
However, when I filter by "Assigned To", I get the following error message: "Data Type Mismatch in criteria expression". So I googled this error message , but this field has nothing to do with any date formatting.
It gets even stranger when I try to filter by "Project":
Then a new pop up appears and asks me to enter the "Project".
No matter what I enter, the report turns blank
I have a report that is based on a query.
The query has two fields. Start and End Dates.
When I run the query the Parameter box asks for the dates by using <[Date1] and >[Date2]
What I want is to have 2 fields at the top of the report, that display the values I enter in these boxes?
Suppose we have a report that outlines several fields, one field shows the City. In the report, this week, we see 10 records "New York", 8 records "Houston", 7 records "London", 3 records "Paris" and so on.
Next week's report outlines different cities and different number of records. I need to have in the report footer a "recapitulation" , a field that would say :
New York 10
Houston 8
London 7
Paris 3
Total 28
Next week cities and number of records might not be the same, we may have
Tokyo 12
Singapore 14
New York 6
London 7
Can i make my report in that way that it will count the values without using VBA ? I tried the count values option but it counts the overall report, does not take into consideration the different values.
I have a report that returns timesheet info on employees. The group-by is on the employee, and the detail records are projects billed-to, including how much time billed to each project by that employee.
Parts of the detail line are accomplished by dlookups, and I was hoping to sum() these values on each employee/group, in the employee footer area.
Per the image, the circles show where I hope to show the sum'd value from text boxes indicated by the arrows...
In the group footer on employee I've set text boxes to:
Code:
=Sum(DLookUp("ProjectLeaveHours","qryTimeRpt_LeaveOnly"))
...basically taking the dlookup accomplished in the detail line and trying to sum() it... No luck.
Code:
=Sum([RADefaultHours])
...using the name of the control where the dlookup is accomplished prompts me to declare the value of the "RADefaultHours" - indicating the report text box control doesn't know what I'm talking about...
Code:
=Sum([Reports]![rptTimesheetReport]![RADefaultHours])
...trying the above again, but hoping to properly declare what is being sum'd... No luck.
...I saw some posts regarding setting "running sum over group" - and I tried that on the text box in the footer where the sum() is being attempted, but no luck.
Can I do this this way? Perhaps I need to set some other variable that is more agreeable than a 'textbox with a dlookup' to do the sum when grouping?
In Access 2003 I have a form, at the footer of which I want to put some summary information. One of which is a count of the number of purchases over £500. I have tried many variations after searching the posts on this site but the only one which does not return me an error of some kind with the expression is
=IIf([Bought For]>"500",Count([Bought For]),0)
where 'Bought For is the reports listing of the purchase prices in a given date range when opening the report, but the value returned in my test is '0' when it should be '2' as the test file has two purchases of over £500.
I'm trying to make a report that a untrained user can use to review the relevant data from the database I'm creating. All of the work I've done so far has been directly in the tables . Here's a quick outline of my general data organization:
Department:
DepartmentID(PK)
DepartmentName
Employee:
EmployeeID(PK)
EmployeeName
DepartmentID(FK)
Needs_Work (check box)
[code]....
Basically, I want the user to select a department to view using a combo box or option group (which I'm not sure of how to do in a report). Then, I want to view the employees within that department sorted first by whether or not the Needs_Work check box is selected, and then by how many of their competencies are unchecked.
I am not sure whether I posted in the right place but here's the deal: I have a form and a subform based on two tables and on the form I create a mathematical expression concerning a field which is based on another. The results are correct but of course the values in the table remain the same (null). Is there a way to have a formula in the table and then when I create the form based on that table to have the formula ready? BTW where are the data stored if they are not stored in that table?
View 1 Replies View RelatedHi everyone
i have been struggling on this database that i am developing for a while and i haven't found a solution yet.
i have a Two tables
Table 1
"Attendance" with fields
Attendance ID,
Hours,
Group ID.
table 2
"Days" fields,
Day ID
Week beginning
Monday
Tuesday
Wednesday
Thursday
Friday
Hours
now i have created a form from these two tables, and what i wanted was each day of the week i will input a value under the day and i wanted it to be added up and then the result outputted to the "Hours" Box.
My dilema is that although i have figured out how to add up the fields in the form view by changing the Control source for the Hours box to something like
=[Monday]+[Tuesday]....[Friday]
this will only display the value in form view, the table will not update. i guess this is because the control source is not relating to te table anymore.
so if someone has some other ideas i would appreciate it.
regards
Yusef
Below is the code I have created to calculate the values "CalcValue" and "CalcWork". The values for AssignValue, AssignPriority, Complex, Effort, Goal are all assigned by the selection made in the referred to/related combo boxes. However, the caluclated values return the concatonation of the intergers and not the sum of the integers. Additionally, if I am to use the CDec() function how do I specify the number of decimal places?
what am I missing here?
thanks!
Private Sub cboAssignPriority_AfterUpdate()
Dim CalcValue, AssignValue, AssignPriority, Complex, Effort, Goal, CalcWork As Integer
Value = 0
AssignValue = 0
AssignPriority = 0
CalcValue = 0
CalcWork = 0
AssignValue = Me!cboAssignValue.Column(3)
AssignPriority = Me!cboAssignPriority.Column(2)
Complex = cboDBObjectID.Column(2)
Effort = cboTaskTypeID.Column(3)
Goal = cboAgencyGoalID.Column(2)
Value = AssignValue + AssignPriority
CalcWork = Complex + Effort + Goal
CalcValue = CDec(Value)
CalcValue = Me!lngCalPriority
Debug.Print "Complex="; cboDBObjectID.Column(2)
Debug.Print "Effort="; cboTaskTypeID.Column(3)
Debug.Print "Goal="; cboAgencyGoalID.Column(2)
Debug.Print "AssignValue.Column(3)="; cboAssignValue.Column(3)
Debug.Print "AssignPriority.Column(2)="; cboAssignPriority.Column(2)
Debug.Print "Value ="; Value
Debug.Print "CalcValue ="; CalcValue
Debug.Print "CalcWork ="; CalcWork
End Sub
I have a table with numberous records with Values in a number of fields. In a report I am showing all these records but at the end I want to tally the values.
Whats the best way to do this?
I thought of placing a text box in the form that will run some code to calculated the total....
What kind of code would I use to accomplish this?
Is there an easier way?
I have a report that contains 3 subreports with a calculated total text box in each. I am trying to calculate the total of these text boxes from the subreports in the main report but I keep getting an error message (#Name?). Is it possible to calculate calculated sums from subreports in a main report?
View 1 Replies View RelatedI am pretty new to access, I will get right to it.
I am scoring rating scales. I want to be able to enter just the responses from a person, say on 80 questions (preferably into a form), where all the answers are 0 to 3 and have Access add the questions into the right groups to calculate all the sub-scores of the rating scale.
For example, Scale A may consist of Questions 1, 6, 15, 29, 35, and 70
I need access to add those up and save them so I can use them in a report.
I also need access to add the frequency of certain questions that were responded to with the answer 3.
I have gotten Access to calculate the scores in a form by adding extra variables to the form, erasing the variable name out of the white box and putting in the syntax =[Q1]+[Q6]+[Q15], etc. in place of a variable name but it just sits there on the form and doesn't store it anywhere.
Thanks
Hello all. I hope someone can lend a helping hand here. I am trying to create a query that will calculate a 24 hour value for every day of a month. For a month a go out a read meters. I need to get the difference of two readings by subtracting the first day from the second day and so on until I get to the end of the month. So, Day2-Day1=24 hour value, Day3-Day2, Day4- Day3... I have this in a spreadsheet but can't seem to grasp it for a query in a database. I would appreciate any help. Thanks.:confused: :D
View 1 Replies View RelatedHi, I wonder if someone can help me with the following problem:
I have a table with 2 columns,start time and end time, both containing time values in a four digit format, eg 0930 being 9:30am etc.
I have made a query to convert these into the standard format (eg 0930 becomes 09:30), using left/right and & functions.
Now I use the timevalue function to convert the string , eg 09:30m into a recongnised time value.
The problem is now I want to find the difference between the start and end times- but when i try a simple end_time - start_time formule it returns a long number which i dont understand, rather than just giving me the difference betwen the two times.
Im sure there is a simple way to do this which I dont know, can anyone help me out? Thanks in advance!
Hi, I wonder if someone can help me with the following problem:
I have a table with 2 columns,start time and end time, both containing time values in a four digit format, eg 0930 being 9:30am etc.
I have made a query to convert these into the standard format (eg 0930 becomes 09:30), using left/right and & functions.
Now I use the timevalue function to convert the string , eg 09:30m into a recongnised time value.
The problem is now I want to find the difference between the start and end times- but when i try a simple end_time - start_time formule it returns a long number which i dont understand, rather than just giving me the difference betwen the two times.
Im sure there is a simple way to do this which I dont know, can anyone help me out? Thanks in advance!
I have a query that must calculate the different between values in two different records, based on the date.
The underlying ORIGVALUES table contains:
Date; Value; Diff
1/10/15; 100; (this is what I am trying to calc)
1/11/15; 101; (this is what I am trying to calc)
1/14/15; 102; (this is what I am trying to calc)
What is the best way to have each record calculate the selected date value divided by the prior date value)? E.g.
For 1/11 the math is 101 / 100 (so the 1/11/15 record would show the result of 1.01)
For 1/14 the math is 102 / 101 (1/11 to 1/14 represents a weekend)
I could copy the prior day values to the current day records and do that math easily but that is very redundant.
I have created a link to a CRM system that we have, and am using access as an interface for a specific task with the data contained within this CRM.
I have written a query that pulls to fields of data together but cannot get the two to calculate.
Within SQL server management studio I use the following query to give me a numerical response.
SELECTPRCPRICE,SUM(CAST(VL2BENEFICIALASNUMERIC (20,6))*CAST(PRCPRICEAS MONEY)))ASHOLDINGVALUE
Access doesn't appear to accept the cast functionality / how I can perform a similar functionality with access?
I am using workdays to calculate time passed between two dates. I also have a table for Holidays that I don't want counted as work days. This is working well, but I would like my queries to show a null value instead of showing #Error when a date field is empty. Here is my coding in my Module.
Option Compare Database
Option Explicit
Public Function Workdays(ByRef startDate As Date, _
ByRef endDate As Date, _
Optional ByVal strHolidays As String = "Holidays" _
) As Integer
[code]....
I'm creating a simple delivery management database. I have created a query that calculates the time taken to deliver a package. However I need it to work out how many times it took longer than 3 days and just display the amount of times that this occurred. I'm using access 2003.
View 4 Replies View Related