Simple Calculation Won't Work?
Jan 19, 2005
I have been designing my first database and am just about ready to finish my first report. I have created a text box and added the following to the box =myfield1+myfield2+myfield3+myfield4
I created this through the expression builder so I know the fields a correct but now the calculation is not working. I don't get any calculation in the box except a "0". Why isn't this calculation giving me the sum of these 4 fields?
Please please help.....
View Replies
ADVERTISEMENT
Aug 7, 2006
Ok, i have a problem and i can think of no reason why what i'm doing shouldn't work.
I have a form with a number of combo boxes like so.
Combo1: RSP
Combo2: Currency
Combo3: GBP Exchange Rate
Combo4: Euro Exchange Rate
Combo5: RSP (In Euros)
I type in the RSP in the first box, i then select currency (only one currency will be availabe based on an earlier choice of country), select exchange rate in GBP and Euros and then Combo 5 has a simple calculation to perform.
[Combo5] = [Combo4] * [Combo1]
This will convert the original value RSP (which is in local currency) to the RSP in Euros. Ideally this calculation would happen automatically and fill Combo5 but it won't work. I can't even get this to happen as an on click event.
I can't understand why this won't work although i'm sure one of you will show what a simple thing it is. I just can't seem to get my head around it.
Many thanks in advance
Martyn
View 2 Replies
View Related
Jul 20, 2007
Well, I hope this is simple, but for some strange reason, very difficult for me.
I'm having issues setting up a calculation between 2 tables. for example:
Table1 ID Quantity
Table2 TransID Used
The "Quantity" from Table1 is a set number but the "Used" will change. all I want to do is have a Form and Report that will subtract the "Used" from Table2 from "Quantity" from Table1.
I've tried: =Table1.Quantity - Table2.Used
=Quantity - Used
what am I doing wrong? all my attempts have failed, even though I have setup a relationship between the 2 tables.
any idea?
thx
View 6 Replies
View Related
Apr 18, 2008
Hiya,
I have a formula in my querry which works.
total: (Ticket_Info!T_Number_of_tickets*Ticket_Info![T_How_many_games/events_does_this_allow?])-(Ticket_Info![How many Clients are going?]+Ticket_Info![How many Aplant staff are going?])
Now what I would like to happen is that if this value does not =0 then for a mesage to pop up to alert the user that they need to ammend something.
Background of database:
It is to record where and who a business take people on trips out.
So the questions asked have been: how many tickets do you have? Then how many people are you taking (followed by a break down of people). So if the number of tickets don't = the number of people going the user needs to fix it.
Thank you so much im completly lost!
Sarah :(
View 3 Replies
View Related
Nov 2, 2006
Form: frmProduct
Field: unitprice.
I have some simple validation that unit price is greater than 0. The code is this:
If Me.txtUnitPrice.Value <= 0 Then
MsgBox "Please enter a value greater than zero", vbOKOnly, "Alert"
Me.txtUnitPrice.Value = Null
Me.txtUnitPrice.SetFocus
End If
However, when I run it, the setfocus doesn't work. It jumps straight into the next field. I can make it run to any other field (productname, productID). But not back to UnitPrice.
View 6 Replies
View Related
Feb 4, 2007
Hi,
I have used the Contacts template.
I want to make a simple calculation for mins remaining from sunbed courses.
So lets say one of my clients in my database was called Sally.
When I open up Sallys details screen I want it to show:
- What sunbed course she's on (inputed via details screen per course taken out)
- How many mins she's used (inputed via details screen per visit)
- How many mins remaining (calculated for us)
I don't know how to make these calculations!!!
I can not work out how to put formulas into the Contacts table.
Can anyone help me out please?
(see pic below)
http://img219.imageshack.us/img219/9760/accesssunbedzd5.jpg
View 8 Replies
View Related
Mar 14, 2006
Hello.
Please see attached picture for an idea on what i'm talking about. I need to achieve the following:
I have a query and it gives a total policy commission for each policy an agent sells. There are several agents under one commission statement.
For example
agent ref 1, policy 1, information, commission amount
agent ref 1, policy 2, information, commission amount
agent ref 1, policy 3, information, commission amount
agent ref 2, policy 1, information, commission amount
agent ref 2, policy 2, information, commission amount
agent ref 2, policy 3, information, commission amount
agent ref 2, policy 4, information, commission amount
agent ref 3, policy 1, information, commission amount
etc...
What i need access to do is add a total for each agent before it shows the next agent, so the above example would change to:
agent ref 1, policy 1, information, commission amount
agent ref 1, policy 2, information, commission amount
agent ref 1, policy 3, information, commission amount
Total: commission total
agent ref 2, policy 1, information, commission amount
agent ref 2, policy 2, information, commission amount
agent ref 2, policy 3, information, commission amount
agent ref 2, policy 4, information, commission amount
Total: commission total
agent ref 3, policy 1, information, commission amount
etc...
I hope that this makes sense, and i know its probably very simple but im a n00b! so any help would be much appreciated.
Kind Regards
Darren
View 1 Replies
View Related
Nov 25, 2004
I am trying to do a simply calculation on a report which uses a value from a subreport. The main report is called rptAllProjectsStatus the sub report is called rptAllProjectsStatusExp, and the box from the sub-report is called tboAmount which is controlled by query.
In the main report I have a box whose control source is
=[tboFixedFee]-[tboSumStaffingCost1]-rptAllProjectsStatus!rptAllProjectsStatusExp!tboAm ount
But it doesn't recognise this formula - what am I doing wrong?
I know the problem is with the latter part of the formula (in connection with the sub-report), as if I remove this part, the rest of the calculation works fine.
thanks
S
View 6 Replies
View Related
Dec 21, 2005
I have a report that contains an expression field for total sale. It is computed from multiplying quantity * price. I want to have a grand total item at the end of the report that sums all the "total sales" items. My question is what is the syntax to do this and also what section of the report should I put it in? Should I put it in the header? Any and all help is appreciated.
View 3 Replies
View Related
Aug 3, 2007
Hello,
This is a pretty simple question I'm sure. This is a simplified version of my data:
AAC |Begin |End |Total # of days |Average for AAC
m28357|7001|7195|
m28357|7001|7124|
m20195|7002|7234|
m20195|7002|7143|
The purpose is to calculate the total # of days by just subtracting the julian dates and then I need to figure out how to have some sort of way to calculate the average # of days but only based on AAC.
Thank you for the help!
Kat
View 1 Replies
View Related
Feb 18, 2014
I'm trying to open or unlock a recordset do do a simple calclation and then relock the record set. How this can be done with vba?
View 7 Replies
View Related
Feb 27, 2006
We are creating a simple database to maintain driver license information for faculty, staff, and students who use cars from the university’s motor pool.
To do this, I have created two tables: tblDRIVER and tblLICENSE.
The fields in tblDRIVER are:
pkfDriverIndex
strLastName
strFirstName
strInitial
strAddress1
strAddress2
strCity
strState
strZIP
datBirthDate
The fields in tblLICENSE are:
pkfUpdateIndex
fkfDriverIndex
datDateUpdated
strState
strLicNumber
datExpirationDate
ynViolations
ynActive
Information about the driver is stored in tblDRIVER and the driver’s license information is stored in tblLICENSE. Periodically, we run a report that identifies drivers whose licenses are due to expire within a certain number of days. All this works fine.
My problem is that I am trying to create a lookup form that will load from a data entry form that will permit the Motor Pool Clerk to look up a driver by name and review the licensing information (which appears as a subform).
All this sort of works- I am using a combo box (based on a query) to look up a driver’s last name (which it does) and to populate all the driver’s information on the look-up form (including license information in a subform). Currently, the combo box locates the driver (including the unique index, last, and first names), and populates the form with first and last name but the rest of the information is not displayed on the look up form. Worse still, sometimes one driver’s last name matches up with the first name of the next driver listed in the table! This seems to happen only if a look up is attempted more than once. What gives.
Thanks for the help.
View 9 Replies
View Related
Sep 10, 2007
I have an expression in one column of my query and It keeps returning a negative result and there are no negative numbers in the source data. Can anyone help. I just want to sum the sums the two IIf expressions, but its not working. Thanks
Other Qty: Sum(IIf([QSum]![ACCOUNT]=2 And [QSum]![ICUNIT]=95 And [QSum]![UNITS]="ITEM(S)",[QSum]![QUANT],Null)) Or Sum(IIf([QSum]![ACCOUNT]=2 And [QSum]![ICUNIT]=100 And [QSum]![UNITS]="ITEM(S)",[QSum]![QUANT],Null))
View 4 Replies
View Related
Dec 29, 2006
Ok I am right now making a simple Vendor/Product database to create a line sheet for some sales folks. I have 3 tables: Vendors, Products, and an associate entity Vendors_Products to relate the two. I have a form currently that draws the Vendor Name (primary key) from the Vendor table and the Product Name from the associate entity. This allows me to create new vendors and select current product types from a drop down box. The problem is that the drop down box is too long and it is tiresome when 1 vendor has 10 product types.
Can anyone tell me how to resolve this? I thought it would be better to have option buttons and display all available products. Then you could just click all of the option buttons that apply to that Vendor and it would create the relationships...is this possible?
View 1 Replies
View Related
May 30, 2006
I’m a newbie to Access, I have done some simple things but I now have to do something that is out of my league.
I work in an engineering company and I'm trying to make a database that the engineers can select a specification quickly, a specification or spec is like a table. Things like materials, ratings, pipe Schedule, etc are stored in it. The spec says how strong a pipe will be.
When selecting a spec you have to look at the service (what the pipe is caring, eg high pressure steam, or low pressure water). Services also store things like Design Temperature and Design Pressure.
You are giving the Service, and you have to select a spec that will be able to handle the Design pressures. To do this you compare the Design pressure to the Maximum allowable Pipe pressure (it is calculated at the join (flange) as this is the weakest point).
One other pike of information is the Tables that have the Maximum allowable Pressure. To find the Maximum allowable Pressure, you need to know the Material, Rating and the Design Temperature. Each material has its own table.
If you have a look at the attached file then in tables, SPec has Spec ID (Spec) which is Primary Key. Flange Material (FlangeMat) and Flange Rating (FlangeRating).
The Flange Material should take you to another table, FlangeMat. This table is used to get you to the correct Maximum allowable Pressure table.
There are 2 Maximum allowable Pressure Tables, TABLE_211 and TABLE 212. Here the rest of the information from the Spec is needed (Flange Rating (these are the field names with 150, 300, 1500, etc numbers)). And also the Design Temperature is needed of the service.
Then it looks up the Maximum allowable Pressure from the Table and displays it. I was thinking a form.
Also the design Pressure needs to be displayed so the engineer can compare the two.
So for the form, there will need to be a drop down that has the Service, A drop down that has the Spec, and the two values, the Design Pressure and the Maximum allowable pressure.
All I have done is shown on the file, I started to play around with the Relationships and tried to create a Form but I could not get it to work so. I deleted it and left it with just the tables. If possible can someone please help me, maybe even get my file to work and the post the working one, I will then be able to understand it.
View 2 Replies
View Related
Jul 23, 2006
The company I work for uses A97 throughout. Although on my laptop I have Access 2003. I want to design my inventory control database using A2003 not 97 for obvious reasons. I know A97 users cannot open a Acess 2003 db.My Q is: If I created a A2003 db and also built some data access pages within the database and placed it on my server, could users view/add/edit records in the db via these pages using their web browsers? This would only be required across our small network of less than 10 users.Any advise welcome. Even just a YES or NO would help Many thanks.
View 4 Replies
View Related
Oct 6, 2006
Can anyone look at this database at let me know why it is not working
plse
Stuart
View 7 Replies
View Related
Dec 8, 2006
Hi All,
I need to create some conditional formatting on a field that will change the colour depending on how much money has been spent
I have MaxBilling As the Field I want to attach the condition formatting too which can be a user entered value or system updated depending on other things.
The Current worked costs total field Is Called Text119.
when a project starts The Cost will be 0 so I don't intend to assign a colour there.
when the Costs get to 50% of MaxBilling I would like to change the colour to Green
70% Chances to yellow
90% Would change to Dark Red
I can do most things but %'s and me don't mix well LOL
Any help would be much appreciated
thanks
mick
View 3 Replies
View Related
May 11, 2006
hey there kinda new to access...
how would i work out the amount of hrs worked?
e.g
time start at 8.00am, time finish at 5.00pm
how would i get to say 9hrs?
View 5 Replies
View Related
Dec 7, 2006
I have two database which are very similar. They both use the same back end table. I have a query that I developed in one db and it works correctly there. However, when I import it into the other db, I get the Ambiguous Outer Joins message. I've tried copying the SQL from one and pasting it into the other and that didn't help. I've checked the table used and they are all properly linked in the second db. Any ideas about what could be going on?
View 4 Replies
View Related
Apr 4, 2007
I have created a query thats initiated by a command button from a form and one of the criteria for the query is a 'Like' command.
On the form, various option buttons and combi-boxes determine the search criteria and put it into a hidden textbox. Then, the query is run based on the contents of that hidden textbox using 'Like'.
However, I cant seem to get the 'Like' command to work with the contents of the hidden [forms]![frmSearch].[txtSearchcriteria] field.
Do I need to edit the VB code on the form to format the text to include the Like '* prefix and *' suffix, or should my search box remain basic text and have the Like command in the query ??
I seem to have tried loads of variations incorporating the various components of the Like command (the asterisks, single quote marks and even the Like command itself) on either the Form field or query but nothing seems to work. Anyone have any ideas ??
Thanks chaps.
View 2 Replies
View Related
May 30, 2006
The on click event, for a Report, has the following:
Sub DateEntry()
Dim Start_Date As Date
Dim End_Date As Date
Start_Date = InputBox("Enter Start Date mm/dd/yyyy")
End_Date = InputBox("Enter End Date mm/dd/yyyy")
End Sub
This works fine and allows for Input. I added the sub below, but it does not work and the report opens:
Sub CheckEntry()
If Start_Date Is Null Then
MsgBox "No Date was Entered"
DoCmd.Close acReport, "Summary Action Report"
Exit Sub
End If
If End_Date Is Null Then
MsgBox "No Date was Entered"
DoCmd.Close acReport, "Summary Action Report"
End If
End Sub
View 5 Replies
View Related
Jun 9, 2005
I have a form with two textbox, when I type something in textbox, the second textbox is filled with database query "SELECT MAX...", this works in one PC but doesnt work in other...
Why ?
all PCs is Windows XP with Office 97 and 2000.
thx
View 1 Replies
View Related
Aug 5, 2005
I thought I understood Dlookup – but I am not able to make it work!
Here’s my scenario:
tbl_Details (GroupID, and other misc fields)
tbl_GroupList (AutoKey, GroupID, GroupName)
rpt_Report (GroupID, and other misc fields)
I would like my report to show the GroupName instead of GroupID (Data is entered as GroupID – hence, I created the look-up table “tbl_GroupList.”
I created a text box on my report:
=DLookup(“[GroupName]”,”[tbl_GroupList]”,”[GroupID] = Reports![rpt_Report]![GroupID]
I think my syntax is correct – I just can’t figure out why it won’t work. Do I need to link the tables?
I even tried DLookup in a query (changing the above code to fit the query fields) and can’t seem to make it work. Please help!
PS -- I don't think it matters, but I am trying to do this in a sub report.
View 3 Replies
View Related
Oct 5, 2006
i am trying to run an append query in SQL which appends a calculated value into another table 50 times incrementing the day by one day each time. When I run it it asks me for the parameter [NewDay] each time. It is obviuosly not picking up the variable.
Can anyone tell me why ?
Dim NewDate As Date
Dim n As Integer
For n = 0 To 50
NewDate = Date + n
DoCmd.RunSQL "INSERT INTO InventoryEvolution ( SAP, Stock, [Date] ) SELECT UK_Product_Estimate_Live.[RE SAP Code], ((Sum([Estimate01])+Sum([Estimate02]))/50)*-1 AS Stock, NewDate From UK_Product_Estimate_Live GROUP BY UK_Product_Estimate_Live.[RE SAP Code] HAVING (((UK_Product_Estimate_Live.[RE SAP Code])=513450))"
Next n
View 6 Replies
View Related
Jun 18, 2007
I created a database in MS Access 2000 for the company I work for.
I sent this database from the US to our sister company in the UK, and the gentleman there has no right-click access within the data entry form.
Is there something that needs to be turned on or a check box that needs to be checked for right-click access within the program?
Thank you in advance.
Jason
View 14 Replies
View Related
