I HAVE A DATABASE WHERE THERE ARE CERTAIN FIELDS THAT NEED TO BE SUMMED UP. I HAVE BUILT A UNBOUND TEXT BOXES WITH THE EXPRESSION BUILDER TO SUM THE FIELDS IM WANTING.
THIS IS FINE HOWEVER WHEN THE RESULTS ARE SUMMED ALL MY RECORDS ARE BEING SUMMED TOGETHER.
WHAT I WOULD LIKE TO DO IS TO SUM WHATS ON THE FORM. SO IN A TEXT BOX WHERE THERE IS A NUMBER 2 AND IN ANOTHER TEXT BOX THERES A 3 THEN IN THE SUM IT SHOULD EQUAL 5.
DOES ANYONE KNOW HOW TO GET AROUND THIS RATHER THAN IT SUMMING THE TOTALS FOR ALL MY RECORDS?
THE CURRENT EXPRESSION IM USING FOR MY UNBOUND TEXT BOX IS
I am creating a database at my job. The database tracks how many hours a CONSULTANT spent with a CLIENT vs. the number of hours the CLIENT has bought. The hours from the CONSULTANT are entered into three different subforms (because there are a few "types" of hours). I want the total number of hours bought (entered/shown in the parent form) to subtract the hours the consultant worked (hours entered/shown in the sub forms). I half-way got it to work but I am making several entries in each subform and I need to calculate ALL hours. If someone could please help, I would greatly appreciate it. I have attached a copy of the database so to give you a better idea as to what I am talking about.
Hi I have a Text box that stores a date of when a problem or query is received on to a customer account, when resolved the date it is resolved is inputted in the next text box as a resolved date. My third Text box then needs to display Yes or No depending if it took less than 28 days to resolve.
Ive used a Text box and DateDiff expression to work out the ammount of days in the control source, Called query dauration then a macro to test if its less than 28 days to display the yes or no. But this is not working days Mon - Fri. Has any one any ideas?
Created a form that requires daily work flow info to be entered. Tested the form accepting random typos like "%, # ! etc... Worked just fine. In production received the following long error:"The expression On Click you entered as the event property setting produced the following error: Expression may not result in the name of macro, user defined function or event procedure".
now i want to split it up. i want to count all the records for each month. my field name is datein_now but its just a normal textfield and not a date field. example in field: 05/02/02 10:24:31 AM. also got a datein textfield:05/02/02 i had Count(*) where datein_now between 05/05/01 and 05/05/31
I have a database (I've attached it for you). I'd like to set it up so that in the "incentive scheme" form when the field [term 1 allow] is changed and is more than 0 then have the form "permissible explanation" appear with that record so the user can add an explanation, they should not be allowed to leave it empty if the [term 1 allow] is still >0 and then save and return to the "incentive scheme" form for further input.
I want to count the number of occurences in a datatable when loading a form. BUT i want only to count if it is equal to a value on a text box on this same form.
what i made:
1-created a unbound textbox 2-inserted on "control Source" this expression =DCount("*";"T_Pendentes";"[disponente.Procurement]= " & [me].[disponenteProcurement].[Value] & "")
where * is to count all, T_Pendentes is the table, and de expression is the criteria.
On my form, I currently have a combo box that updates text box data with each new selection. However, after these text boxes for FirstName, LastName, etc. are updated, they cannot be edited. Here is the error message that appears in the status bar:
Control can't be edited; it's bound to the expression '[Form]!EmpList.Column(13)'
I'm thinking it might be a result of the data source for the text boxes being a combo box expression. Any suggestions on how to fix this? Thanks. :)
In my query, I have several results that relate to the same PK overall, and I'd like to concatenate these records into one expression/one field in a form.
Currently my query looks like this;
As you can see, the BandPK/BandFK are repeated where the GenreFK/GenrePK are different. What I'd like to do is concatenate the column named Genres into one field so the BandFK/PK isn't repeated.
I have the below code to reset my subform after I have filtered it using some combo boxes. One of my fields in the underlying table had a calculated field so I'd get an error when clicking the reset button. I edited the query to update the 'type' field and deleted the calculated field in the table but now get another error. The exact error is 'Run-Time error '3327' Field 'Type' is based on an expression and cannot be edited.' It appears it will not reset my form and show all records as long as this field is based on an expression.
Code: Private Sub cmdReset_Click() 'Purpose: Clear all the search boxes in the Form Header, and show all records again. Dim ctl As Control 'Clear all the controls in the Form Header section. For Each ctl In Me.Section(acHeader).Controls Select Case ctl.ControlType
I have a searchable form that display information on agreements other companies have with us. If a company requests an inspection we have 30 days to go out and complete it.
I am trying to create a message box that will display all inspections that are due within 5 days when the form loads. No luck so far, only broken dreams.
Is it possible for message boxes to use expressions and display information that fits the criteria?
I created a query with one expression field that updates a user inputted date field on a form. The expression adds a certain amount of time to the field (usually six months) so I know when the next inspection should take place. Everything works great except when I put a parameter in the expression field. It will not return the property dates. If I simply remove the expression, and input the date manually, it works just fine. Am I not allowed to use date parameter with an expression? It returns every date within the correct month, but will give me future years as well.
The expression is - NextInspectionDate: DateAdd("m",12/[InspectionFrequency],[LastInspectionDate])
The parameter is - Between [Forms]![Preventative Maintenance Dates]![Sta
I need help setting up an expression. I need to set the default value = date + 2. But this also needs to take into account working days. I am just having problems with the syntax for this, as i would normally do this in VBA and do not have much experience with Expression Builder. And yes it does have to be done in Expression Builder.
I know this is horribly wrong... thats why I am asking for help. IIf ((WeekDay(DateAdd ("d", 2, Date)) = 1, (DateAdd("d", 3, Date)),(DateAdd("d", 2, Date))) OR IIf (WeekDay(DateAdd ("d", 2, Date)) = 7, (DateAdd("d", 4, Date)),(DateAdd("d", 2, Date))) OR IIf (WeekDay(DateAdd ("d", 2, Date)) < 7 AND IIf (WeekDay(DateAdd ("d", 2, Date)) > 1, (DateAdd ("d", 2, Date),(DateAdd("d", 2, Date)))
I need 3 If statements: 1 for if its Saterday 1 for if its Sunday 1 for if its neither.
In VBA I would do something like this: If WeekDay(DateAdd ("d", 2. Date)) = 1 Then Box = Date() + 3 If WeekDay(DateAdd ("d", 2. Date)) = 2 Then Box = Date() + 4 If WeekDay(DateAdd ("d", 2. Date)) > 1 And WeekDay(DateAdd ("d", 2. Date)) < 7 Then Box = Date() + 2
could i get some advise on an expression i need in a query?
i have 5 fields SumMats,SumLabs,MatsDed,LabsDed,Ttreat
i need another field that will give a value on a condition. the condition is that if LabsDed isNull, MatsDed will be divided by Ttreat otherwise LabsDed will be divided instead. can this be done? if so, how could i do it?
Maybe i am totally confused but here is initially what i have and what i want to do.
I currently have a db for carrer development-- i have three fields
Last Review Date Next Discussion Due Status
My next discussion due is on the fly-- i did a dateadd (by one year) from the last review date to get the current result for the next disussion due--
here is what i want to do with the status and am confused with how to do it-- i want the status to have three options-- completed,overdue and upcoming-- i want these to be autogenerated but am unable to find what expression i need-- as the IIf expression only gives the option of two possible results--
so how would i go about doing the expression with having three possible outcomes?
I am trying to execute a query that calculates a retail pricing off the whsl pricing. This is the excel calculation i use: =IF(F2<10,(SUM(F2*4)),(SUM(F2*2))). I changed the IF to IIF, and replaced "F2" with "[WHSL PRICE].
However, I am getting an error that the "category" expression is not included in the function (when I have all the fields in the query) or the function if I have just the field I am trying to calculate in the query. Can somone help me understand what I am doing wrong.
I would also like this calculation to save in the table, if possible. As I use this information to update my fields in my web database.
;) I want to create a field named ErrorCases that sums the rows meeting the following conditions: The row has a ValidityReason = 2 and for the same row ExpValidity is either null or 3. I want a count of the rows meeting these condtions.
Here is the formula I have constructed that does not work.
ErrorCases: Sum(IIf(([ValidityReason]="2" And [ExpValidity] Is Null) Or ([ValidityReason]="2" And [ExpValidity]="3"),1,0))
I having some trouble trying to use an expression in my query, I have 3 indivisual exoressions in 3 fields I which work perfectly, however I want only 1 field produced so I tried placing 1 after the other as below:
How can i write an expression in a query field that will return the following text; "A","B","C","P" and "F" Base on another field when the values of that field are <=44.44, between 44.45 and 54.44, Between 54.45 and 64.44, Between 64.45 and 74.44 and above 74.45 respectively?
