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 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.
View 1 Replies View RelatedI 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].
Code:
Balance1: IIf([Balance]<>0,"TRUE","FALSE")
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 very basic query with the following calculated field in it:
ProjRevNRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentNRC]*[Rev Flow Through],0)
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.
I have query with a calculate field to finds the next service due date but I'm having a problem getting it to only show services due dates in the next 30 days.for some reason I cant add a criteria date()-30...I have to calculate the next service it takes service intervals from maskservicemonths field then find the last service date and generates the next service due date NextService: DateAdd("m",[MaskServiceMonths],[FindLast]).
View 14 Replies View RelatedI've can do this on excel but don't know how in Access. I'm calculating bonuses. My table has salaries, and my query simply multiples each salary amount by a % to get the bonus amount. But I need to calculate adjustments to the bonuses using the sum total of the bonuses my query calculated:
Salary (from table) Bonus (calc'd by query) Adjustment (to be calced)
100,000 1,000 Sum of total bonuses/salary*4%
90,000 900
How do I capture the total of my calculated bonus column to use to calculate the adjustments in my right-most column?
I am trying to construct a crosstab that averages a calculated field from a previous query. It is returning a "Data Type Mismatch" message.
The field I am trying to average is a subtraction of dates to find total days. I assume my field is not a number so I have tried to wrap it in CDbl() to change the type.
The formula is
Code:
CASE_DAYS: CDbl(IIf([Actual Close Date]-[Creation Date]>=0,[Actual Close Date]-[Creation Date],""))
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 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.
I am working with DCount in a query based on a calculated value [MyCalValue] that is a status.
The status can be: Complete, On Going, Past Due, Additional Information, No Date.
In the DCount, I want to return 0 if null.
Sample that works fine
Code:
Green: DCount("[MyCalValue]","Open Issues","[MyCalValue] = 'COMPLETE")
But I want combine values in an OR statement but can't get it to work.
Code:
Green: DCount("[MyCalValue]","Open Issues","[MyCalValue] = 'COMPLETE OR On Going")
Also not working is when I am trying to get a count based on the month like this:
Code:
LastMonth: DCount(Month("[RequestDate]","Open Issues","[RequestDate] = fLastMonth(Now())",0)
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))]
Can I place a criteria in a calculated field?
[SellingWgt]*[SellingPrice] is ok but only
if [SellingUnits] = "lbs" or [tblSellingUnits].[SellingUnitsID] = 1
I want to calculate running total and find out the date when that total is greater than a number.
My initial plan was to use Dsum and then use dlookup to find when that Dsum value > [Fixednum].
But when I try Dsum and use Totals in query, access shuts down. maybe because of 15000 rows.
I have attached a sample database that shows what Im working with and what I would like.
I am using access 2010. I have a query with an iif statement on a calculated field im trying to round up. ex.
Code:
TotalDiscount: Round(iif([field1]="Piece",[int]-[decr],[field2]*[field3]/[field4],3))
I get an error something like "wrong number of arguments or expression on comma.
I want to take the sum of all records 'cost' i.e.
record 1 - 2
record 2 - 4
record 3 - 3
I want to take the sum and put it in a calculated field and use it to divide it by a predefined value 'no of responses.'
So I make the field in the query -> Calcfield: (Sum([cost])/[responses])
I am pretty new to this and i don't understand why this won't work? Is it something to do with the row Group by (total.)
I have a calculated field in a query. The field name is TotatPt (this is to calculate the total points students have earned during the term). The expression is as follows:
Code:
TotalPt: [Att1Pt]+[Att2Pt]+[Att3Pt]+[Att4Pt]+[Att5Pt]+[Att6Pt]+[Att7Pt]+[Att8Pt]+[Att9Pt]+[Att10Pt]+[Att11Pt]+[Att12Pt]+[Att13Pt]+[Att14Pt]+[Att15Pt]+[Att16Pt]+[Att17Pt]+[Att18Pt]+[Att19Pt]+[Att20Pt]+[Att21Pt]+[Quiz1Pt]+[Quiz2Pt]+[Quiz3Pt]+[Quiz4Pt]+[Quiz5Pt]+[Quiz6Pt]+[Quiz7Pt]+[Quiz8Pt]+[Quiz9Pt]+[Quiz10Pt]+[MidtermWritPt]+[FinalWritPt]+[Proc1Pt]+[Proc2Pt]+[Proc3Pt]+[Proc4Pt]+[Proc5Pt]+[Proc6Pt]+[Proc7Pt]+[Proc8Pt]+[Proc9Pt]+[Proc10Pt]+[Proc11Pt]+[Proc12Pt]+[Proc13Pt]+[Proc14Pt]+[ProcPracPt]
I think this should be no complicated expression (though a bit long) and should just add the fields together. But what I get is that the calculated field appears as expected for records with an odd primary key (1, 3, 5, 7, ...) and turns out blank for records with an even primary key (2, 4, 6, 8, ...)!
I've attached a screenshot.
I'm stuck in Access 2003 - the group I am working with is unwilling to upgrade due to costs, and I've inherited this database (or I should say, a glorified spreadsheet), so unfortunately I am stuck with the tools that I have to use.
So I have Table A:MemberID (autonumber, Primary Key)
NameLast (Text)
NameFirst (Text)
TLBeginner (Yes/No) - Training level
TLIntermediate (Yes/No) - Training level
TLContinuing (Yes/No) - Training level
TLAdvanced (Yes/No) - Training level
I have created Query1 with the above table and added in a calculated field which essentially takes the training levels and converts it to a decimal number (basing it off a binary number of the 4-bits/Yes-no fields) with the calculated field below:
TrLevelTxtID: IIf([TLFundamental]=True,1,0)*1 + IIf([TLIntermediate]=True,1,0)*2 + IIf([TLContinuing]=True,1,0)*4 + IIf([TLAdvanced]=True,1,0)*8
So the calculated field works great - Depending what training levels are selected or not selected, I get a range between 0 through 15.
Table B: I want a list of "friendly name" for the different training levels.
TLID (Number, No duplicates)
TLFriendlyText (Text)
So what I want to be able to do, is to have Query1, that will pull the training level text (TLFriendlyText) within the query based on the calculated field (TrLevelTxtID). The catch is, I can't seem to create a join between the two tables with the calculated field.
Ideally, I want the final query to pull, [NameFirst], [NameLast] from TableA and [TrLevelTxtID] (from TableB) based on the queries calculated field [TrLevelTxtID].
How best to achieve this with the Access 2003 limitations. Also, this query will only be used for reporting/mail merging so there are no update requirements or concerns.
If I have four date Fields in a query, Astart, Bstart, Cstart, and Dstart and want to have a calculated field to find the latest date for each record how would I do that? I have tried things like:
LatestDate: MAX(Astart, Bstart, Cstart, Dstart).
The calculated field converts all percentage marks perfectly fine with grades except 100 which returns to a stupid "E" . I've been trying all sorts and now give up.
Code:
Grade:IIf([Percentage]<20,"U",IIf([Percentage]<30,"G",IIf([Percentage]<40,"F",IIf([Percentage]<"50","E",IIf([Percentage]<60,"D",IIf([Percentage]<70,"C",IIf([Percentage]<80,"B",IIf([Percentage]<90,"A",IIf([Percentage]<100,"A+")))))))))
I have a query that returns several calculated fields. One of them is simply derived by simple summation of the others. If this calculated field returns a negative number, I need it to show as a zero.
The only way I know how to do this is by an IIF statement :
Code:
SELECT [fld1], [fld2], [fl3], .....
IIF(([fld1]-[fld2]-fld[3])<0,0,([fld1]-[fld2]-fld[3])) AS fld4
FROM...
(The above doesn't suggest that [fld1], [fld2] etc are calculated fields - I just wrote it like that for succintness - they calculate fine, there's no issue with them...)
Is there a more efficient way of doing this? I find IIF's a bit tardy, possibly because they evaluate for both True & False eventualities, regardless of the condition, and this query is going to run against a fairly large dataset so any performance lag is going to be exacerbated.
I have an inventory transaction file that has quantity on hand as a calculated field. I am trying to add this unbound field to a report using a query but I can't figure out how to add my quantity on hand field to the query since it is a calculated field and not a bound control.
Any help would be appreciated.
Thanks
darrrellx
I can't seem to find a problem like this already answered....
I have these two calculated fields:
dtmNPacketDue: IIf([strRevTypeNIRB]="Full" Or [strRevTypeNIRB]="Termination(Full)",[dtmNIRBMtgDate]-28,[dtmNExpDate]-28)
dtmCOIDueDate: IIf([strRevTypeNIRB]="Termination(Full)" Or [strRevTypeNIRB]="Termination(Expedited)","N/A",[dtmNPacketDue]-42)
When I use the one in RED in a calculated field in a new query, things are peachy.
But when I use the one in BLUE in a calculated field in a new query, the message is #Error
My Calculated field (which produces an #Error) in the new Query is:
2ndCOINotification: [dtmCOIDueDate]-14
(if I insert [dtmNPacketDue] instead...it works
Perhaps my problem is that dtmNPacketDue (a calculated variable) is part of the BLUE calculated variable....??
Any ideas greatly appreciated....
Hola folks.
I am trying to create a calculated field in a query that subtract 2 fields within the same query.
That is, i have a FiscalWeekStart and FiscalWeekEnd pulling from a table. I created a CountofWeek field that count the weeks between the FiscalWeekStart and End using "DateDiff("w",[FiscalWeekStart],[FiscalWeekEnd])".
I also have a HolidayWeek field that pulls from a table using a
Count expression in the query.
My goal is to subtract the CountofWeek and the Holiday week calling it NonHolidayWeek using "NonHoliday Week: IIf(IsNull([CountofHolidayWeek]),[CountofWeek],[CountofWeek]-[CountofHoldayWeek])"
BUT IT IS ASKING FOR PARMETERS ON THE "CountofHolidayWeek".
Thanks for the help in advance.
TukTuk
I've been struggling with this for a while, and even though I understand the theory, I can't get my code to work correctly.
I have 2 fields:
CallbackFrequency & Last Contacted.
I want to use these fields to populate a third field (callBackOn) so that we can have a list of candidates that need to be called from a certain date.
CallbackFrequency is added from a combobox, so all values are either 1 Month, 3 Months, 6 Months, or 12 Months.
I have tried creating a calculated field directly in the candidates table, but apparently this is not possible.I've therefore tried to create a query using IIF statement that will calculate this value.To get to grips with the code, I'm only dealing with 1 callbackFrequency at the moment. So far I have:
CallbackOn: IIf([Candidates].[CallbackFrequency]="1 Month", [LastContacted],)
I have not factored in the date manipulation yet, as the query does not display the LastContacted date of the record it finds, it only shows a blank cell,