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
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
Hi
I'm creating a small stock control system. I want to be able to update stock levels. Simply by entering new amounts of stock when they arrive and adjusting for stock used that day. I've created a form from a query which takes current_stock level and then I enter a value (say 50) and it is calculated (added together). Then i want to save that new value back into the current_stock field?
Can anyone help??
Thanks
Access 2003. I am trying to save a calculated field (Item No) from a form to the accosiated field within the Products table
I have a hidden text box (itemNo) which is bound to the relevant field and a second text box with the following formula
="TV-" & Left(CatShort.column(1),3) & "-" & Format([ProductID],"0000") as its source
What I am trying to do is to provide an item number for each item of stock based on the Category short name (Catshort) and the incremented item ID Number, with a TV prefix.
I am aware that its a bad itea to place calculated field into the table if the calculation is likely to change, however once my calculation is stored it has no need to change.
I have tried the beforeUpdate method within the ItemNo textbox
Private Sub ItemNo_BeforeUpdate(Cancel As Integer)
Me!ItemNo = Me!ItemCalc
End Sub
but nothing happens (in that the table is not updated)
Any way of putting together an incremental item number based on the selected category with a "TV" prefix....
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
On a form there is a sub form.On the form I can only cycle through the records that have been created since the last time the form was opened. I cannot cycle through all records. Is this normal for a form. I assume some code might change this behavior.
More importantly when I look at the sub form in datasheet view I notice the first field of each record is not displayed (saved?) unless it is the very last record created. (This field has a SQL statement as a row source). Is this normal - or are the last 2 paragraphs descriptions of the same thing?
I have a List box set to YES and default value set to YES.I would like to hide this field and when I click SAVE in my form, I would like to save it to my table.I did set the control source to the field that I want but somehow it is not working unless if I click on to YES then save.How do I set it in such a way which will save even without me clicking on to the LIST BOX?I want the default value to be saved when I click SAVE command button.
View 3 Replies View RelatedHelp!!
I have a form created on a query. I created a field to add up the cost of all components on the form and now I need to save it to a field in a table so that I can run a report (my invoice)! Any suggestions??
I have a currency field that is not saving pennies. (cents if you're Americian i suppose) :confused: if i put in pence it rounds it to the nearest pound.
I have tried setting the decimal places to "2", but that makes no difference :mad: :confused:
the table is called invoice
this also happens with the invoice form :mad: (i suspect for the same reason)
The database can be downloaded from:
http://www.tapestriesdirect.com/Garage.mdb (996KB)
or if you can unzip (who can't?!?)
http://www.tapestriesdirect.com/Garage.zip (86.1KB)
Cheers for any help
Graeme
I have a form that has a bunch of project information and scrolling buttons at the bottom to browse by next/last.
Right now the form is sorted by the ID associated with the project, which kinda sucks because they were and are not put in alphabetically.
I discovered if you right click on a field and click "Sort Ascending" or the opposite it works fine but when I open the database it is right back to normal ID sort
I have checked the Data and Other tabs but can't find anything with regards to this as well as I have tried sorting the actual table by name and that changed nothing.
Any help would be greatly appreciated.
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
hi
i have a form (A) with some unbound combo boxes which generate a subform (continuous) recordsource and the records output
after i filter the subform, i click on a subform record and open another form (B), and close form (A)
i do a bit of editing on form (B), then i click a button to go back to form (A).
is it possible to open the form (A), with the unbound combo boxes values at the same value as when form (A) was last closed, and as such the subform records output as per when the parent form (a) closed
if this is possible i can then have a button on form (B), to go to the next or previous record on the form (A) subform
apologies if this sounds confusing, many thanks in advance
I'm trying to save the contents of a text box into a memo field in a table, but after my code runs i look in the field and it is cut off after a hundred characters or so, and the last few characters are garbled. This is the code:
Code: DoCmd.RunSQL "UPDATE orderheaders SET orderheaders.Comments = [Text127] WHERE orderheaders.OrderNumber = " & GlOrderNumber & ""
orderheaders = table
Text127 = text box
GlOrderNumber = integer variable
Is there a way to do this with only code? (I dont want to do it by binding the text box to the field)
I want to be but to save a space in a field on a table but every time I do it appears to be a NULL and therefore fails a validation rule that I wish to preserve.
View 14 Replies View RelatedI Have a Contacts table, when I use the filter on the field that I want filtered I get what I want, no problem, however when I save that filter and close the table and then reopen it it the filter is gone, how can I save the filter that I set up?
View 2 Replies View RelatedHow 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
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
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 RelatedI 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]))
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 RelatedI 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
is there any way to sum a column or field that has a calculation in them?
View 5 Replies View RelatedIm 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 RelatedMy 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?
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 RelatedI 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.