Queries :: How To Filter Calculated Field In Query Using A Criteria
Jul 17, 2014
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]).
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].
PROBLEM: i want to search for all the "amount outstanding" that >0 HOWEVER when i put that as the criteria it asks me to enter values for other calculated fields
I would like to display a report based on the table called "expenses", filtered by:
- "from" (datefrom field) and "to" (dateto field) date on "payment date" field; - multiple criteria on same field called "payment method" (I would like to include only payment methods "check" and "credit card", but not the other payment methods in the field, such as "cash", "transfer", etc.)
For that end I made a query based on the table "expenses", and in the "payment date", in the criteria field, I entered:
between [form]![formname]![datefrom] and [form]![formname]![dateto]
This works fine so far, however when I attempt to add multiple criteria on the "payment method" field, it does not filter accurately any longer. In the same row of the criteria field where I completed the date criteria, I enter "check". In the next row, same field, I enter "credit card".
Since it doesn't work, I tried putting both arguments in the same line as the date criteria (always in the payment method field) as: "check" and "credit card" but still does not work (now it filters the payment method correctly, but the dates filter appear as if I have never completed them).
What I am trying to do is fairly simple i just dont have the ability to correctly code what i want to do.
I want to filter my query based on some criteria in multiple columns. But i only want the query to filter based on the specific criteria if a checkbox has been selected.
Basically i want the criteria for one of the columns criteria to read
IF a check box "Check0" is selected THEN filter the column to only records that = 1 and if "Check2" then filter all records that = 2
I was trying to filter a word using Not Like "word" in my query cirteria, some data is blank also, From this record i want to retrieve the result. But when i use Not Like Criteria, filter is working but blanks columns are also filtered.
I want to display all records (including blanks) except what i shown in the criteria.
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.
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
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 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.
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'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 tend to work with large amounts of data (extracts from company systems) and I create a lot of Access/VBA based tools to automate processes.
I have an annoying error which has always appeared but I don't understand the root cause of it.
When viewing a query, if I filter, I get an error message pop up (though after clicking through the error I can still use the filter function):
"syntax error missing operator in expression 'name of field'".
This seems to happen when I add several calculated fields. Here are some examples of the conditions and calculated field formulas I'm using in this current one:
Conditions: <>"CINEMATIC" And <>"SFX" Not Like "*_ZZ*" And Not Like "*test*" And Not Like "EP_*" [Forms]![FRMscriptPrintReview]![selectLangCombo]
I get the impression that its more of a bug with Access as the formulas aren't complicated really but need confirmation on this and if there is a way I can avoid it.
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 with a DateSerial Calculation field that I would like to filter the query by. The DateSerial calculates the same day of every year (5/31/"YYYY"). When I try to add a criteria sort to this field, I get a data mismatch error. Here is the code: ThirdMay: DateSerial(Year(DateAdd("yyyy",3,[LastDayYear])),5,31).
How do I get only dates due in 2015 to show? I have tried all the standard date criteria to no avail.
On [Form1] I have a Multi-Select Listbox[List1] which shows Job Numbers [WBS] (The job numbers displaying are a result of a separate query filtered by the Fiscal Year combobox). When I click [CMD1] I want [Query1] results to only be selected [WBS] from the form.What is the best way to code this? I'm a beginner when it comes to VBA.
I have a value in an unbound field on a Form, which is 1234 OR 765 OR 356.
In the QBE criteria grid, I used builder to reference this form:
Forms!myform!myunboundfield
The column this is in is for the ID field, which is a number.
However, it is not filtering the data correctly. If I copy the above text and paste it into the QBE grid, then it will work. But when I reference it, it fails. If I change the value to just a number on my unbound field, it works. So the issue seems to be that its bringing across the text as a string and so perhaps effectively puts quotes around it when referencing it.
I have made a function returning True/False values. I used this function in a query and now it return value Error as well...Is there a way to set criteria to values received in that field (0/-1/#Error). I've tried putting Like 0, Like True with or without quotation mark.Also every workaround comes into play as long as it works.
I need to update the periodtype field in my table depending on different values in the field Formtype- I am looking to do this without having to use VBA. I have the following fields in a table - I want to update the value of the field Period_type as follows -
When Formtype is 10-Q, update Periodtype to "Quarterly"
When Formtype is 10-K, update Periodtype to "Annual"
The current value of Periodtype for both formtypes is "Semiannual"
I have a table that has one field and I want to use this field as a query criteria . when i click on CRITERIA & BUILD, Access lets me select the table field and shows [tblBillRun]![Bill_Run] for the criteria but when I click RUN, it prompts me to ENTER PARAMETER VALUE. What am i doing wrong?
I have two date fields in a table. I need to find the days between these dates and, if its greater than 7 days, I want to display the record in a report, so far, I have found a hand function that allows me to enter two dates and it returns a long data value representing the number of days in between the two dates. (google "I've developed the following code to count the business days between 2 dates." and its the second one that starts as SOLVED I made no mods to it as it does what i need it to do.
So, i added this to its own module within the data base for use within a query. My test query basically displays the unique ID, the start date and the end date and then displays the values returned from the function. here is the SQL:
SELECT [Main Table].[Unique ID], [Main Table].[Start date], [Main Table].[End Date], (Business_Days_Between([End Date],[Start date])) AS [Days between] FROM [Main Table] WHERE ((([Main Table].[Start date]) Is Not Null) AND (([Main Table].[End Date]) Is Not Null));
When ran, this Query works... However, when I enter a criteria like =2 or > 7, it says data type mismatch. I have even attempted the CInt() function to make sure its formated as int but i continue to get the same error.
