Save Result Of A Calculation Or Function Into A Table

Apr 1, 2006

Hi there,

I've read through the forums on saving the results of a calculation into a field is a bad idea. I somewhat understand the reasoning for it. But I don't think such situations arise for everyone.

But I have gone with the suggestion and created an updatequery, which simply goes through and updates the calculated value into a table. Now each time the updatequery is run its asking for permission as to the fact if I'm sure I want it to be run. How do I turn this option off. And should I call this updatequery afterupdate or beforeupdate?

Thanks

View Replies


ADVERTISEMENT

Forms :: How To Save Result Of DLookup Function (used In Unbound Text Box) In A Table

Oct 17, 2014

I have a form based on query. On form i am retrieving data from another table using DLookup in a unbound text box. So I want to save the result of DLookup function in another field/table on same form.

View 8 Replies View Related

Keep Result Of Calculation In Table

Oct 18, 2004

Hi all.
I have a TableA with 3 fields: FieldA, FieldB, Result. A Form1 based on these fields. User will put data in FieldA, FieldB and calculate in Result. Problem is how to keep result of calculation in TableA?
Thanks

View 4 Replies View Related

How To Save Query Result To Table

Dec 6, 2004

I want to save an Access query result to a Table, but I couldn't find a way. The only thing I can do is save the result to excel or as a query in another file. Any hints? Thank you very much.

View 2 Replies View Related

Write Result Of Calculation Back To Table

Jul 1, 2004

I am sure that in earlier versions it was easy to write the result of a calculated field on a form, back to a table, but I can't find the method in the help file for the current version of Access.

Would appreciate any help.

View 3 Replies View Related

Tax Calculation - Save Total Amount In Table

Mar 16, 2013

We have a form where we enter various amounts and a box where these are added together to calculate taxes. This amount is shown on the screen but we need to save this total amount in the table where it can be used in other calculations in other forms

The control in the box looks like this:

=[registration fee]+[membership fee]+[fee per term]+[additional classes fee]+[costume]+[books]-[discount]+[applicable gst]+[uniform fee]

How can I get the result of this formula saved in another table field?

View 4 Replies View Related

DateSerial Result On Form Will Not Save Data In Table

Aug 23, 2006

I have used DateSerial to calculate a future date in Microsoft Access form, but it wont save the calculated date on a table (I need the calculated date on a table so that I can generate a phone list sorted by dates).

I have tried to use the formula (=DateSerial(Year([StartDate]),Month([StartDate]),Day([StartDate])+21) in Defaul Value, without avail, and while the formula works in the Countrol Source, it wont save it to a table because it wont accept the formula and link together, so that I can do a report, or search on it.

If anyone can help I would be so greatful

Thank you

Nic

View 5 Replies View Related

Edit Result Of IIF Query Calculation

Dec 4, 2007

Hi ..

I have an issue I do not know how to tackel. I have a select query that selects from three tables to calculate the commission for each transaction. The query is working just fine.

My problem is that I need an additional function ... What if I need to charge a special commission for that specific trade ?

I need to ammend the commission based on a figure I input in a text box from the form view.

Example:
- Commission (calculated by the query) is 100.00
- Special Commission (which is a text box on the form) is 80.00
Then Commission field = 80.00

PLEASE HELP .. Please let me know if you have any other suggestion in tackling this problem. Thanks

Query
Commission: IIf([Shares]![Currency]="USD" Or [Shares]![Currency]="CAD",IIf([Blotter]![Quantity]*[Clients]![USD_2]<[Clients]![USD_1],[Clients]![USD_1],[Blotter]![Quantity]*[Clients]![USD_2]),IIf([Shares]![Currency]="GBp" Or [Shares]![Currency]="EUR",IIf([Subtotal]*[Clients]![EUR_GBP_2]<[Clients]![EUR_GBP_1],[Clients]![EUR_GBP_1],[Subtotal]*[Clients]![EUR_GBP_2]),IIf([Subtotal]*[Clients]![HKD_JPY_2]<[Subtotal]*[Clients]![HKD_JPY_1],[Subtotal]*[Clients]![HKD_JPY_1],[Subtotal]*[Clients]![HKD_JPY_2])))

View 2 Replies View Related

Calculation....need To Drop The Decimals In The Result.

May 2, 2008

Here's what I have so far...and it works fine; however, some results have decimal places; i.e., 2.1666666.

I just need the result to be the whole number. I can't seem to get it to come out that way...I've used "round"...but I must not be putting it in the right part of the formula.

TEST: IIf(Year([EndTerm])>Year(Date()),0,IIf((Year(Date())-Year([EndTerm]))/[Renew]<1,1,(Year(Date())-Year([EndTerm]))/[Renew]))


Thank you for any help!
Carol

View 2 Replies View Related

Make My Calculation Box Flash If The Result Is Less Than 2

May 1, 2007

I have a formula box in my form (textbox) called InventLeft that would calculate product in my inventory. I'd like to make this box flash if the result in it is Less than 2..This way it would catch attention to my user to order more product..Is this possible? Thanks in advance..

View 5 Replies View Related

How Do I Save The Data From A Calculation.

Apr 11, 2005

On my form I have calculated fields based on information from my sub forms. I have the calculations all working fine however I can seam to figure out how to save them in the table that my form is linked to as my control source is the calculation. Should I be using my calculations in an area other than the control source?

View 2 Replies View Related

Forms :: Displaying Result Of A Calculation In A Form

Jun 17, 2015

I am creating a driving school database and have four different tables. Student, Instructor, Lesson and Lesson Type.

In the footer of my subform which works out what lesson type the student has taken and from which instructor, I have created a calculation to multiply the number of hours a student does to what type of lesson they take.

=([LengthOfLesson]*[cost]) (This works ok)

I then want to add all of these options together. I have tried:

=sum([OverallTotal]) in the footer (This doesn't seem to work)

and then

=[Booking].[Form]![OverallTotal] to show it in the form

The name of the subform is correct (booking) and name OverallTotal is also correct but I keep getting #error message.

View 3 Replies View Related

Text Boxes Control Source = Calculation Can't Save Sum

Oct 7, 2005

Hello,
I've got a text box on my form header with a calculation in the control source that adds values from other text boxes on my form.
My problem is that since the calculation is in the control source of my text box I can not save the sum of the calculation to a field on the forms bound table.
Is there a way to move this calculation out of the control source and still have the calculation populate this text box with the sum of the calculation, so I can choice a field in which to save this calculations sum.

Calculation in the text box control source=
[Tot40yrcomp]+[Totfelt1536]+[Totfelt3036]+ there will be many more other text boxes added.

Thanks--Any help will be greatly appreciated.

View 1 Replies View Related

Queries :: Script To Do Calculation For Every Record And Place Result As Field In Query

Dec 21, 2013

1. I created a table that contains information about people and their details (mainly numerical info).
2. I created a form containing a command button and a label.
3. I have written a VBA script under the button so that when the button is pressed, the result of the calculation appears as the caption on the label.

My problem is...How do I get the script to run so it does the calculation for every record and places the result as a field in a query.

View 2 Replies View Related

Using Nz Function In A Date Calculation

Apr 2, 2008

Must admit I have always had problems getting my mind around the Nz function and where to stick it in an expression. have googled this one (as I know it is not a common problem) but to no avail

If i have a calculated field

=DateAdd("d",7,[DateSubmitted]) then how do I deal with the possibility that [DateSubmitted] may be blank??

View 1 Replies View Related

How To Return An Array From A Function ? And Handle The Result After ?

Jan 29, 2008

Hi there !

I display some buttons on my Form. I should do an SQL request to know how many and what the button should display. It works fine with a RecordSet.
Now I want to move the code that do the stuff to a function. I read that I can't return a pointer to a RecordSet and I should used a variant with a getrows.

So I try this :

*here is my function

Function get_nom_operation(ByVal cnn As ADODB.connection) As Variant
Dim requetteSQL As String
Dim rst As New ADODB.Recordset

requetteSQL = "SELECT libelle " _
& "FROM operation;" _


rst.Open requetteSQL, cnn


get_nom_operation = rst.GetRows

End Function

* and here is the code that call the function

Dim res As Variant

Set res = get_nom_operation(conn)
Dim i As Integer
'i = 1

For i = LBound(res) To UBound(res)

Set Obj = Me.Controls.Add("forms.CommandButton.1")
With Obj
.Name = "monButton" & i
.Object.Caption = res(0,i)
.Left = 14
.Top = 25 * i
.Width = 60
.Height = 20
End With

'ajout de l'objet dans la classe
Set Ge = New gere_event
Set Ge.CButton = Obj
Collect.Add Ge
i = i + 1


Next

But it doesn' work, and I don't know why...

The error doesn't show where the code is stoped, but only show the call to display this form.

someone could show me some way to find the solution please ?

Thanks a lot.

View 1 Replies View Related

Trying To Get A Form TxtBx To Display Function Result

Nov 30, 2004

It's written a Function which takes an Integer as Arg, runs a Select Statement and returns a String.

Such that the Control Source of the Forms reads....

=ConvertCompanyNumbers([12]) = where [12] is the Name of the field. Not My choice to have numbers as Fields.. but there it is.

Anyway.. I'm getting an #Name? error... also tried [Ctl12] but again same error.

I know the function works, as I can use something like =ConvertCompanyNumbers(12) and it displays the appropriate result.

What is up with this?

View 2 Replies View Related

Rename/Save As Function For Entire Database

Aug 24, 2006

Hi,

I am running an autocompact module in Access97 which will created a 'compacted version' of my database. I then want to rename this compacted version to the name of the original database I compacted. I cannot however find a way of doing this in code.

Basically the idea is to compact every 45 minutes or so, but what my autocompact is doing is creating a compacted version under a different name, and so compacting a copy of the database, I then want to change this compacted database name to the original name and re compact every 45 mins


Can anyone help?

View 1 Replies View Related

Save Function For Adding New Records In A Form

Nov 24, 2006

Hi guys and girls,

I'm fairly new to Access and I'd appreciate some help. I've built a form with buttons to open new forms for that specific record (based on queries as all the data is held in a single table). This works fine until I try to add new records. At the moment the only way I can make this work is to add the new record, shut the original form before re-opening it and proceeding on to the other sections (via the buttons I mentioned).

If anyone could tell me how to get Access to save the record before it opens the other forms I'd really appreciate it.

Cheers,
Franny.

View 2 Replies View Related

General :: Textbox Displaying Result Of Sum Function In Scientific Notation

Apr 3, 2014

I am maintaining an Access 2003 application that is running on Windows 7 64Bit Enterprise OS setup.

I have a form in the application that displays a number of records with each record having a unique id and a field called ClockHrs which is stored as a Number (Long Integer) type.

I have a textbox that displays the sum of the ClockHrs field.

The textbox contains no code behind.

The Control source property of the textbox is set to '= SUM([ClockHrs])'

ClockHrs
10
10
30
15
10
15
25
===
115

The textbox displays the result of the sum 115 in scientific notation as 1.15 +02 .

View 3 Replies View Related

Tables :: Field With Calculation Expression - Round Function

Jul 28, 2014

I create e field with calculation expression and I need to round the results. I insert the following expression:

Round(IIf([A]="N";0;(IIf([B]>0;([B]-[C]-[D]-[E]-[F]+[G]);([H]-[C]-[D]-[E]-[F]+[G]))));2)

I have back the following error message: Could not find field 'Round'.

I do not understand why, because Round is not a field, but a function.

View 6 Replies View Related

Queries :: DCount Function Returns Correct Result But Datatype Is Text

Feb 4, 2015

I am using the dcount function as the example I display below. The problem is that it returns the correct result (i.e. 59) but the data type is text (59 is on the left side) . I need this to be number.

=DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")

View 3 Replies View Related

Creating A File Upload Function Where Selections Save The Document?

Sep 12, 2014

I work in a school where teachers have to archive their assessments and other documents monthly. Currently they email them to a set email address but I was wondering if it would be possible for me to make something with access whereby they can do this. I'd like to make a form where they selected their name and then attached a file, this file would then be saved in an area I would have assigned that persons name to, does this make sense? I want it to be very simple so just a name section from a dropdown then a file upload section and done.

View 11 Replies View Related

If A Make-table Query Result Is Null, How To Have A Default Message Appear In Table ?

Jan 21, 2005

Hi everybody,

Beginner here needs help !

I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?

Thanks in advance !

View 1 Replies View Related

Search Record In Other Table And Show Result On Current Table

Apr 11, 2012

Actually I have a small form of customer details, that i made in excel, the field name mention below,

Customer Details Table
First Name
Last Name
Contact Detail
Address Detail

Postal Code
Last Purchasing Date
Remark

Now i want to make a search form like this

Search Form

Contact Details

& the result is show which I insert the contact number.......

View 1 Replies View Related

Tables :: Multiple Table Data To Save To A Different Table From A Form

Mar 6, 2014

I have three tables: Vehicles; Vehicle Reallocated; and Vehicles Retired. I have a form that runs a query to find all the info in the Vehicles tbl that is not "Retired", not visible in the form. I then have the option to toggle to a Reallocated or Retired form. When i toggle to the reallocated form, i have the like fields in that table (ie Van #, Vin, Make etc) pulling the info from the hidden subform with the vehicle query, so i do not need to fill in repeat data. However, when i add a reallocated date and the new clinic that vehicle is for, i get the record ID for the vehicle reallocated table as expected, but when i save none of the data moved over from the query saves in the record?

How to get all the data on the reallocated form to save?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved