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?
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.
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.
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.
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.
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
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.
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.
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!
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.
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))
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?
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.
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.
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
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?
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 ??
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...
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))"
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?
