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])))
For some reason i've had to make an update on some old VBA projects of mine. In this update i switch all references of DAO to ADO. I know there are some limits to what i can accomplish with ADO (where i would need to use DAO). Anyways in some of these old projects i use an SQL statement to provide me with a limited number of records which i then modify by iterating through the recordset.
I can't get this to work with ADO. It's as if it always expects me to provide a table in the argument.
In short my question is. Is it possible to change the values returned from an SQL statement using ADO
Code: 'This is the DAO version which is exactly what i'm looking to using ADO strSqlCommandText = "SELECT tblMain.TaskID, tblMain.Heading FROM tblMain WHERE (((tblMain.TaskID)=" & ProcessForm.txtIDValue.Text & "))" Set objRecordset = pubObjDatabase.OpenRecordset(strSqlCommandText, dbOpenDynaset) objRecordset.Edit objRcsToDbTable.Fields("Heading") = ProcessForm.txtBoxHeading.Text objRecordset.Update
This is what i've done and it doesn't work.
Code: strSqlCommandText = "SELECT tblMain.TaskID, tblMain.Heading FROM tblMain WHERE (((tblMain.TaskID)=" & ProcessForm.txtIDValue.Text & "))" Set objRecordset = New ADODB.Recordset objRecordset.Open strSqlCommandText, pubObjDatabase, adOpenDynamic, adLockOptimistic objRecordset.Edit objRcsToDbTable.Fields("Heading") = ProcessForm.txtBoxHeading.Text objRecordset.Update
I can think of some work around like, creating a temp table and insert/update all the records from there or creating an UPDATE SQL statement.
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.
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?
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
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.
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..
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?
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.
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.
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.
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
I have a query that gives me data from a table, and I then export it as text to Excel. In Excel I do some edits, insert columns with special characters as delimiters, then copy it into a JScript data set.
This all takes some time, and has a number of steps that need to be done in a certain order - prone to errors on my part...Is it possible to use VBA to make the changes in the query to reduce the manual steps perhaps even to remove Excell from the process entirely and just save the query to a text file?
The query has 5 fields: TxtProductCode Unique to each item curCurrentPrice The listed price txtHeading The Main description of the item txtDetails Additional details txtVender A name of who has the item
In Excel I change all instances of '"' to ' inch', then insert the delimiter fields, and the final line for the item looks like this:
I have query that runs ok but will not let me edit the data when I run it. I have 4 tables going to that query , if I delete a certain table I can then edit the query.
Is there a way to edit/replace tables in a query after you've copied the query? Basically, I have created a query and would like to copy the quesry. However, I would like to easily change some of the tables within the query. Is there a way to edit/replace the tables?
I have done a query which has calculated fields, but I can't edit the stored fields directly from the query, instead I have to go to the table so as to edit it. Is this the way it works or is something wrong?
SELECT [INSTRUCCIONES DE EMBARQUE].N_OP, [INSTRUCCIONES DE EMBARQUE].[N° FAC EXP], [INSTRUCCIONES DE EMBARQUE].[FECHA CUMP], [INSTRUCCIONES DE EMBARQUE].[DERECHO PGC], [INSTRUCCIONES DE EMBARQUE].[VTO DER EXP], [INSTRUCCIONES DE EMBARQUE].[IMPORTE REINTEGRO], [DETALLE CONTENEDOR].CANTIDAD, [DETALLE CONTENEDOR].[VALOR FOB], [INSTRUCCIONES DE EMBARQUE].[TIPO DE CAMBIO], [TIPO DE CAMBIO]*[VALOR FOB] AS [PRECIO VTA (ARS)], [PRECIO VTA (ARS)]*[CANTIDAD] AS [TOTAL(ARS)], [TOTAL(ARS)]/[TIPO DE CAMBIO] AS [TOTAL(USD)] FROM [INSTRUCCIONES DE EMBARQUE], [DETALLE CONTENEDOR];
I have a table structure that I guess could be called a dual one-to-many-to-one, as can be seen in the attached Relationships screen print. Customer loan files are maintained in an AllFiles table, tied to the Customers and Loans tables through the CustomerFiles and LoanFiles tables. Users access the data from an unbound form called FileProcessing, where they can select a customer, and a loan for that customer. The files linked to that customer and loan then appear on a form called Files. However, I need the data on Files to be updateable, and I'm having a hard time coming up with a Files datasource that can be updated. The SQL that retrieves the needed data is:
Code: SELECT AllFiles.* FROM (AllFiles INNER JOIN CustomerFiles ON AllFiles.FileNum = CustomerFiles.FileNum) INNER JOIN LoanFiles ON AllFiles.FileNum = LoanFiles.FileNum WHERE LoanFiles.LoanNum=[Forms]![FileProcessing]![tbLoanNum] AND CustomerFiles.CIFKey=[Forms]![FileProcessing]![tbLoanCIFKey];
I tried creating another query that just had the AllFiles table linked to the above query, but the data still couldn't be updated.
I've created a query where I am searching a database of job titles for contacts that are considered senior management.
One criteria I've entered into a query is as follows:
Like "*Vice*"
Which is meant to identify anyone with "Vice" in the title. The problem is I have noticed this also returns records with "service". So can I edit this query to specify that the "V" must be capitalized? I think this would serve.
I have 2 tables (tblItemsQB and tblItemInfo) that have a 1 to 1 relationship linked by the "PartNumber" field. The tblItemsQB table contains a description field and 2 price fields (this table is downloaded from Quickbooks). The tblItemInfo table contains additional fields with information on the part that isn't contained in Quickbooks, like OrderType, Phase, SubSystem.
I import the items from QB into tblItemsQB and then do an outer join with tblItemInfo to link all the information for the part. If I have created a new part in Quickbooks, when I do the outer join (I include all records from tblItemsQB) I can see the new part but since the part number doesn't exist yet in tblItemInfo, I can't add the information to the fields in that table.
What is the easiest way to create the new PartNumbers (those that exist in tblItemsQb but not in tblItemInfo) in the tblItemInfo table so I can add the other information to it?
Hi have a table tblImportLearnerFunding with the fields [LCCode], [Date] and [Total Learner Funding]
I want to create a table to see the funding generated for each LCCode in custom date ranges: LCCode, DateRange1, DateRange2, DateRange3
I can almost get it to work using the crosstab query wizard, but I need to specify the date ranges. Instead of Jan I want to sum the funding in the date range 01/01/2005 to the 28/01/2005 and so on.... Does anyone have any clues?
Here's the Crosstab SQL:
TRANSFORM Sum(tblImportLearnerFunding.[Total Learner Funding]) AS [SumOfTotal Learner Funding] SELECT tblImportLearnerFunding.LCCode, Sum(tblImportLearnerFunding.[Total Learner Funding]) AS [Total Of Total Learner Funding] FROM tblImportLearnerFunding GROUP BY tblImportLearnerFunding.LCCode PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
I have a query that selects records based on certain flags in each record. I can view the record in datasheet view, but I need to be able to edit the records selected by the query using another form. Is there any way to automate this process?
I am trying to create a VB script to automate a mailing based on several query result sets from access. I have gotten to the stage that the output is correct but have a problem with the 5th and 6th record set query as they only return one record (When in fact there should be at least two for each).
I don't really understand why this is happeneing as the SQL is exactly the same as in the 2nd record set - which works perfectly. Also I've tested the SQL directly in an access query & there are no errors in the formatting that I can see... correct number of records returned.
Code: Public emailaddress, ccaddress, Subject, body1 As String Public baserow, toprow, countnumberofrows, emails As Integer Public tempdir, projectlistdir, WBPATH As String Option Compare Database Option Explicit
