Queries :: Calculated Field To Display Value Of One Field Based On Another
Jul 23, 2015
I have a query with a Date field for EndDate (the dates for end-of-week, Fridays in our case) and another field for Sales (number of sales, not dollars).I want to add 4 calculated fields that represent weeks and have the Sales appear in the correct column (field) for that date.So I will have columns for 10 July 15, 17 July 15, 24 July 15 and 31 July 15 and I want the Sales for each record to land in the correct date column, based on the EndDate column. (The 4 fields is just for the sake of the example, I will actually be having dozens of these calculated date fields).I tried to do it by setting up the 4 calculated fields like:
10Jul15: Sales
and then adding Criteria like:
EndDate = #10/07/2015#
It doesnt work.
the table 2 is the source of a form that will let the user change the numbers. table 1 should change Date1 and Date2 Fields based on the two fields (3months) and (6months) if i want to make a lookup wizard it should be changed manually and if i make a calculated field i can't find other tables in the expression builder
Hi, I have a query that contains the results of repeated tests on a number of persons. The goal of treatment is to get those test results below 50. I need to find, for each person, the period of time (with a start and an end date) during which their test was below 50. The dataset looks like:
Hello, Bit of a newby to all this. Am trying to use a text box as a calculted field. I am doing so using an sql statment in control source The sql stat is built and tested using build query and then copied and pasted from sql view)
I keep getting #name? as an error.
I have managed using dlookup of the same saved sql to return the required value (which works) but find it to be a bit slow and clunky so wanted to give this a go instead. If somebody can have a look at the query below I would much apreciate.
=(SELECT Sum(QryInout!CostExVATGBP) AS TTlCostExVATGBP FROM QryInout &_ WHERE (((QryInout.StockImportNoJoinToAll)=[Forms]![FrmSPurch]![ImportNoNew])) &_ GROUP BY QryInout.ProdType &_ HAVING (((QryInout.ProdType)="Freight"));)
Note that I have tried with and without the &_ and also with or with out the brackets..
Hi. I have an Order form that is based on an Order table. In this form there are also a number of text boxes (not bound to table) with calculations, and one final text box with a deadline based on these calculations. Is it possible to filter this form based on the deadline? I can do this by making the form based on a query, but then again I'm not able to edit the information in the form.... Please anyone???
I have a table with numerous fields including timeIn and timeOut field (these are date/time format). I need to find out how many people were clocked in during each operating hour (based upon their time in and time out). How can I run a query that will allow me to use the times in and out to 'flag' each hour that each person was on duty (a new field for each hour) so that I can run a count on the completed data for every hour?
I have two tables that are joined - called A and B. A has two fields, "PLACE" and "RAND" and is joined to B via field "RAND". Other than "RAND", B has several fields named 01 TOTAL, 02 TOTAL, 03 TOTAL, etc...for about 60 fields.
The "PLACE" field in table A has data that is 01, 02, 03, ect.... How do I structure a query so that whatever "PLACE" is, I can match the XX TOTAL value? In other words, i need to have the query field header be somehow dependent on the value in "PLACE".
I am trying to show an image on a form based on a user choice from a drop down box. For example, the user needs to choose a priority for a task. If the user chooses "Immediate" I want an image of an exclamation point to show up to draw attention that this is important.
This is not the normal calculated fields in query's. What I want is different, I want it to be like
IFF(ShowID="A",[TotalSales],0)
Thing I have is, I want to know all the Total sales in on row that has Show A in it and then Total sales for Show B. Problem is, I have over 130 shows. How would do that?
I have a several fields, calculated, that break apart a string of text at every open and closed parentheses. They work like they are supposed to with no errors. However, I want to use the result of this particular field in a combo box to populate another combo box with the other half of the string that will be split. The problem, I think, is that since the field is calculated, it will not allow me to select it in the combo box, saying that the field can't be edited.
So, I added the same field names to the table that the query was built on, however when I run the query, it says that there is a syntax error with a comma in the code. If I take out the table in the query, (not using the SQL side, just the query builder), it works again with no problem.The text I am breaking apart looks like this: 1.234(a)(1)(A)(2)(b)(i)(-a-)
I need this field to break the text up at the (1) mark, so the code I am using looks like this:
Code: PrinSubChap1: IIf([Sub2] Is Null,[PrincipleNumber],Left([PrincipleNumber],InStr(InStr(1,[PrincipleNumber],"(")+3,[PrincipleNumber],")")-0))
The other fields I have do the exact same thing, written the same way, with adjustments made to the position (+3,0).I have stared at this for awhile and can't figure out why it is giving me an error and research on the web doesn't appear to cover my question.
This is the error message: Syntax error (comma) in query expression '[mytable].[IIf([Sub2] Is Null,[PrincipleNumber],Left([PrincipleNumber],InStr(InStr(1,[PrincipleNumber],"(")+3,[PrincipleNumber],")")-0))]
I Have some calculated fields in a query and want to update it in the table .So,is there anyway to store these fields.Otherwise can i store this Query data every month in database so that when i re-run the query the previous month data will not be affected.
I have a query which returns a calculated percent. I have ordered that in descending order, and now want to see the top 50. So (In Access 2010) I entered 50 into 'Return'. But it returns all of the records!
Is this because pct is a calculated field? How can I correct this? The SQL seems to be correct.
Code:
SELECT TOP 50 HeciFail1.POHECI, HeciFail1.POQTY, HeciFail1.FAILQTY, IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) AS PCT FROM HeciFail1 ORDER BY IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) DESC;
I have created a form with 3 subforms on. i was just wondering is it possible to display/ hide these subforms based on a Yes/No field in the form. as the subforms would only be valid if the field is ticked as yes.
I am having a problem with calculating a date field in a query. Prior to this posting I've done some research and made several changes to my query. This only resulted in fixing one problem but then creating another problem. Original problem was I had 2 fields, arrived (23:36) and stemi (0:07). I use the following calculation AT_ST: DateDiff("n",[arrived],[stemi]) which resulted in -1409. So my research showed me I had a problem with the date whenever the time went past midnight and trying to calculate a zero hour number. I changed my calculation to
AT_ST: IIf([stemi]>=#11:59:00 PM#,(DateDiff("n",[arrived],[stemi])),(DateDiff("n",[arrived],[stemi]+1440) Mod 1440))
This works fine and gives me the result of 31 minutes which is what I want, however the problems comes in when I change to this calculation any where there was a negative time now has a 1400+ plus value. Such as arrived (7:37) and 1st_eck (7:18) = 1426 where as before it would report -14 (yes, negatives are acceptable for my reporting because sometimes a call to the hospital is placed before the patient arrives so we want to report on the negative splits). I've tried using a nested IIF to calculate for stemi time being less than arrived time, this didn't work when I tried to use it on the calculated query field. I was wondering if I could write something to check the value of the calculated field if it is greater than 1440 and if yes - subtract 1440 from it. So in the example above 1426-1440 = -14. Is it possible to do this within the query or do I need to do it using VBA
So I have the following query field which calculates another field.
How do I Filter the records in this calculated field to only return TRUE, as if I put "TRUE" in the Criteria for this field (or anything at all) then a parameter message box pops up asking for [Balance].
I want to calculate a field that is Sales*6+Salary, then in that same query I want to select ONLY the greatest Salary per employee. So for example
SELECT EmpId, MonthDate, Sales, Salary, [Sales]*6+[Salary] AS SalTot FROM EmpTable S1 WHERE SalTot = (SELECT MAX(SalTot) FROM EmpTable S2 WHERE S1.EmpId = S2.EmpId);
Can I not select a value that has been calculated this query?
I have a lengthy CASE statement in my database that displays specific text in a field based on the value of another. Simple stuff but for some reason it randomly will not work on certain values, and never the same one twice. Is there a commonly known cause for this? I have verified that the spelling and spacing etc. are correct in my code so that shouldn't be causing the problem.
I went into the properties if that field inside the query design and changed it to Standard with 0 decimal places and it worked fine.
But when I based a crosstab query off the query that contained the above calculated field, I cannot seem to get the numbers to format correctly. 1231313.424 is shown instead 1,231,313 and I don't have a line in the Properties window to even change the decimal places. It doesn't recognize when I change the format to Standard. I have tried using Round([ProjRevNRC]) which gets rid of the decimal places as desired but does not show commas.
I'm sure it's a simple part of the Round expression that I am missing but nothing has worked.
