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 Replies


ADVERTISEMENT

Queries :: Conditional Query To Post Result In Field And Filter Result Records?

Mar 5, 2014

I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg

In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.

View 1 Replies View Related

Queries :: Two Tables To Result In Single Column With Field Name Identifier For Each Record

Apr 27, 2015

How to get this one to display in a single column.

I know how to do this wiht VBA. But, this output will need to reside on a SQL Server View. So I need a SQL language solution. If it can work in MS Access Query, it won't be too difficult to test then translate to SQL Server.

Customer Table with PK Customer_ID.

There are two tables with FK Customer_ID.

1. Table Lease1 - Has 3 Fields - the form code enforces No Fields -or All Fields. The red * indicate a Required field - These 3 are entered together.

2. Table Lease2 - Has 1 field with 0 to Many records.

Goal:

The Type shows up in a single column.

Each Type shows where the data comes from (Lease Type, Surface Owner, Mineral Owner, or Hz Lease Type)

Challenge:

Lease1 table has 3 fields that need to be transformed into a single column.

Lease2 table has 1 field to be appended to the single table.

Then, there is the column that identifies where the data came from based on the column name.

View 6 Replies View Related

Queries :: Cancel Age Calculation Query For One Record

Mar 26, 2013

I have the following age calculation query:

Age: (Now()-[DOB])365

It works a treat! However, I do not want this to continue to calculate if the record has them as deceased - I want it to stop at their date of death.

I have a tick box that when selected indicates that this record has died, and a field where you can enter date of death.

Is there some way that via clicking this button, or by entering a date of death, I can stop the Age Query from calculating for just that relevant record, not all of them? If so, where to place the necessary VBA, etc?

View 8 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

Queries :: 2 Inputs For Same Field - Query Result Not Correct

Sep 4, 2014

I have a query that allows 2 inputs for the same field ( batch no )

if you enter "pco0093" only you get 4 results
if you enter pco0094 only you get 4 results

both have the same part numbers is the results

When I run both together pco0093 and pco 0094 and use the "group" function on the field batch no I get 8 results ( as expected)

if I use the " where" function I expected to get the 4 results but with the " quantities doubling

What I am getting is more than 4 results but 5 with a part that was not on any of the single results

File attached ....

View 9 Replies View Related

Queries :: Allocate A Record To A Place In Table

Nov 25, 2014

I've got a table [Devices] with some number fields that are already in there for another purpose.

In a different exercise I will manually collect more information from a layout drawing and give each piece of information I collect a unique reference(this could be as simple as a PK ref) and attach a "type" and "location" . This will be done randomly in a batch exercise any time.

I need to put the above into access and hold it until I choose to individually allocate these records to the number field that already exists in the [Devices] table.

I would like to add "say" a combo/List box in [Devices] where I could search for the PK Ref which when selected against a number would populate the [Devices] table with previously unallocated information. I would like to keep any combo/List box down to unallocated PK refs to make records easy to find. I guess this a kind of cut and paste exercise.

View 5 Replies View Related

Queries :: Selecting One Record And Place It On Report?

Jun 26, 2013

After running a query is it possible to select just one record and place it on a report or print it? If so how do i go about it?

View 3 Replies View Related

Queries :: Filter Query With DateSerial Calculation Field?

Nov 21, 2014

I have a query with a DateSerial Calculation field that I would like to filter the query by. The DateSerial calculates the same day of every year (5/31/"YYYY"). When I try to add a criteria sort to this field, I get a data mismatch error. Here is the code: ThirdMay: DateSerial(Year(DateAdd("yyyy",3,[LastDayYear])),5,31).

How do I get only dates due in 2015 to show? I have tried all the standard date criteria to no avail.

View 1 Replies View Related

Queries :: Field Is Empty - Query To Update Table But No Result

Feb 26, 2015

I have a created a query which I want to update a table. The field is called DTP. I have the Workingdays2 module in place as well as the Holidays Table. The dates (date only) are called Time in and Transaction date. I want the table to update records only if the entry in Completed is Yes. I have attached screenshot of what I have in query. I am no good whatsoever at VBA so I used the Query design view. It shows how many records will be updated (which is a valid number) and shows me which field will be updated. However when I run the query and then opend the Table the DTP field is empty.

View 2 Replies View Related

Queries :: Can Place Criteria In Calculated Field?

Jan 27, 2015

Can I place a criteria in a calculated field?

[SellingWgt]*[SellingPrice] is ok but only
if [SellingUnits] = "lbs" or [tblSellingUnits].[SellingUnitsID] = 1

View 7 Replies View Related

Queries :: Exporting Currency Field Without Decimal Place?

May 1, 2015

I need to export a currency field to include the pence but without the decimal place, is this possible?

It also needs to be 11 characters wide.

I have tried a number of formats and can get the decimal point removed but I am not getting the pence displayed.

View 5 Replies View Related

Queries :: If There Is No Result In Query Need To Have Default Result Zero

Oct 12, 2013

I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.

View 5 Replies View Related

General :: Calculation On Form - Force 1 Decimal Place At All Times

May 7, 2014

I'm using a calculation on my form that subtracts one number from another and stores the difference between the two in a 3rd field. This works correctly but I want the difference to show 1 decimal place at all times.

So for instance if subtract 35.1 from 35.2 I get a difference of "0.1".

But if I subtract 35.0 from 36.0 I get a difference of "1".

The first case is fine but I need the difference to be "1.0" in the second case.

I've set the number of decimal places in the table from auto to 1 and changed the format on the form to a standard number with 1 decimal place. Neither of these show a difference of 1 as "1.0"

How to force 1 decimal place at all times?

View 3 Replies View Related

Queries :: Sum Records And Put Result In Next Record

Mar 27, 2013

I have record like below:

id s q
1 11 11
2 14 15

I want to sum record 1 and record 2 and put them in next record.

View 2 Replies View Related

Queries :: Using Query To Place Single Grand Total On Row Based On Particular Criteria?

Dec 7, 2014

I have a very simple query on an accounts form to show a running transaction history.

Identifying from the TransactionID (shown for display purposes only - normally hidden) three or four postings make up one transaction.

Using TransactionID 10 as an example, I'd like to have a sum of total [Credit]-[Debit] and have the query display on one line (either at the top of £1,429, or at the bottom of £16,995) to identify this is in fact one transaction, having three posts.

Transaction 9 will have obviously have one total, as this is a single post.

11 the same as 10, by having one total Transaction Value either at the top of the row or bottom.

View 4 Replies View Related

Forms :: Edit Record From Query Result As New Record To Another Table?

Aug 22, 2013

I have a form that using "Query A" as data source. The Form need add (edit) a field value before save that Query Result to another Tabel. Is it possible to do that?

The PROCESS simply like below: Tabel A --> Query A --> Form -->Edit value a field -->Save to Tabel B

if it is possible, are there some requirements that have to be fullfilled?

View 2 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

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

Queries :: List Field Result In One Row

May 3, 2013

How can list the contents of one field columns in one row in a report.

Example:
ID: 1
A1
A2
A3
A4

Should be: ID1 - A1, A2, A2, A3, A4

View 3 Replies View Related

Sum Specific Values On Subform And Place Result On Another Subform

Jan 10, 2015

The forms in the code are subforms on a TabControl on a main form.

I have this code which needs to add together only the areas [Area] of records with a FloorNumber field value of 1 or 2 or 3 or 4.

At the moment the code works for one entry of 1 in the form frmRoomDetails. I'm guessing i need For Next or something like that but i don't know and also unsure of how to code it.

Private Sub FloorNumber_AfterUpdate()
If [Forms]![frmSiteDetails]![frmRoomDetails].[Form]![FloorNumber] = 1 Then
[Forms]![frmSiteDetails]![frmFloorsDetails].[Form]![Text8].Value = [Forms]![frmSiteDetails]![frmRoomDetails].[Form]![Area]
End If
End Sub

View 1 Replies View Related

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 3 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

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

Queries :: Copy Result Of Expression In A Field

Dec 5, 2013

I've got these expressions in a query to extract parts of an mp3 file full path from a field and just let the name of the song.

exp: Mid([imported];[exp3]-[exp2];Len([imported]))
exp2: InStr([imported];"")-4
exp3: InStrRev([imported];"")
exp4: Left([exp];Len([exp])-4)

"exp4" is the clean name of the song.

Now how can I update my "song name" field (which is empty) to be same as "exp4" . Is it an update query? If so how can i do it?

View 2 Replies View Related







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