I have a database I inherited that I'm trying to do some clean-up on. One of the things I want to add is a text box on the startup page that tells users the date of the most recent record in the DB. The date field is stored as "Batch Posted Date" (already yelled at them for spaces) in a table called "Adjustment Data."
I tried using =Max([Adjustment Data]![Batch Posted Date]), but I get #ERROR. I thought it might be a problem because of the spaces, so I wrote a query to pull the max value of the date, and tried dlookup. When I try =DLookUp("maxofbatch posted date","qryHighDate"), I also get an error message that says #ERROR.
I'm a bit new to formulas in Access, can someone help me out?
Hi All:running Access 2000.I have a qry with a formula field that performs some calculation (Extension: CCur(([OnHand]*[Price])/[UnitsPer]))and it works fine. How can I update the actual table with the result of the calculation for every record? The problem I have is that there's some null values in the results therefore when I try to get totals on that field, it errors out saying "invalid null use"thanks
I'm trying to secure the table records in my db. I'm looking for something that will allow me to input regular text from a form and then once the form is closed it would add texts to the record that is stored in the table record.
Example: Input "HELLO" into form. Table record would show record as "xegaHwueEoieLwoiLiwoO" Also, recalling the text would also have to be able to revert the jumbo text back to regular texts also. This would be needed for forms and reports.
Ok so I'm very new to access. What i want to happen is for there to be a textbox on a form that changes depending on what is in the other text boxes. Eg 1st box = 1, 2nd box = 2. I want the third box to equal the ammount of the first two added together (textbox1 + textbox2 = textbox3) how do i do it? Where do i put the formula. Thanks.
I've built a table and used it to build a form. One the form I have three boxes, one to enter the DoB, and two others that record the age and also put the individual into an age bracket.
In the latter two categories I have entered a formula in the Properties Box under Data & Control Source (the formulas are below) to automatically work out what the age and age bracket is when the DoB is entered. This works fine but the information isn't transferred back to the table.
Do I need to do the info in the table first and then update the form? And if so where do I enter the info in the field properties in the table design view?
A nice easy one for you. I am creating a database, one of the fields is total cost. Is there an easy way to have another field, which calculates the VAT without using a query? I would like this done as soon as the total is entered.
I have done this using a query, but didnt like this way.
I am sure there must be a very simple way of doing this but it has escaped me.
I am getting an error everytime I try to use the SumIIF formula in my form.The relevant fields are Qty, Unit Price and Vatable (check box)..I then created a field in the form's footer with the following formula
I've been trying to put a formula in a text box within a form, with not much successes.
What I want the formula to do is:- From the Date field in the form, if there no date entry's then display a Blank, if there is only one date entered, I would like it to display the time in week since that entry. But if there are multiple dates entered I want it to display the Ave time between the dates, in weeks. BUT only as far back as 12 months. Any dates further back can be ignored.
I am an Access newbie and I have a form running about 5 seperate formulas and I would like to store the info in the table that it is associated with so that I can use it later in a query to create another formula. Any help would be greatly appreciated.
I put the formula in the Text box field in the control source in the property's tool box and i don't know if it will save that calculation to the table. I was wondering if i can do VBA code to save the the calculation in the table.I have a field called CA Weigt and i put the formula in there as =[Weight] * 0.4536 and it works. How would i put it in a vba code.
I have a query that calculates input information into a value that then needs to be compared to another query values and will be used to output a % change in a third query. Is there any way to make this happen? Thank you in advance!
I have a routine that exports the results of a query to an Excel file. Is it possible to input the formula into the query so that the Excel values calculate?
This is the formula I am trying to pass to the "AZ" column of the Data tab
i have a list of check boxes which state whether a ticket has been sold or not. what formula can i use to find the percentage of tickets that have been sold (by counting the number of checked boxes). also how can i implement that onto a report to display it next to a pie chart of the information. if it cannot be put on a report how can i put it onto a form?
I am trying to create a formula that will update a field in my table - I have created an update query and want to update the field GPA.
I have tried using the iif formula without success. Here is what I want to calculate.
I have two fields one is Score the other is Baseline. The formula needs to place the Score of 4.0, 3.5, 3.0, 2.5, 2.0, or 1.5 in the GPA field given the following.
If the difference between the field score and baseline is .00 then the score 4.0 goes in the GPA field, if the difference between the field score and baseline is .05 then the score is 3.5 in the GPA field, if the difference between the fields score and baseline is 1.0 then the GPA is 3.0....
If both fields are equal then score is 4.0 .05 score is 3.5 1.0 score is 3.0 1.5 score is 2.5 2.0 score is 2.0 2.5 score is 1.5
I have a customers table, products table, and a transaction table that brings them together fields in tblTransaction:
Transaction ID Order Number Customer ID Title ID Date Status
I want to create a query so that you can type a name of a customer in, and it will bring up all the items they have bought, and will add up all the prices (from tblTitle) and show it at the bottom but im not sure how to get this calculated field into the query? Whats the sum formula that you put into a query?
Because I am stupid at math, can some please help me out with the formula to get the percent of something that has changed.
Say I have October usage of 2685 and November usage of 6294. I need to know what the percentage of the change is from Oct to Nov Basically the formula to make this calculation.. The calculation will be done in Excel. I know this is an access help area, but you guys have really helped me in the past.
I need a formula that will give me the count of the number of files I scored 3.0, 2.5,2.0,1.5, and 1.0. These scores are in one field called Claim Quality Index.
What I am trying to do in a query is to count the number of files I scored a 3.0 and in the next column of the query give me the count of the number of files I scored a 2.5 - same for score 2.0, 1.5, and 1.0.
I have chosen the count in the query but I cannot get the criteria to only give me the above. I believe I need to use the expression and name the field CQI3: ????(formula that I cannot get right.
Can anyone help - I have tried various ver. of the IIf formula but to no avail.
Could anyone give me information on learning how to use the formulas in Access 2003? A good book, site..etc. Example I need to know the formula that takes 20061015 and puts it into 10/15/2006 format. Any help is appeciated!
I am trying to Automate the creation of a quote number. I plan on making a form that contains a SalesID ComboBox and a Command button that opens a new form. On Click I want the new form to open a new form and insert the created quote#
Basically the number needs to consist of:
The letter "Q-", "Date", "SalesID" (selected from the drop down menu) and "-#" (sequenced number)
this should give me something like this:
Q-13107BOB-1 Q-13107BOB-2 Etc.
How can I do this?
Could someone provide a name for what i am trying to do so I can look it up on Google?
Hello, I have a formula on my report in a text box that just shows the total delay for a crew by using the supervisors name. This information isnt stored anywhere its just to show a percent on the report! the formula is:
Ok so I'm kinda new to access. What i want to happen is for there to be a textbox on a form that changes depending on what is in the other text boxes. Eg 1st box = 1, 2nd box = 2. I want the third box to equal the ammount of the first two added together (textbox1 + textbox2 = textbox3) how do i do it? Where do i put the formula. Thanks.
I am a big Formula 1 fan and I am thinking about creating a F1 database. However, I can't decide which is the best way to store the data. I don't know if one race should be a one field or if one driver for one season should be one field. Obviously I want to store it in the best way possible so that I can easily pull out relevant data in queries. My knowledge of Access is relatively limited so any help you could give me would be appreciated.
I've got a table set up with sales info and all other kinds of data. How do I get a formula to work? Here are the inputs I have: Total sales (by outside company), total purchases (from my company). I want to know what percent of their total sales are made up of the poducts they purchase from me and have that field update automatically. Its a simple formula really, I just don't know how to enter it properly. Thanks.
