Math Operations On Values Question
I have created a table that holds the inventory information of some items i have in stock. What I want to do is this:
1.)When a customer orders something, i enter the quantity ordered and hit a process button. When this button is clicked it automatically reduces my inventory count to reflect the reduction
2.)When I receive shipments, i want to enter the information and hit process so that it automatically sets my inventory counts to reflect the addition of the new units
I am having trouble figuring out how to go about doing this. I was thinking of somehow setting up some textfields and then adding buttons that set fields accordingly.
Any help would be most appreciated.
I am in desperate need of some advice. Today I set to work building some math functions in my queries to calculate delays/turnaround times for work, etc.
I have found that where there are null values in my table - the calculation seems to break down.
Let me explain...
In it's simplest form (I have removed some fields and will deal with DATE only, not TIME which is a seperate field) I have the following -
I have the following expressions in my query -
Turnaround: Date_Completed - Date_Received
TimePended: Date_Recommenced - Date_Pended
TimeInactive: Date_Started - Date_Received
Workrate: Turnaround - (TimePended + TimeInactive)
The query calculation will only work when all fields contain a value.
For instance, sometimes the work may be started, then completed. Skipping the 'Pended' and 'Recommenced' steps.
Can anyone tell me what I need to include in my maths for Access to treat a null value in the table as zero? Or any other approach.
Do queries support bitwise operations:
2048+512+128 = 2688
Field1 = 2688 in the table
MyField: ([Field1] And 512) = 512
Jaeden "Sifo Dyas" al'Raec Ruiner
I need help in writing a query that would return the records that are not older than two weeks.
I would appreciate your help in advance. :o
Please help, this error is wreking my head:
I have a VB APP updating a an Access DB, i get this error every so ofter:
-214 (load of numbers) Operation must use an updatable query.
I have given full control to to the directory on which the App and the Access sit..
Is there a way for now...to click on a button on a form and have it direct you to a website? eventually i'll build the forms and calculations and stuff..but for now is there a way to do this?
See what i would like to do is have the client select a certain record and then have them click a button to view the insureds information whihc is posted on the company website..
Edited by: dcpikapp on Thu Apr 27 9:34:12 EDT 2006.
Hi folks. I'm new to this and would appreciate any help. Here is the scenario: I am trying to build two separate forms. First one has the primary key as an AutoNumber. I am attempting to open another form from the main form so that it will automatically insert the AutoNumber from the main form onto the subform. Subform runs off a different table than the main form.
I have an access table that is transfered into excel using the following code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, rptName, outputname
I want to do some operations on the excel workbook after it is created, but I am having problems. I would prefer to do it in access rather than writing separate code in excel to run. Here is the stuff I was trying:
' Dim xlWorkbook As Workbook, xlOutput As Workbook, xlApp As Excel.Application
' Set xlApp = GetObject(, "Excel.Application")
' If TypeName(xlApp) = "Nothing" Then
' 'Excel was not open
' Set xlApp = CreateObject("Excel.Application")
' End If
' 'make Excel visible -- while writing code
' xlApp.Visible = True
' 'New Format workbook with no data
' Set xlWorkbook = xlApp.Workbooks.Open("N:EXCELPremium Error Spreadsheet Template.xlt")
' xlWorkbook.SaveAs ("N:EXCEL" & auditID & " - Prem Error Spreadsheet.xls")
' 'No format workbook with all the data
' Set xlOutput = xlApp.Workbooks.Open("N:EXCEL" & auditID & " - Error Spreadsheet.xls")
' xlOutput.Application.Range("A:A").Insert shift:=xlToRight
' xlWorkbook.Application.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
' :=False, Transpose:=False
This isn't working though. I don't even know if this is the correct way to go about it - I was piecing stuff together from other posts in UA. Any thoughts?
I got one command button on my Data Access Page. When I click the button, it would run a SQL. I want to open a page right after it running the SQL, but the second operation doesn't work at all.
It's probably because the first operation takes too much time, so I need to know when the first operation has completed. Any expert can tell me how to do so?
Thanks a lot!
I would like to be able to delete the active database front end with VBA code.
Of course, I would want to do this WHILE the database was running, so that could be problematic at best.
Is there any way that I can close the database and THEN issue the follow up command to delete said database?
Failing that, can I use VBA to execute a DOS .bat file? (Which could then delete the current database and copy over a new one)
Can I also use VBA to open an Explorer window to a given location?
Something like executing the command "explorer.exe C:Stuff"
Edited by: Maxer on Thu Nov 16 10:58:55 EST 2006.
Hello. This is the BEST help forum I have found and I want to thank everyone who posts replies. You guys are great. I hope I can return the favor someday.
I have a command button on a form that will populate a subform with specified data. However, I need to check the subform's (table) contents, because I can only have so many records associated with a particular revision. The revision id is a foreign key in my subform/table.
I tried using DCount, but I keep getting a "you canceled the previous operation". I believe this is associated with the Exit Sub statement, but I was hoping to see a message box, not an error.
Here is the code giving me grief:
Dim RevCount As Long
Dim RevID As Long
'Set the Revision ID in the OutlierDetail form to match the Revision Data form
RevID = [Forms]![frmOutlierMaster1-25-08]![sfrmARevisionData]![RevisionID]
RevCount = DCount("RevisionID", "tblOutlierDetail", "RevID")
Dim Response As VbMsgBoxResult
If RevCount = 24 Then
Response = MsgBox("You need to add another revision, as this revision already has 24 periods forecasted.", vbOKOnly + vbCritical)
If Response = vbOK Then Exit Sub
'Code to Add the 24 records to the table...
There will be a value in the RevID that matches the parent form's Revision ID. I'm trying to count the number of records in the child table that also have the same Revision ID (foreign key). If there are 24 records with that Revision ID, then I want the user to get a message box that will stop the procedure when they click on its OK button. If there are not 24 records, then I want the procedure to continue and add the 24 records to the table.
Thanks for your help in advance!
..some of the basics of manipulating recordsets through VB.
I have created a .mdb called TEST
I have a table therein called TABLE1
I have 3 fields in the table .. amount1 amount2 result
Would some please give me the "on click" code that would cycle through
these records and add amount1+ amount2 and put the result....in result.
I have written what I belive to be accurate code that works through to the end with no errors,
BUT...the result is not finding its way to the Result field in the table...
Many thanks in advance...
I have an artwork book which I am cataloging. Each page of the book contains 24 pices of artwork.
What I need to do is display a page number to whatever record number I enter into the field.
Example 2: I enter any record number from 1140 and the field will display 48 for the page the artwork is on in the book.
Example 2: I enter any record number from 1153 and the field will display 49 for the page the artwork is on in the book.
Example 3: I enter any record number from 457 to 480 and the field will display 20 for the page the artwork is on in the book.
Thanks for any help I can get on this
I am a wiz at formulas in excel but am not sure yet how to make them work in access. I have a main form into which I put the date recieved of many items for each facility. Next to each of the dates I have entered I want to have a non inputable text block that will look at the date then do the necassary math automatically. Some of the formulas will also contain conditional statements based off of a date, type and rating. Can anyone suggest the best way to do this or point me toward a resource that explains how to use formulas in access. Here are a few examples of the formulas that are already in place in the excel version.
in the above formula C28 is a date entered by the user, otherwise most formulas I need to input are simply (date + 1 year or date + 4 years)
Any advice would be greatly appreciated.
I have a form where the user can paste rows of data they've copied from Excel or from other sources. I've set up a "raw data" table to paste the new rows into, and then have code that will clean it up, perform validation, etc.
I have a button to automate the paste append. All works well until the user pastes a second set of rows. The second time they click the button the rows from the clipboard appear in my form but do not seem to get written to the underlying table. The problem does not seem to occur if I use Edit/PasetAppend, only if I use my button.
My code is simple :
Code:Private Sub cmdPaste_Click()
' sub DoPasteAppend
' call the Paste Append command
' note that if this is called from a button event you may need to setfocus back to your
' data before calling the paste
Public Sub DoPasteAppend()
The button is on the main form and I'm pasting into a subform.
I've searched here and through Google and have found other reports of the same problem but no solutions.
Here's one here at UA. Here's one from another board, And another.
Edit- I've attached a quick sample database where the problem can be demonstrated.
Edited by: Clippit on Tue Aug 7 11:04:55 EDT 2007.
What the best way to perform math functions on data in a table? I have a form and a subdatasheet in it. I want to be able to: Average out data in cells. I.E One field will have a list of numbers and I want to be able to average those out.
The other is that i want to be able to check the previous cell(year) and see if the number has gone up or down?
Many Thanks :)
I have 6 fields that display a price based on a combo box selection
I can get one to subtract from another but not all add up
It should also be a running total so it updates as I pick more options
And how can you get the combo baxes to start out blank they all have
selects already made when I open the form
Thanks I'm fairly new to access
I want to have a query that will subtract a Due Date from todays date and tell me how many days are left/passed in either a positive number or negative just like the DateDiff Function. how do i do this?
Its in the control source of the text box I want the total stored in
=Forms![Estimate Maker]!Tank.column(2)+Deck.column(2)+[Internal Valve].column(2)
The data entry setting did the job thanks much
I am trying to find the total time between 2 different dates for all data, not jsut 1 date. I know how to use the datediff function, but it is just returning the difference between 2 dates, and not summing the total for all entries. Do I need to use a query to assist em in this or is there another function I can combine with the datediff function?
So I just realized that the built in math library is pathetic. Am I missing something or does VBA really require users to define PI, write code for min, max, mean and variance? Obviously these things aren't very hard to code but they are used so frequently that I suspect there might be a better library out there that I don't know about. Does anyone have any recommendations for another library to connect to? The more statistical functions the better.
I am completely new to Access but probably all I will need is a gentle push in the right direction. I'd like to think I'm fairly quick at learning new things. Thank you in advance, here's where I am, (first step anyway):
Table 01 Parts List
ID - name - cost
Table 02 Item List
ID - nick name - description
Table 03 Items to parts relationship (this is where I fall apart)
ID - Item ID (relationship to table 02) - Item nickname - PartID (relationship to Table 01) - Part name - Part quantity.
What I am trying to do:
I resell items in table 02 which are built of various parts from table 01. I need to generate a table that says:
Item 1 is built from 4 of part 1, 2 of part 2, 9 of part 3, and the total cost of parts for Item 1 is x. I want to update the parts list cost on a regular basis and have the item price change reflected.
If I can understand how to make this work, I can ultimately add in labor and overhead etc etc. Baby steps for now.
Here's a copy of my sad little db
I have a form with a combo box named product that is a required field. Say for example, that I wanted to multiply a currency field by a percentage field when product A is selected from the drop down and have the answer stored in a field in my table called income. What is the code to make this happen? I think I need to put something in the code builder under "on update" but I don't know what. Thanks again.
Access has some statistical function built in, but does anyone know of a 3rd party math/statistical object library?
Has anyone used a 3rd party math library with access before?
I'm mainly interested in statistical functions like mean, mode, median, variance, covariance, etc...
I have created a text box with this expression:
=tblCompressor![Annual kWh]-tblCompressor![Consumption kWh]
It is the Control Source (on the data tab). It is a simple subtraction. When I open the form, the text box contains "#Name?" instead of a numeric value. All variables in the above expression are chosen from within the expression builder.
Why does this not work?
I built an order form for my company in Forms ( here's a screenshot of it in design view) and I need to write a simple expression to total up the price in each row. I don't need the information saved anywhere so I left the fields unbound, but is it even possible to write expressions for unbound fields? I just need the quantity row * the price each row so I get a total. I know how to get it to work with the fields linked to tables with a relationship but if I do that the form looks messy, and we need this to send out to customers. Any thoughts?
Edited by: Stellae on Fri Dec 21 12:28:33 EST 2007.
I am trying to display total time in system for 3 different areas on my report. The part I am working on is; I am trying to get the total time in area a based off of a date. So my thinking behind this was write an expression that is liek this;
=Forms![Engineer Info 2]![Completion Date]-Forms![Engineer Info 2]![Date Assigned to Drafter]
I am putting this under the control source for a text box, is math thinking wrong or is there something completely different that I need to be donig. If anyone needs more information on this please let me know. Thanks!
I need to know how to make two or fields relate mathmatically in another field. For example, I need Field C to represent the totals of Field A and Field B. Specifically, what I need is:
Fields A, B, and C to be tallied in Field D. Then I need Field E to be:
((Field D) + (Field C X .91)) X .15
All I really need to start figuring this out is a point in the right direction.
Also, I need to know how to make Field G automatically represent the value as entered in Field F. In other words, if I enter a value $10 in field F, further on down the form Field G will automatically show a figure of $10 too.
I am working on a project and have some questions. I want to create a form that will eventually be saved as a web page where the user will select a product and two text fields and one number field will flood into the form. The rest of the fields on the form will be data entry and will be stored in a query, along with what is flooded from the product table. I can then create a report from the query that shows all of the information based on the math in the query. For example, one of the fields the user will input is balance. Depending on the balance they enter, the value equals the balance * the number field that floods in from the product table. In other products, the math is number of units sold * $.12. Other products have a flat fee earned.
I have the project started but it is far from perfect. I want the product table to be it's own table. When prices or fees change, I can update the table and the new price will be refected in the calculations on the user end. How do you do this without changing all of the existing records already saved?
How do I get the calcualated value that shows on the form to store in the query? It's an unbound text box right now that multiplies the balances * the number from the product table. In order to get a true calculation on the report, I need this value in the query. The other math formulas are controlled by case if code and unit rather than balance field show on the form if they select this product. Regardless of the final value, I need it to store in the same field in the query for each record.
Please help if you have any ideas thanks.
I created a DAP from a form and i noticed that when i launch the DAP my math calculations i had in their were no longer functioning. When i use the form, it works properly, when i input data via the DAP and open the inputted data in the form it shows up. However it will not carry the needed calculations over to the DAP. Is there something that has to be done special when saving to a DAP file to carry all that over or am i doing something wrong. Any help would be apprecaited
I am trying to make a report that pulls information from two queries. On one query it is pulling a count of total parts. On the other report it is pulling the count of completed parts. When I try to add both of these to a report wizard it says it cannot link them.
Further more, I want to be able to display both of the numbers and then in a box below it do the computation of (total parts/completed parts) * 100. is that possible?
I currently have a spreadsheet that when I enter data in Excel, am I able to create a Table where the info in Excel will populate in the Access Table? If so, how do I go about doing that. I can do it in Excel, but I am trying to avoid adding all these formulas and locking the cells.
Hi guys hope you can help
I have a text box for each day of the week in a form and numbers are entered into them (but sometimes they need to be left blank i.e. not 0). I am summing these values for a total in the footer and get an error (it works if there is a number in each txt box but I some need to be left blank)....
I have tried the control source of the total in the footer to be...
...this gives an error.
...this works if a number is in each text box (not possible)