Subtraction Issuse 4 Feilds

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 Replies


ADVERTISEMENT

Accessing Feilds In A Table

Jan 13, 2006

Ill start from the dawn of time

right all tables are like a 2d array so does any body know how i can access a field in a row of a table ie like in most languages it goes a bit like this ArrayName(1,1) with ArrayName being the name of the table and that access the first field of the first row. im new to Vba and access 2000 but all i want to do is access the fields in a column of a sub-form displayed as a table add them all up and place the answer in a text box on the main form stick all this in a function so i can run it inside a macro

q1 is this possable
q2 how do you do it

thanks in advance john

View 5 Replies View Related

Autosum For Highlighted Feilds

Sep 21, 2006

Can anyone please help or advise where I can look for help on this problem.
I have a subform which shows all my outstanding orders. Below it is a calculated field which shows the total weight of all the orders. (Steel Beams)
I would like to be able to highlight the weights on certain orders & show total weight for only these orders. (Similar to the way it can be done in excel)
I have been trying to do it via a query with a Yes/No check box & when I check the orders required the total weight changes. But I cannot get it to work properly as this puts a Yes in the field & the record disappears.
I only want it for a quick reference to save doing mental summing up so don’t want to change or add anything to a field to get answer.
Can anyone give me any ideas please?

Tks

View 4 Replies View Related

What Feilds Should Be Editable From Website?

May 10, 2005

Hi every body. I have an access database that u can see the tables and their reletionship in the link i posted.
I would like to web enable this db and would like to get some suggetion on what fields should i allow to be edited and what records should i put delete option for? In order to keep the refrential integerity of the acces db.i be happy to get some feed back from u guys.Thanks

http://i5.photobucket.com/albums/y180/method007/dbreletionship.jpg

View 2 Replies View Related

Subtraction Within Queries

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

Subtraction In Query?

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

Query Subtraction

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

Generating A Numbering System Dependant On Other Feilds

Apr 20, 2007

I have used and maintained Access databases in the past but this is my first experience with building a new database. I started by borrowing every Access book my Tech department has on hand and going through them. I have gotten to the point where my tech department can not help me because my question is beyond their own knowledge. I have searched through many different website’s forums and have failed to find what I am looking for. It very well may be out there and I am using the wrong terminology in my searches. The most important piece of information to be stored in my entire database is of course the one that I am having the hardest time figuring out how to set up.

I have a table called “Document Index” which will be a master log of every document that passes through my department. This table has the three fields which are important to my problem/question.
1. The “LOC” field represents the location at which the document was logged. The “LOC” field has a working validation rule that only allows a “D” or “S”, representing the department head’s office or the site office.
2. The “DUO” field value represents the three digit unique number assigned to a project by another department (accounting)
3. The “ID Number” field is a unique number we will assign to each document that is this table’s primary key. It is also where my problem sits. The “ID Number” format has been decided upon by my boss and the rest of the office has already begun stamping these ID Numbers onto every document (which I will have to go through and enter once the database is complete). I am stuck with the format as it is shown in the example below.
“ID Number” format is: value of “LOC”- value of “DUO”-number

I know what I want the field to do but I have no idea how to turn my ideas into the proper code to see if it works. So here is my idea of a solution with example values entered.
A.Fill in form for “LOC” and “DUO”
B.At this point Access would run a behind the scenes search and return all ID #’s that had a that “LOC and “DUO”
C.At this point Access would run a behind the scenes search through only those records found in step B to find the highest “ID Number” previously assigned
D.At this point Access would make a calculation: adding one (+1) to the “ID Number” found in step C
E.At this point Access would insert the result found in step D into the “ID Number” field of my current entry (which until this step the record only contained the “LOC” and “DUO”)

For example: if my first 4 entries had this info:
LOCDUOID NumberDocument Description
D410D-410-1 Wendy’s Ransom Note
S410S-410-1 Priate's Demands
D415D-415-1 Cinderella’s Birthday Invitation
D410D-410-2 Peter Pan’s Response to Ransom Note

Then I complete step A by typing into the form:
LOCDUOID NumberDocument Description
D410D-410-1 Wendy’s Ransom Note
S410S-410-1 Priate's Demands
D415D-415-1 Cinderella’s Birthday Invitation
D410D-410-2 Peter's Response to Ransom Note
D410

Access would execute step B finding:
LOCDUOID NumberDocument Description
D410D-410-1 Wendy’s Ransom Note
D410D-410-2 Peter's Response to Ransom Note

Access would execute step C finding:
LOCDUOID NumberDocument Description
D410D-410-2 Peter's Response to Ransom Note

Access would execute step D finding:
D-410-2 + 1 = D-410-3

Access would execute step E:
LOCDUOID NumberDocument Description
D410D-410-1 Wendy’s Ransom Note
S410S-410-1 Priate's Demands
D415D-415-1 Cinderella’s Birthday Invitation
D410D-410-2 Peter's Response to Ransom Note
D410D-410-3
Then I would finish entering the information for that record.


I would appreciate any help you can offer, even if just pointing me in the right direction or correct terminology for my searches.

View 5 Replies View Related

Current Week Query In 2 Date Feilds

Nov 17, 2006

I have a query with below formular which return's all records between Sunday and Saturday

Between Date()-Weekday(Date())+1 And Date()-Weekday(Date())+7

I can get this formular to work when it is looking at one date feild. (Date Received). I would also like it to look at the (date completed) feild so I get all records entered & completed in the previous week.
I have tried putting same formular in the OR critiria as well but cannot get it to show all records at same time.
What am I doing wrong ???

Tks

View 1 Replies View Related

Field Subtraction Problem

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

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 4 Replies View Related

Formatting Subtraction Of Two Numbers

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

Yes/No Feilds Display In Numeric Format When In Union Query

Jan 25, 2008

Hi all,

I have looked everywhere for a solution to this and found none. Its an annoying issue which I would like to get around.

Here is what happens.

I have a union query that gathers rows with different criteria together. The query works execept that the yes/no feild which is set to display as "Yes" or "No" is displayes as 0 for No and -1 for Yes.

I understand that the numeric values are the underlying values of yes/no feilds in access, however they are displayed as yes/no in all my queries and reports execept my union query.

I will include the union query here in case.

The following code is a function that I use to build the union query on the fly based on some parameters. If you are asking why I am doing it like this is becasue I need the rows that have a value for the feild schFeilds(0) to be on top of the final results followed by the rows that don't have any values for the above mentioned feild, and I want them sorted. I found this method to be the only one that gives me the result in the order I need.

' ----------------------------------------------------------

Public Function makeMainPanelQry()
On Error GoTo Err_makeMainPanelQry

Dim db As Database
Dim qd_window As QueryDef
Dim qd_nowindow As QueryDef
Dim strSQL As String, sql_window As String, sql_nowindow As String


Set db = CurrentDb
Set qd_window = db.QueryDefs("Q1")
Set qd_nowindow = db.QueryDefs("Q2")

strStatus = cmbStatus.Value
strReboot = cmbARStatus.Value

strFilter = makeHostFilter(strStatus, strReboot)

schFeilds = Split(getRSchechuleFeild(), ",")

sql_window = "SELECT " & pbl_ReleaseTable & ".Hostname, " & pbl_ReleaseTable & ".Status, " & pbl_ReleaseTable & ".Excluded, " & _
pbl_ReleaseTable & ".AutoReboot as Auto, " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & ", " & _
HEAT_PROFILE_NODECOM & "." & schFeilds(1) & _
", Technician.FirstName as Assigned, 1 as Priority FROM (" & pbl_ReleaseTable & " LEFT JOIN " & HEAT_PROFILE_NODECOM & " ON " & _
pbl_ReleaseTable & ".Hostname = " & HEAT_PROFILE_NODECOM & ".DeviceName) " & _
"LEFT JOIN Technician ON " & pbl_ReleaseTable & ".TechID = Technician.TechID " & _
"WHERE ((" & pbl_ReleaseTable & ".Excluded) = False) " & _
strFilter & "and " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & " Not Like """" "
'" ORDER BY " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & ", " & pbl_ReleaseTable & ".Status"

qd_window.sql = sql_window

sql_nowindow = "SELECT " & pbl_ReleaseTable & ".Hostname, " & pbl_ReleaseTable & ".Status, " & pbl_ReleaseTable & ".Excluded, " & _
pbl_ReleaseTable & ".AutoReboot as Auto, " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & ", " & _
HEAT_PROFILE_NODECOM & "." & schFeilds(1) & _
", Technician.FirstName as Assigned, 2 as Priority FROM (" & pbl_ReleaseTable & " LEFT JOIN " & HEAT_PROFILE_NODECOM & " ON " & _
pbl_ReleaseTable & ".Hostname = " & HEAT_PROFILE_NODECOM & ".DeviceName) " & _
"LEFT JOIN Technician ON " & pbl_ReleaseTable & ".TechID = Technician.TechID " & _
"WHERE ((" & pbl_ReleaseTable & ".Excluded) = False)" & _
strFilter & "and " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & " Like """" or " & _
HEAT_PROFILE_NODECOM & "." & schFeilds(0) & " is NULL"
'" ORDER BY Priority, " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & _
'", " & HEAT_PROFILE_NODECOM & ".Status;"

qd_nowindow.sql = sql_nowindow


strSQL = "SELECT * FROM Q1 " & _
"UNION ALL SELECT * FROM Q2 " & _
"order by Priority, " & schFeilds(0)


Set qd_window = Nothing
Set qd_nowindow = Nothing
Set db = Nothing

makeMainPanelQry = strSQL

Exit_makeMainPanelQry:
Exit Function

Err_makeMainPanelQry:
MsgBox Err.Description
Resume Exit_makeMainPanelQry

End Function

' ----------------------------------------------------------

I woudl really appreciate any assitance as its my last hope for a solution.

Thanks and regards
Sep

View 2 Replies View Related

Date/time Subtraction Using Previous Row.

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

Reports :: Running Difference Of Several Numbers - Subtraction?

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







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