Field To Perform This Calculation
Feb 23, 2007
How do I get a field to calculate this formula?
I want a script to find the precise current date and from another field find a number and add it to the current date day to make current date + number = date in the future.
For example
Field: Total Nights Hired = 2
Field: Date due back = 23/02/07 + 2 = 25/02/07
I am sorry if I did not explain this well enough. I am totally new to databases but need help in my corsework. I am designing a video rental system.
Any help will be greatly appreciated.
Andy
View Replies
ADVERTISEMENT
Feb 20, 2015
I'm trying to get a query to perform a calculation and round the results.
The fields that I am running the calculation and am trying to round are Data Type Number, properties Field size Single and decimal places Auto.
I have tried the built in function described in Allen Browne's site to no avail.
[URL]
I have tried rounding the individual fields and then adding result.
I have also tried rounding the result (as attached).
View 11 Replies
View Related
Nov 8, 2004
KTYPE=text; EXPIRATION_DATE=date/time; OVERAGE=date/time
im doing a report based on a query (which has a few fields, including KTYPE, EXPIRATION_DATE, OVERAGE). im trying to determine the OVERAGE based on the following conditions:
here are the conditions:
if KTYPE="PO", then OVERAGE:=(EXPIRATION_DATE + 6 months)
ie.. KTYPE=PO, OVERAGE:=(01/04/2004 + 6 months) = 07/04/2004
if KTYPE="IAA", then OVERAGE:=(EXPIRATION_DATE + 20 months)
if KTYPE="FA", then OVERAGE:=(EXPIRATION_DATE + 12 months)
and so on and so forth.
I'm very new to Access. What formulas do I use and do I put the formula in the query or the report.
Please help. thanks in advance
View 2 Replies
View Related
Nov 21, 2005
Hi,
I have a cross tab query that has field names 6, 7, 8, 9, 10 representing months and a relevance date field with a date.
I have a form and from that form i need to get the value of the last three months from a relevance date field.
I.e relevance may be 01/10/2005 so i would need 3 labels, (labels are already made, but there source needs to be) 10, 9, 8. But the next row may have a relevance date of 01/08/2005 in which case it would be 8,7,6.
Any ideas on how i can do this.
Thanks Adam
View 3 Replies
View Related
Mar 10, 2006
Hi i really need help!
i need to be able to use "lookup", i have a set of postcodes from 3000 customers, i need to extract these and compare them to lists i have of postcodes, which make up a region. And run queries from these
e.g a customer on my database has the postcode BD21 7KK
On my list of postcodes i have on paper - WEST YORKSHIRE - BD21 7
therefore The BD21 7KK Customer falls in the West Yorkshire region.
I need to do this four 4 regions with lists i have on paper then run queries.
Can some one please provide the neccessary steps?
Or even give me an example made?
Im really stuck on this at the moment and me job depends on it ! :(
Thanks
View 1 Replies
View Related
Aug 12, 2005
Having some problems with a calculated field on a form. The field came from my table but I typed in the calculation (=[cost_price]+[sale_price]) and it works fine but it wont save to my table so when I run a repot off it doesnt have any of the calculated values because they arent saved in my table??? Can anyone help?
Thanks
Tom
View 2 Replies
View Related
Apr 16, 2008
So here is my problem i am semi access savy but i have having trouble with one thing. At work when we make a schedule there are 5 possibilites for how you want to mark your day. I have created a field using text as my parameters since my possibilities include both letters and numbers. At the end of each week I need to create a calcuation only utilizing the number which is 1 to add up the total shifts in the week.
I have tried some IIF statements with no luck. The 5 possiblities are 1, c,r,a,l. Each of the letters stand for something. When the scheduler runs the query he needs to see the letters or the number for each day. But i can't create a properly working calculated field for adding all of the 1's together only. The letters don't need to be added. Any ideas?
Also I have to run a sum for the total of each day at the bottom so I am running into the same problem there if there is any letters in the boxes. Going to a number field is not an option
thanks for the help
View 6 Replies
View Related
Jul 31, 2007
Below is the code I have written to calculate the arrival and departure times based on the users input on time in the air (tripETE) and on the ground (tripGT). The arrival (txtArrTimeZulu) and departure (txtDepTimeZulu) times are unbound boxes since there is no need to save the information. My problem is when the next leg's ground or air times are entered, it updates the arrival and departure times for all the records to that value; overwritting the previous legs times. What am i missing here? Do i need to open a recordset when opening the form or when entering "TripETE" and "TripGT"?....
View 1 Replies
View Related
Mar 4, 2013
I have a report which is a statement. I want to be able to have a field that calculates rent, and minus payments. There are a few rent types, Pro Rated rent, Rent brought forward and rent changed, then there is 1 payment type. I am trying to write the sum a few ways, but have not had success. If rent type i= 1 or 2 or 3 I want it to add, if rent total = 4 I want it to minus.
=Sum(IIf([RentType]=1 Or 2 Or 3,[RentAmount],0))-Sum(IIf([RentType]=4,[RentAmount]))
View 2 Replies
View Related
Apr 20, 2006
I know how to do a "Like '*xyz*' " type condition in a query. How do I set up a "Not like '*xyz*' " type condition in a query?
Thanks for your help. This forum is wonderful!
View 3 Replies
View Related
Feb 27, 2006
Hi,
Is there a way i can perfrom some functions before or after the next record in the recordset is loaded. (form is set to continuous)
For instance:
If i have 5 records in my forms record source after the 1st it will msgbox "sumin" after the second msgbox "sumin" and so on?
This is the record source in the properties of the form by the way not a recordset within vba?
Thanks
k0r54
View 2 Replies
View Related
Nov 3, 2004
Hello Everyone, (where would i be without ya)
Here is what i would like to do if anyone can help
I have a table with the following columns which are currency values, along with aload of other stuff
High
Med
Low
These have been put into a form as bound text boxes, i would like to allow the user to
chose their rate, say by a button or maybe option group "i'm open to ideas"
Dependent on the choice made the rate chosen will then go into a independent text box.
After there i'm fine, the reason for this is,.. the chosen rate/independent text box will form part of a calculation
Best Regards AM
View 4 Replies
View Related
Apr 10, 2014
I'm getting an error when trying to run a report. "There isn't enough memory to perform this operation. Close unneeded programs and try the operation again."
1.) I tried to install both the V-Tools and Rick Fisher add-ins, and neither one could be added. I got errors from both.
2.) I'm working with a small test database to create a report. Db is tiny, 1.8Mb. But I've also created a report which has several embedded subreports. Everything is based off of queries which certainly have to sort through some data.
3.) I have compacted and repaired.
View 14 Replies
View Related
Aug 22, 2005
OK, I figured out how to make a calculation. My big problem now is to have a global field, which would be "beginning balance" and "ending balance". I have 5 employees that share the budget. The budget starts at $1280.00. One employee may buy somthing one day and then another day while another employee may also buy something. I want to be able to put in the amount the employee spent and have it deduct from the balance. But then when I go to another employee and subtract what they spend I want that to subtract from the balance. I want the balance to be visible at all times no matter what employee I am on. Hope this makes sense. I have an attached file that shows how I did it for one employee. Cannot figure how to be able to do it for each employee and just the balance changes. Also I built a subform to be able to show different dates. But the problem again is having it subtract from one global balance. Thank you.
View 3 Replies
View Related
Aug 26, 2005
I have the calculation =DateDiff("yyyy",[DOB],Now()) in the Data/Control source properties of a numeric field in a database/form which correctly calculates age in years from a date field DOB. How do I get this field to save to the database?
Georgina
View 2 Replies
View Related
May 11, 2014
I would Like to save the data to our Table
[InvoiceTotal] - [InvoicePersonal] "if Invoice Personal contains a value" = [InvoiceShareLoan]
If "InvoicePersonal" does not contain a value, no calculation will be made
View 2 Replies
View Related
Jan 31, 2013
is there any way to sum a column or field that has a calculation in them?
View 5 Replies
View Related
May 31, 2013
Im building an expression where I am dividing fields by constants and adding them together. I need all answers always round down, so 19/20=0, 21/20=1
View 8 Replies
View Related
Dec 3, 2014
My Sales! table has fields [ItemType] and [Price] and VAT! table has [ItemType] and [VATRate].
Both tables has a common field which is [ItemType].
I am trying to make a query which will calculate the VAT figure by multiplying the Sales table [Price] with the corresponding [VATRate] in the VAT table by matching the [ItemType] in both tables.
I tried DLookup but couldn’t find a solution. How do I accomplish this?
View 1 Replies
View Related
Jan 27, 2006
Can someone please tell me if there is a way to perform lookups in Excel from an Access table without first exporting the data into Excel? I have a form that is being exported into Excel from a pricing tool our company uses from an outside vendor. It is important the users have the ability to perform complicated calculations and are not at all experienced in Access. There are several data sources that are well over the 65,000+ rows limited by Excel. I need to either prove or disprove whether it is possible to perform lookups between Office applications. If it is possible to lookup between applications, I would appreciate any sources known to learn how to do this. If not possible, I would like to be able to confirm that fact to my manager.
Thanks for any help that can be offered.
tschultz
View 1 Replies
View Related
Apr 4, 2006
Hi there.
Does anyone know how to use an update query to copy some records from one table into another?
I have table1 which has all the contact details and what I would like to do is work out a process for importing records from an excel spreadsheet.
What I have so far is as follows:
1. In access go into the queries tab and select update query
2. Open both table1 and table2
3. Drag the fields to be updated (table1)
4. In Update to select [table2].[table2 column name]
When i run this nothing appears to happen. 0 records are appended.
Does anyone now how to go around this with another solution or should I be using other SQL methods?
All comments much appreciated
Thanks
View 1 Replies
View Related
Dec 20, 2004
Is it possible to create a macro in Access that opens up a word document from a shared drive? Within Access, I don't see a macro available to do this.
View 1 Replies
View Related
Jan 3, 2013
I have a form which calculates many fields in the form. However, the issue that I have is how to use a different field in the query than the field that is shown in the drop down combo box in the calculation of an expression.
View 4 Replies
View Related
Oct 10, 2013
I am working on updating another person access database, how to add a calculated field. I need to add a field to an existing table to subtract the date listed in one field from todays' date. It would need to return the answers in days
ie
10/10/13 - 10/1/13 = Return answer of 9 days
I have been told about DateAdd but I cant find these date function in the option of expression builder.
View 3 Replies
View Related
Jun 23, 2015
I have a calculated field in a query. The calculation only works if the fields have values. This is what I used a the calculation:
PrimaryShareTotal: [Primary1Share]+[Primary2Share]+[Primary3Share]+[Primary4Share]
I tried using the Nz command
PrimaryShareTotal: Nz([Primary1Share]+[Primary2Share]+[Primary3Share]+[Primary4Share])
but this doesn't work.
View 1 Replies
View Related
Mar 3, 2008
Environment: Front-end Access application developed in 2003, with back-end database in 2003. Being deployed in a mixed 2003/2007 environment. Users with Office 2007 will have to open and use the 2003 Access application without converting it to Access 2007.
Problem: When I take the Access 2003 application and open it with 2007, some forms give the following error message (whether selecting Open, Layout View, or Design View):
"There isn't enough memory to preform this operation, Please close unneeded programs and try the operation again."
The real problem is some of the most complex forms open in design view, and some of the simplest do not. There appears to be no ryhme or reason to it. Some of the forms are bound and some aren't. Some of the bound forms are bound at run-time with embedded SQL, some aren't. All query objects seems to be fine, when they are used on bound forms. Some forms have significant amounts of code behind them, some do not.
All report objects execute fine with no problems opening any view.
I can open the same file in Access 2003 and everything is fine. I've compacted and repaired the DB in Access 2003, no help when I go back to 2007.
Wondering if some references are off? But I have some really simple forms that can't be opened. I find nothing on MSDN on this.
View 14 Replies
View Related
