!! HELP -- URGENT !! - Need To Know How To Make Numeric Calculations In Access!!!
Mar 25, 2006
Erm Hi everyone I'm new.
Sorry about the rather striking title, but I have an assignment with the deadline tomorrow and I need to find out how to perform numeric calculations in access with different fields (i.e. adding numbers, minusing etc.)
Well, I had to make a project at school for my GNVQ ICT, I chose the advanced sales database, since its a project we have to add in our own features and find out about them ourselves. Well I thought I'd create one for my company inside school, I wanted to create a database that would print invoices and monitor all the resources we have. The project has to be in on monday!
However all was going well until I came upon a slight problem, I'm not sure how to do calculations in Microsoft Access!
Well basically this is what I'm aiming at, its nothing too complicated really.
I have a table called RESOURCES which keeps track of all of the stock the company has (e.g. how much paper, how many mugs, tshirts etc.). I want the database to be able to automatically take 1 away from the selected resource when a form is used to file a new order.
E.g.
I have a form asking me the details of the design, In the resources table I have set the quantity of mugs as 100, i.e. I have 100 mugs in stock and available.
I use the form and tell the database that I am making a design which will use 1 mug.
The database then goes to the resource table and takes 1 away from the available stock of mugs making the total mugs available 99.
I then the database to be able to do this automatically, when a checkbox is ticked, when the form is submitted I want it to see which text boxes are ticked and take the correct consequent resources from the RESOURCE table.
I have Access 2003, I have attached the file incase anyone needs it, note that the Forms have NOT been made only the resource, customers and orders tables have been created.
If someone could tell me how to do this I'd be extremely greatful, this is very URGENT I have to hand in the project on Monday!!!
I know I'm asking alot but I'd be really really greatful if someone could help me out!!!
View Replies
ADVERTISEMENT
Feb 5, 2015
I have a database I have worked on for the sister company of the place I am employed.I have a field I am trying to make alphanumeric that has been numeric. (PO Number on the main form). I had make it alphanumeric a couple of months ago, but it disabled the Edit Customer Information button on the bottom right of the form to where I cant enter shipping addresses and things like that. It should be able to enter multiple shipping addresses.
I need making the PO Number field button alphanumeric and making sure it doesnt mess-up the Edit Customer Information button. I have attached both versions of my database. The GM at the sister company would also like me to create a Spin button where you can take an old record and keep all of the previous information on it, except it gives it a new Work Order# and you can change the date to something newer.
View 4 Replies
View Related
May 25, 2014
Have a Make table query that needs to create (add) several new fields where each field must be numeric design.
Have tried:
Score1: Not Null - does not seem to work (results in a Binary field)
Score1: 0 - which does give me the numeric field designation but every field in table contains a 0.
Would like to show Blank field (makes data input easier at a later time) but still have the Numeric designation.
View 6 Replies
View Related
Jun 8, 2005
I have an odd problem that has stumped me for several days. I'm working on a form that contains a chart. The chart is based on a query, which is based on another query, which is based on a table. In the top query, I need to put some calculated fields that operate on other fields. But when I try to multiply two fields together, I get Null. I'm pretty confident that the fields I'm operating on are numerical.
The fields of the query should be:
MaxOfEverInSchool
MaxOfSurvivalToGrade
MaxOfEverInSchool
With these being the calculated fields:
MaxOfSurvivalToGradeCum: MaxOfEverInSchool*MaxOfSurvivalToGrade/100
MaxOfCanReadCum: MaxOfEverInSchool*MaxOfSurvivalToGrade*MaxOfCanRea dCum/10000
Any insight would be greatly appreciated--I just can't understand why Access would suddenly refuse to do math.
View 1 Replies
View Related
Mar 11, 2014
I have a text field having data i-e HO-1, HO,2, ACW-25 and so on. The field name is nBadge_num and is Unique. The data in this field is sorted automatically like 1, 10, 11, 12, 13, 2, 3, 4, 5...because this is the text field.
The number on the form is automatically generated, when the user type HO- for example on field exit event. The last number will generate like HO-5.
Code for automatic number generation is:
Dim dbs As Database, rst As Recordset, Response
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Max(Right([nBadge_Num],Len([nBadge_Num])-" & Len(Me.NBadge_Num) & ")) AS MaxNo " _
[Code]....
My problem is when the number is generated it give HO-5 instead of HO-14, How can I sort the numeric part of the field ?
View 2 Replies
View Related
Sep 28, 2015
I have solved the problems getting values on the subform. I have not in getting values on the subform.For instance, on the one titled phone use the formula in the tutorial is:
=[sbfCustomerRoomUse].[Form]![txtTotalPhoneUse]
#Error results when the doc is put into a form mode.
Now when I input each value in the equation above separately.I still get no entry.
For " = sbfCustomerRoomUse" , I get #Error;
and for "= txtTotalPhoneUse", I get #Name.
CustomerRoomUse and txtTotalPhoneuse are from the subform that was dropped into the customer form in a previous step. It shows that explicitly when designed sbfCustomerRoomUse on the main that CustomerRoomUse come from a subform. This does not seem to need to be done with txtTotalPhoneuse, and I am not sure why. Neither one gives me a desired calculation result.
View 14 Replies
View Related
Aug 24, 2005
I need to be able to use some functions available in excel in an access 2000 database. These include SUM and IF functions. If any one can help me figure out how to do this any help help wouold be greatly appreciated
View 6 Replies
View Related
Feb 18, 2007
Hi
I am currently setting up a data base for my work and was wondering if there is any way to do the following calculation:
I have a database of automatic doors that are contantly being repaired and i want to keep a record of the accumulated costs involved with this.
I have set up a form to update each door and its accociated repair cost, the problem is when i enter the new repair cost access replaces the current cost with the new and i want it to be added to the current cost so the accumulated cost can be bulit up over time.
I hope this makes sense
Any help would be greatly appreciated!!:)
View 10 Replies
View Related
Dec 5, 2007
Hi
Since this is my first attempt at creating a database and I must admit I am needing a wee bit of help with doing a calculation
I have created a database and need to create a Query I think, to calculate the number of places available the maximum places in each group would be 20 and obviously a count of the people going onto each group would be subtracted from the Max places available this would then need to update the available places . I just cant think how to do this any idea would be a great help.:confused:
View 2 Replies
View Related
Jun 6, 2005
hi to all,
does anyone know the code to close the access program.
That is when i select the close button on the switchboard, the whole of MS access is closed.
Thank
View 2 Replies
View Related
Sep 19, 2006
Dear All, i have a very simple database with only one table.
The table has three values [Start], [end] and [Total].
The [start] & [end] fileds are numerical.
I am trying to get it so that i can enter the values onto a form, the two values in [start] and [end] need to be added together to give the [total] value.
I can get it to do it on the form but i don't know how i can get the record in the table to be updated.
Does anyone know if this can be done and if so could you advise me where i'm going wrong.
Any help would be most appreciated.
View 5 Replies
View Related
Oct 16, 2006
I have created a form in Access to enter data into a Table. But one of the firelds I need to enter into the Table is calculated from some of the data being entered in the form. I currently use an Update Query to go back and do the calculation and then update the appropriate field. What I would like to do is to do the calculation and update the calculated field while the user is entering the data. (Boy I hope this is making sense!)
If it helps understand what I am trying to do here is an example: The user is entering data for a job we produced. Some of that data is NumberOfPages, PaperSize and JobType. But I also need to take those three pieces of info and calculate the number of square inches. Currently I run a function I wrote via an Update Query that updates the SquareInches field in the table. I was wondering if there was some way to do the calculation on the fly and enter the calculated value into the table while all the other data is being entered. I tried writing a little code to attach to one of the field's OnExit event but I seem to be having a problem getting the values from the fields I have already entered.
If anyone has any suggestions I would sure appreciate hearing them. Thanks!
View 7 Replies
View Related
Sep 10, 2015
I am building a database and in it I need to do date calculations. Without too much detail I input a date based on the built in calender from the date/time field and based on that date I need to have the following data returned into other individual fields: 6 months from that date, the days until that date, 2 months from the date input, 4 months from the date input, 5 months from the date input, 60 days from the end date, 45 days from the end date, 30 days from the end date, and 15 days from the end date.
Example: I input "Sep 10, 2015" and I would get "Mar 10, 2016", "181 Days", "Nov 10, 2015", "Jan 10, 2016", "Feb 10, 2016", "Jan 10, 2016", "Jan 25, 2016", "Feb 9, 2016", "Feb 24, 2016".
I have read through websites regarding date calculations within Access but I cannot find the specifics for the application that I need.
View 5 Replies
View Related
Aug 6, 2007
Hi
Please answer ASAP, this would be urgent!
I have two tables that need to be updated in tandem from a single access-page, by entering previously non-existent data.
One contains a contractor ID, and the name of the contractor; The other contains details of contracts, with the name of the Contractor selected from a drop-down box that draws its contents from the other table.
I have a data access page with an always visible drop-down box that shows contractors, and if I select one, it displays all contracts entered. This unfortunately means I cannot easily add new contractors, since they have no pre-existing contracts to display the details, and allow entering, and I cannot make the details fields permanently available.
Right now, I can add new contracts to those contractors who have at least one in the system already, but I'm unable to add new contractors. My aim is to have a single page add the contract data, and new contractors. Having a hyperlink open a new page that allows the adding of new contractors is also acceptable, but no coding, please, cause I suck at that...:(
Thanks in advance for your help!
View 2 Replies
View Related
Dec 9, 2005
hi to all,
I have a software which links itself to a fox pro database/tables. Now i want to link some of these free tables or database from fox pro to access. Whenever there is a slight change in the fox pro tables/databases they must reflect directly into access database. Is this possible and how?
thanks,
ashley
View 3 Replies
View Related
Nov 7, 2005
Please help...
Database Results Error
[Microsoft][ODBC Microsoft Access Driver]
General error
Unable to open registry key 'Temporary (volatile) Jet DSN for process 0xcac Thread 0xcc0 DBC 0x1090064 Jet'.
The code: example.asp
Code:<%fp_sQry="SELECT count(*) FROM TEVPDATA where ((datediff('d', '" & date() & "', DT3PROF1) between 0 and 30)) and (COL2 = 'PROF1/SLM')"fp_sDefault=""fp_sNoRecords="<tr><td colspan=1 align=left width=""100%"">No records returned.</td></tr>"fp_sDataConn="Fabos"fp_iMaxRecords=0fp_iCommandType=1fp_iPageSize=0fp_fTableFormat=Truefp_fMenuFormat=Falsefp_sMenuChoice=""fp_sMenuValue=""fp_iDisplayCols=1fp_fCustomQuery=TrueBOTID=1fp_iRegion=BOTID%>
Global.asa:
Code:'--Project Data ConnectionApplication("Fabos_ConnectionString") = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=data/Fabos.mdb"FrontPage_UrlVars(4) = "Fabos_ConnectionString"Application("Fabos_ConnectionTimeout") = 15Application("Fabos_CommandTimeout") = 30Application("Fabos_CursorLocation") = 3Application("Fabos_RuntimeUserName") = ""Application("Fabos_RuntimePassword") = ""
View 1 Replies
View Related
Mar 30, 2006
i'm doing my final year project and i have restricted the toolbar in access. I need it back as i need to make changes to the forms and switichboards, but i can not access them. Does anyone know how to get it back???????????
Please .......
Pamela
View 1 Replies
View Related
Sep 20, 2006
Hi All,
What's the best way to go about protecting an MS Access database online? Hoping to launch tomorrow, but no can do without protecting the DB first
Basically, I have a database containing some personal information and I need to block people from downloading it, while still allowing my ASP pages to read/write/modify its data.
Can anyone point me in the right direction here? Any relative links, methods, tutorials, etc, would be a fantastic.
I've looked into User-Level Security Passwords, but am not sure if there's a better way to achieve what i'm trying to do (i.e. setting up specific access rights to the folder in which the MDB file is contained).
Thanks in advance...
View 4 Replies
View Related
Jul 22, 2014
How to implement YTD calculations in Access.
Current table:
Property Month RevenueA Jan '13 100
A Feb '13 50
B Jan '14 200
B Feb '14 300
Desired output:
Property Month Revenue YTD revenue
A Jan '13 100 100
A Feb '13 50 150
B Jan '14 200 200
B Feb '14 300 500
I am new to Access programing and have not been able to figure out a query/macro to execute this.
View 3 Replies
View Related
Oct 17, 2005
I have designed an application in Access with an SQL backend.
How do I determine the user logged into SQL from within my Access application.
Specifically what stored procedures can i use to determine
1. The user name
2. The user's group(s)
of the currently logged-in user.
I would appreciate any other techniques for solving this problem
Thanks.
Peter.
Nigeria
View 1 Replies
View Related
Aug 1, 2015
Attached in the ZIPPED file is an Excel spreadsheet.
Columns A is all numeric, and needs to be represented in access as a text field.
Column B is a mixed format of dates entered and in some instances only plain numeric. I need to import this column as is into a text field in access.
I tried importing the excel sheet, but the data gets changed.I tried to linked the Excel sheet but it also had an influence on the data.In both cases the influence of change is NOT throughout. Hence my need to get this spreadsheet into access as is.
View 7 Replies
View Related
Jun 7, 2005
Hello, Here's my situation. I inherited an Access db that prompts me for a password when attempting to open it in Access. Of course; no one has the password. However; queries run fine with this db via the web using the existing ASP and VBScript pages. The code for the db connection script shows a login, but it doesn't work when I try it to open the db in Access. With that said, I need to somehow query all the tables of the existing db via the web and come up with a script that would automatically create a new database set up exactly as the existing one is. Also; how would I write the script to make the connections to 2 databases (the existing one to grab the content and the new one to insert the grabbed content)?
Thanks in advance!
View 13 Replies
View Related
Jul 19, 2005
I often run lengthy make table queries form multiple linked tables in a database with my PC unattended (often overnight). I would prefer not to have the following conformation screen “You are about to paste xxxx row(s) into a new table” but rather have Access just paste the rows.. This would prevent the database from become inactive prior to me clicking “Yes” and spoiling my query.
Is it possible to have Access bypass conformation and how would this be accomplished?
Thanks..
View 3 Replies
View Related
Apr 27, 2014
From Excel, I open a database and start a macro with the following:
Set oApp = CreateObject("Access.Application")
oApp.Visible = False
oApp.OpenCurrentDatabase db, True
oApp.DoCmd.RunMacro "Import_Data"
With the macro running and Access not visible, how can I make the database visible if an error occurs in the macro? Currently, if an error occurs in the macro, a 'Msgbox' message is generated (by the database macro). But with Access not visible and Excel visible, the process cannot continue unless the user knows to click on the 'Microsoft Access' icon in the task bar to bring Access to foreground and acknowledge the 'Msgbox' message.
I'd like to activate the Access window from within the database macro.
View 3 Replies
View Related
Jul 23, 2012
How to make the text clickable (and will open a new form)
for example
StudentNo
12345<---- if i click this one 12345 information such as name, age, course, year and etc. will show in a newform
12346
12347
12348
12349
12341
View 3 Replies
View Related
May 14, 2007
Hi im kinda a newbie here so, any helpful comments or links of creating a ms access database where it can be viewed in html is welcome. thanks
View 1 Replies
View Related
