Modules & VBA :: Subtraction From Last Record
Nov 25, 2014
What i am looking for is i am having a field in query "DEP" which is showing the asset current depreciation as per formula given. I have a problem that the depreciaition is continued on initial cost for all years which i dont want like if it depreciate asset of cost 1000 @ 10% so the figure is 900 for the first year and on second year it should start depreciating from 900 @ 10% so it would be 90 then so on..What i need is to start subtraction last year depreciation
View Replies
ADVERTISEMENT
May 19, 2005
Hi all,
I was wondering if somebody could help me with a query I'm struggling to write. I have written a query which shows customers who have booked capacity of my company's product. I have written another query which shows customers who have bought volumes of the product. I want to write a query which shows the remaining capacity which can be purchased by customers (e.g. capacity - purchased) but the query I have written for this excludes any customers who haven't purchased any product. Note I do not want to update the tables, I just want to take a view of the capacity position of our customers.
Example:
Booked Capacity
Customer A = 1000
Customer B = 4000
Customer C = 3000
Product Purchased
Customer B = 1000
At the moment my query gives following result:
Customer B = 3000
What I want it to do is:
Customer A = 1000
Customer B = 3000
Customer C = 3000
Any help would be much appreciated!!!!
Ste
View 1 Replies
View Related
Jan 3, 2006
Hi !
I have big problem in putting subtract function in sql query
i have 2 tables
In the first table i make "sum of amount" in each "category_name". In the second table i have 2 columns with the "category_name" and "available_amount".
I make query with join on "category_name" and i want to get like this:
"category_name", "sum of amount", "available_amount","available_amount"-"sum of amount"
I've tried to use "sum of amount" AS a1, "available_amount"-"sum of amount" AS a2 GROUP BY a2
I get "null as boolean in statement" or "column not found a1" errors....
Can anyone help me with this?
Thanks in advance!
i have to make this in a query.
View 1 Replies
View Related
Mar 19, 2008
Ok, I always post at the end of the day because by now I've fried my brain thinking and I have simple problems that I can't wrap my mind around.
I need some light shed on this for me,
2 Tables
Table1:
ItemID
NumberSold
Table2:
ItemID
InventoryAmt
I have a Select query, I have a relationship drawn between the ID's. I forget now, Straight line with an arrow head from Table2 to Table1.
All I need is every item in Table 2 to have it's Amt subtracted by NumberSold.
My expression [InventoryAmt] - [NumberSold] works for as long as I have an entry for it in Table1. If there is no record of this item ever being sold it gives a blank value. I would like that value to be the InventoryAmt, since X - 0 = X.
How do I explain to my query that if no record is present in Table1 to Subtract from Table2 just use Table2's value?
This has to be easy and I'm sorry to start a thread over it. It's almost quitting time and I'm banging my head off this, heh.
View 1 Replies
View Related
Jun 1, 2005
I have financial table which contains list of End of year revenue titles. Since this combo box was suppose to be part of the master table so I made a joint query. Now there is one problem arising. Table and query and even the form is working fine. But in that combo list there are several titles and 3 of them are Assets, Liabilities and Equity. Equity has to be equal to Assets-Liabilities. And the values are being fed into Borrower In house column. I tried everything, but for some reason its not working for me. It wont subtract the values. Any suggestions. I hope I didn't confuse you guys. Thanx in adv.
View 12 Replies
View Related
Dec 2, 2004
I have four feilds and im trying to subtract them in a query
This is what the SQL looks liek for two feilds. but i have 4, i was gettign and error when i added other feilds
Format(Val([CFR_ALLOWED])-Val([CFR_DEDUCT]),"0000000000") AS [Form Total],
im trying to subtract these feilds
Here are my feilds
Allowed-deduct-other-Coins-Cob
Much help needed thanks
View 1 Replies
View Related
Oct 23, 2014
How can I format the output of subtracting two times as a time value. For instance 10:00 am - 9:00 AM = 1 (where I want to show the output as "1").
View 7 Replies
View Related
Aug 18, 2006
Hi All,
I am on a mission to get this out but having muchos problemos! Hope someone can hand me a little tip or pointer.
My table has two coloms:
Date_stamp(datetime), product_code(text(25))
I need to show this data in a report with a third colum that shows the difference in seconds between each record. IE how long it took for the next date-stamp to occur.
Can anyone help.
Thanks in advace
View 1 Replies
View Related
Oct 25, 2013
Is there such a calculation in REPORT wherein I can have the Running Difference of several numbers?
Example is several Kilometer reading of a vehicle:
18716
18731
18742
difference of red color numbers: 15
difference of 2nd red color number and blue color numbers: 11
total: 26
I don't even know if my calculation is correct ...
View 2 Replies
View Related
Jun 28, 2013
When we browse through records in a subform we store the records in the database.When we want to delete a records for example the third record from the five records always the first records will be deleted. How can we delete the records where the cursor is at? When we are at the third record and press the delete button the third record from the list in the subform should be deleted.
Code:
Option Compare Database
Dim FocusBln As Boolean
Private Sub Identificeer()
Me.[Datum Aangemaakt].Visible = True
Me.[Datum Aangemaakt].SetFocus
If Me.[Datum Aangemaakt].Text = "" Then
[code]...
View 11 Replies
View Related
Jan 26, 2015
i want to be able to create an On Click Event when pushing a command button that will run an Update query to update a record and after it has been updated that specific record will pop up on a Form and be displayed. i know a different way is to run the Update query and then have it displayed in a Select query but i want it to be displayed on a Form instead. is it possible?
View 4 Replies
View Related
Aug 19, 2013
I have two forms both with separate tables
(1) Register and
(2) Payments.
One of the common denominators between them is the URN which is auto-populated as it is an auto number field. My issue is that when I want to add a new record to the payments table using the forms (I can get to the payments form via the register form), I want to be able to identify the record that I am currently viewing within the register and auto populate the URN field with the same number. This is what I have done so far,
Option Compare Database
Option Explicit
Private Sub AttachPaymentDetails()
Call PerformInsert("tblFinancialBudget", "frmFinancialBudget")
End Sub
[code]....
View 5 Replies
View Related
Oct 23, 2014
I would like to highlight record when user will click Record Selector in the continuous form. How to do it?
View 1 Replies
View Related
Sep 8, 2013
I have a form set to the table Client Information with a subform set to the table Event Information. Client Information has a one to many relationship to Event Information.
There is a button that deletes the current record in Client Information--also deleting the related records in Event Information--then closes the form. The code works fine but a blank record in Event Information is apparently being created before the form closes.
Here's the VBA that I'm using:
Private Sub CmdDelReturn_Click()
Dim CmdDelReturnMsg As String
CmdDelReturnMsg = MsgBox("Delete event & client then return to front?", vbYesNo + vbDefaultButton1, "Delete and Return?")
[Code] ....
It's not a big deal because the button won't be used often and I can manually go into the table to delete the blank record. But if there's a simple solution to prevent this that would be nice.
View 3 Replies
View Related
May 6, 2015
I'm trying to make a form that shows what the last record was next to the empty space where you enter a new record.
This is so the user knows that what they are entering is roughly in line with what has come before.
So for example if I was recording temperature every May, I would like a form that has a field called temperature and next to that field I would like to see last year's temperature.
Records:
Date | Temp
2014 | 20.5
2013 | 18.5
2012 | 19.0
2011 | 22.7
2010 | 15.2
So when I enter the record for 2015 I have a box that says: Temp and next to that box is "Last year was 20.5" or something like that.
View 2 Replies
View Related
Jun 23, 2015
I have 5 subform named "bene_subform1", "bene_subform2", "bene_subform3", "bene_subform4", "bene_subform5", and i would like to know if it's possible to msgbox the record from the beginning to end of record in sub-form 1, loop thru all the 5 subform via VBA?
View 14 Replies
View Related
Dec 12, 2014
What I've done is setfocus from another subform to this subform, and I want to add a new record. I've got my code mixed up somewhere along the line as it's saying "the command or action 'RecordsGoToNew isn't available now.I'm trying to setfocus to this form, create a new record and setfocus to a field within the focussed form.
The code I'm currently using is...
Forms!frmtopline!frmTopLineSub.SetFocus
RunCommand acCmdRecordsGoToNew
Forms!frmtopline!frmTopLineSub.Form!TransactionDat e.SetFocus
View 2 Replies
View Related
Jul 18, 2013
I have written the following code. I want to change the status of my order when reminaing products shipped is equal to zero. When I run this code I get error " Cannot update. Database or object is read only". I think that this error is because that I am using the front end of the database.
Code:
Private Sub Form_AfterUpdate()
Dim dbs As DAO.Database
Dim rstTest As DAO.Recordse
Dim strQuery As Strin
strQuery = "SELECT OrderingT.Order_ID, OrderingT.UnitsRequested,OrderingT.OrderStatus, ([UnitsRequested])-Count([Product_ID]) AS [The Remaining
[code]....
View 12 Replies
View Related
Apr 21, 2015
I need any code or way that whenever any field of a record according to unique ID changed the code must save the changed field name and the current date in a specific field in another table (first field store the ID and the second one detail about changes) with add record mechanism. Suppose I have a table about the information of students with the name std_info and another info_report and when any changes make to the any field of std_info the field number and the unique ID to the table info_report. I want to use this system to record which user make changes to which records.
View 1 Replies
View Related
Mar 6, 2014
I have the following code.
Code:
Dim ctl As Control
For Each ctl In Me
Me.Assigned = Me.Combo55
DoCmd.RefreshRecord
DoCmd.GotoRecord , , acNext
Next ctl
I keep getting an error that says cannot go to the specified record. How can I go about fixing this?
View 14 Replies
View Related
Mar 11, 2015
I'm trying to retrieve the value of a record in a table. Why doesn't this work:
tabName = "Plan Data"
Dim db As DAO.Database, rst As Recordset
Dim strQryWhole As String
Dim dbStatus As Integer, rcnt As Long
[code]....
View 5 Replies
View Related
Jun 28, 2013
I have a table tblExtInc that has multiple records with fields
Code:
StartYear EndYear Amount Inflation Freq Source
2 8 1000.00 .02 12 SS
6 10 2000.00 .00 12 Pension
Here is the code I have to Update a table tblExternalIncomeSchedule
It works fine and updates the table for the first record.
How do I move to the next record StartYear 6 thru EndYear 10 and write that data to the same table without overwriting the data from the first record?
It should add 5 more records to the table
Yr 6 2000
Yr 7 2000 (since inflation = 0)
Yr 8 2000 (since inflation = 0)
Yr 9 2000 (since inflation = 0)
Yr 10 2000 (since inflation = 0)
Code:
Function FillExternalIncomeSchedule(nClientID As Long, nProposalId As Integer, nProposalYears As Integer, nRetireAge As Integer)
Dim rst As Recordset
Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("", "DELETE * FROM [tblExternalIncomeSchedule] WHERE [ProposalID] = " & nProposalId)
qdf.Execute
[Code] .....
View 3 Replies
View Related
Apr 3, 2014
How do I delete only one record in a table that has duplicate records using SQL?
Code:
DELETE tblTemp.Fruit, tblTemp.[Country of Origin], tblTemp.Qty, tblTemp.Date, tblTemp.Currency
FROM tblTemp
WHERE (((tblTemp.Fruit)='Fruit') AND ((tblTemp.[Country of Origin])='Country of Origin') AND ((tblTemp.Qty)='Qty') AND ((tblTemp.Date)='Date') AND ((tblTemp.Currency)='Currency'));
View 10 Replies
View Related
Sep 7, 2014
I just want to compare the current record's value in a field with the previous record's value in a field.
Like
If recordset.fields("Field1").value <> recordset.previous.fields("Field1").value
otherwise any code like this:
If recordset.fields("Field1").value <> recordset.position(recordset.absoluteposition-1).fields("Field1").value
View 11 Replies
View Related
Jan 23, 2014
I have a small database with several forms. One of them has a command button to open a new form (which also contains a continuous subform) and go to a new record in the form. This is the code behind it:
Code:
stDocName = "Main Data Entry"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.RunCommand acCmdRecordsGoToNew
This database has been in use for quite without problems a while but now I am doing a few small changes and cleaning it up of data to start over.The code above gives the error "The command or action "RecordsGoToNew" isn't available now" yet it opens the form just fine and data can be entered. This error only happens when the first record is entered; it does not show up when entering the second or subsequent records.
View 8 Replies
View Related
Aug 13, 2013
I have a form based on a query and have written code to display a msgbox if a duplicate entry is inputted in the NHS_Number field in the form. I have also added code "me.undo" to clear the form so that it is not saved. I am looking for a piece of code that will display the duplicate record. Here is my code thus far:
Private Sub NHs_Number_BeforeUpdate(Cancel As Integer)
Dim dbs As Database
Dim rst As DAO.Recordset
Dim x As Integer
[Code]....
View 4 Replies
View Related
