Why can't I ever get these to work properly? I want to use them to ignore null fields so that I won't get the punctuation but it isn't working.
Here is what I have but the commas still show up:
=IIf([Status]=1,(+[MailBoxOrPOBox]) & (+", "+([CompanyLocID].[Column](2))) & (+" ,"+([CompanyLocID].[Column](3))),"")
is there a better way than trying to use these + signs??
Just switched from Access 2000 to 2010 and now on many forms and reports where dates are displayed, you see ###### instead. I realize that widening the field fixes this but there are a lot of forms and reports in this database to go through and change all of them.Is there a setting somewhere to have the date fields display properly even if the text doesn't completely fit in the box?
View 4 Replies View RelatedHere is the basic info:
Table has Checkbox column
BusinessDate column (mm/dd/yyyy format)
OrderTotal column (in dollars)
There are others but these are the relevant ones
In the header of the form I have a calculated control box with the following control:
=Sum(Abs(nz([Checkbox],0))*nz([OrderTotal],0)),0)
Which works just fine. The user is presented with a list of all the orders from this table in the form. They check the checkboxes and the control in the header shows the total of the records checked. I need to make 7 of these calculated controls, one for each day of the week. Is this possible? This way the user can see the order totals for each day of the week they have selected.
I tried:
=IIf(Weekday([BusinessDate])=2,Sum(Abs(nz([Pull],0))*nz([OrderTotal],0)),0)
but it still sums up the whole week as the expression holds true.I do not know VB, and I'm sure there is a really neat and easy way to do it in there. I just don't know how.
I have a form. I want to move a field and it's label. I need to unhook it from the other fields or I can't.
I found this post on another forum: [URL]
It says, in part, When you create your form, before you do anything else, open it in design view. Right click on the top-most of the stacked controls and choose Layout | Remove on the shortcut menu. All the controls are free to move individually now.
alas, I don't know where the "stacked controls" are or Layout/Remove or any "shortcut menus of any kind". (oh how I long for the simpler days of "ordinary" menus (easily accessible from the keyboard.)
I have a main form which shows customer personal details including their budget and a subform which shows the costs for each course they book. The sub form has 4 boxes which I use to capture course fee, travel fees,subsistance and other expenses (along with other details such as course code etc). These cost details are stored in the table study_leave_recs in the respective 4 fields. I show on the subform the total spend for each course being booked using a calculated control. On the main form I use four dsum statements added together to show the total spend per customer.
=DSum("[COUR_ACT_FEE]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")+DSum("[COUR_ACT_TRAVEL]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")+DSum("[COUR_ACT_SUBSISTANCE]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")++DSum("[COUR_ACT_OTHER_EXPEN]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")
It works ok but is there a better way of doing this? Also is there any way I can use the resultant output to calculate / show the remaining budget (the budget is held in the general table "people"? I'm having trouble using the above statement to subtract the calculated spend from the budget box shown in the main form!!
I would appreciate any help, I seem to have spent ages on this on! Thanks in advance. Regards Peter
Now I can create a Query that adds, subtracts a specific column, but im having a hard time trying to get the information into a form since it don't seem to allow me to pick the query field which is calculated as a datasource. Can someone tell me how to make a calculated query column appear on a form as a field? Such if I type in monday in the regular table, the form will add a few days to the next available game? I have the information/code already in a query that displays the query right in a new table using the Expr method but I can't get the form to just simply display the field. Someone please help!
I see a few people asked close to the same question but nobody never answered so I am thinking it is impossible
I am creating a continuous form where each record has a couple calculated controls. The problem is, as you scroll down through the records, the computer hasn't performed the calculations yet. It only starts to perform them once they are visible on the screen. So the result is blank fields for about 1 or 2 seconds every time you use the vertical scrollbar until they all get populated. Is there any way to get access to populate all the calculated text boxes when it is loaded, like a report is? I have played around with not using a continous form but rather creating a form with MANY text boxes on it that are filled with a loop. It works and all calculations are performed prior to displaying it on screen its just tricky to work with in a realistic situation and I figured their must be a better way.
Thanks for your help!
I have a form where the record source is a table.The form has about 5 calculated fields on it. The screen flickers quite badly and at the bottom says calculating. Sometimes the database locks up.
To stop this flickering i created a query so that the calculations are done in the query. Then i linked the form to the query instead of the table. Am i doing this correctly as i didn't think the table would show any new data that i entered into the form but it does seem to work. Is it because i am only using one table.
I am using Access 2003.
I'm still very new to Access.I have a form with subforms.Some of the fields are calculated fields.I cannot figure out what some of the fields will not update automatcally. Once I click out of the form and then back in, it will update.Tried many things like, Refresh, Requery both on the form and on the text box(es).I wish I could figure out how to attach my test database to show you.
View 11 Replies View RelatedHow to store a calculated field into a table
I am preparing an invoice and I have a field called deposit .Now this field calculates %40 of the total of the order .but if the user wishes they can enter what they like .Now this field must be stored in a table for ever and a day and must not change or the invoice will be useless .I can see no other way of doing this and my research tells me (allen Brown) that this is sometimes necessary .
In my form I have a calculated field that works out the time elapsed for a job, worked out as the QTY/RUN RATE which gives me the time in hours. However I couldn't format the box as a date/time, as it is a calculation and the times may go over 24 Hours which results in the date/time giving me something like 31/12/1899 16:00 for example.
I've got around this now and I've formatted the results as HH:NN:SS using a public function in a module, but the problem is this is now stored as a text field. I've tried setting this to a number field but the formatting doesn't carry over. I've tried setting a custom format in the table as HH:NN:SS but then all I get is just a bunch of 0's, no calculation results.
So how to format this as a number field properly?
Also for any extra information:
- I know you're not supposed to store calculated results, but this is slightly different, the calculated field is a standalone text box and the bound field is a formatted text box.
- The reason I have to store the calculated results is because my boss wants to eventually see a chart of the total hours of jobs each day, hence why I'm asking if this can be done as a number field.
i have a continues form that lists all my active items and I'd like to use it to quickly check if they have all the data inserted, ie entries in each separate table. it looks like this
I perform a simple check on form load chk = DCount("[phID]", "preh", "[phbID] = " & Me.tID) where me.tid is the item ID and this gives me 0 or 1 and writes ok, or missing into the appropriate box.Problem is it tkaes value for the first item and repeats it for eah other, so I get ok or missing throughout the continuos form.How can i force a check for each line?
The Database is not split. Have 30 some users.
Here is what I am trying to do.
I have Mary, Sam and Bill logged into the database.
When Mary signs in she has no records to update, but when Sam and Bill logs in they have records to update.
How can I get a message just to Sam and Bill, but not to Mary?
I have a qry what records needs to be updated.
Just how would I go about this? I thought I had it when I use the "environ" but that just shows who is logged in. I am sure the form has something, but I haven't found out where.
Can you point me in the right direction?
Hi,
I have been searching and searching and cannot find any info on this specific problem.
I have a subform that users go into and enter data into the fields. Intermittenly, when a user closes out and then goes back in, the data in one of the fields (always the same field) is missing and has been replaced with ########.
Any insight or advice would be greatly appreciated!!
Thanks,
Krysti
I accidentally (really don't know how I did it) deleted the dollar signs from the entire database that I use daily. I did not set this database up, and don't really know how to much more than enter the data, and do basic stuff. How can I put the dollar signs back on all the forms/queries/reports that I use daily?
View 3 Replies View RelatedHi,
I have read that it's not recommended to put calculated fields on a table and they should instead be calculated using a query.
How about if I need to store that calculated value in a table (e.g. field1 + field2 = value in field3)
How can I do that please?
Thanks,
B
I am trying to create a calculated field in a query, that shows money owed from customers. I have 4 different job codes that have their own prices. I also have customers with the amount of hours they owe for, for each job. There are two customers that owe for two different jobs. I know how to write in : Money Owed: [price per hour]*[number of hours]. but i want to have only one line for each customer when i run the query. How do i combine??
View 1 Replies View RelatedI have a detail form, which has controls populated from a query.
For example,
Name Number
xxxx xxxx
This can be any length depending upon the amount of database records, so there might be 5 names, or 4 names, etc.
I then need to sum these in the footer. Is the possible?
I just seem to get #Error displayed when doing SUM([ControlBoxName])
I have a calculated text box on a form:-
=[AnnualSavings]/[SKUAnnualSpendInPounds]*100
But, if both the Annual Savings and SKUAnnualSpendInPounds text boxes have a value of 0 the result of the calulation comes out as #Num!. I assume this is the error because I am doing 0 divided by 0.
Does anyone know if this is the reason for my error and if so is there anyway of changing the #Num! back into a 0 so that I can submit it to my tables.
Any help would be greatly appreciated
Here is my dilemma
I have a table called inventory that stores my different products and the quanitities i have in stock. I also have an orders table that handles a customer palcing an order for a certain product and the amount they want. What i want to happen is this: If a customer orders for example 5 widgets, I want my inventory table to automatically reflect the 5 widget deduction from the quantity in stock. Anyone know how to do this?
Im wondering how to make calculated feilds in a query?
Do i have to make the feild in a table the go to query?
I developed a query that calulates when a contract is reviewed i.e 5 weeks and 9 weeks after the commencement date. These are called CDE1 and CDE2. This works well and the CDE1 and CDE2 dates are displayed ok.
The query above is:
SELECT Contract.ContractNo, Contract.APSNo, Contract.Firstname, Contract.Surname, Contract.AgencyDesc, Contract.FacilityName, Contract.Designation, Contract.WeeklyHrs, Contract.PositionNo, Contract.ComDate, Contract.EndDate, IIf(DateAdd("ww",5,[comdate])<[enddate],DateAdd("ww",5,[comdate])) AS 1stCDE, IIf(DateAdd("ww",9,[comdate])<[enddate],DateAdd("ww",9,[comdate])) AS 2ndCDE
FROM Contract;
The problem is that I now want to access contracts under these CDE dates between two dates that is user defined (startdate) and (enddate).
As the above are calculated fields, how can I develop a new query based on the above as CDE1 and CDE2 are already calculated fields?
New to Access and learning the hard way. Im sure this is an easy one, but sure would appreciate the help. I am trying to sum a group of cells and display the total, which i have done. But I also want to store this total in my database. So my question is, how do i move my calculated total to my field that has the "record source" bound back to my field in the database? Hope that makes sense. Thanks in advance.
View 3 Replies View RelatedI have an Access 97 database with a form and a subform for invoice and invoice details. When I change the qty or price in the subform I have a calculated field on the main form that updates. I also have a button that when clicked brings up the payment screen with the totals from the previous screen. The problem lies when I change the qty or price and click the button right away, the next form pops up but does not take the new totals, it grabs the old ones. The calculated fields do not refresh fast enough. I have tried requery, refresh, and even a loop to make it wait before opening the payment form, but nothing seems to work. I'm at a loss. Any ideas would be appreciated
Thanks
Ken Reid
I keep getting caught up in the same point over and over again. I need to save a calculated field's calculation to a table field. The problem is how do I do this? I already know about the rules but I need to do this and store that specific information. Can someone post a sample code? The problem is the code and codes I been using dont' seem to work any more. I was putting
fieldname = textbox2.txt
in the form before update box. The problem with that is it would give me an error if the data source the calculation pulls from is not in the table/form.
Hello,
I have a form and a subform in MS Access 2003. I have made some changes to database structure, so I decided to change the subform also. When I changed the Link child and link master fields, the controls of the subdatasheet dissapear- they show only in design view. If I clear the contents of Link child and link master fields they appear again, but the records are not binded.
Is there a setting on the parent form that also has to be changed, to make the new binding?
Thanks,
Aleksander