apply my situation / formula to others who had similar questions, but I get the #error output with no messages from access telling me what I did to cause this.
What I'm trying to do is create a formula that checks if two conditions are met, then applies an output. So I have a starting location [StartLocation] and [Stop2]...Both can be a small variety of locations.
Currently I have as follows:
Leg1: iif([StartLocation]="Location A" AND [Stop2]="Location B",500,0)
The formula would run longer in the end, going up to 10 stops, nesting the ifs and checking multiple locations for each stop.
Both my conditions are Text, and I want a number output depending on the location. Is it a simple error I'm looking past and missing? Or is what I'm trying not possible, I feel like it should be relatively easy. Access give me no trouble for save and running, but it outputs #error.
I got a table salaries master I want to extract some info out of and calculate some values. The formula below doesn't work, I'm pretty sure it has to do with [pay period] being a text field. Is there no way to make it work?
My name is Amanda, and recently I’ve started using Access again for work. I haven’t touched the program in about five years, so I am rusty. In addition, it turns out that I am the only one in my department who’s ever even opened Access. I’ve tried hunting down a manual around here, and it seems to be an ever ending scavenger hunt. I also have not been able to find the answer to my question via web, FAQ, and experimentation, although, I feel it’s a fairly basic question.
So here it goes. I want to return all fields that contain part of a text. For example, I want all fields that contain the word “lux”, regardless of what else the field contains to be returned. So if a field contains “lux. cars” it will be returned in my query.
I’ve tried goggling “Microsoft Access” with “text formulas,” however, all I receive are excel formulas.
it displays a div/0 error which i want to remove, i know i need to put this calcualtion in an iif statement but whenever i try and make the formula it never works
I currently have the following formula as the control source for an Entitlement field: =Round([Position1]/[Stk Ratio 2]*[Stk Ratio 1],2). Occasionally Stk Ratio 2 or Stk Ratio 1 is unknown and users enter 'TBC' but obviously this returns an 'Error!' where the formula sits as its not numeric.
How could I adapt this to pick up if Stk Ratio 1 or Stk Ratio 2 contains 'TBC', overide the formula to convert to text or just somehow show 'TBC' in the Entitlement field?
From a performance perspective, does it matter in what order a number of clauses are specified ? For example if many records satisfy ConditionA but few records satisfy ConditionB, is it better to put ConditionB first ?
SELECT Fields FROM Table WHERE ConditionA and ConditionB or SELECT Fields FROM Table WHERE ConditionB and ConditionA
I have the following query. I got an error when I ran it.
Code: SELECT COUNT([encounter_number] WHERE status = 'Death')/ COUNT([encounter_number]) AS Death Ratio, tbl_test.facility_type AS Type, FROM tbl_test GROUP BY tbl_test.facility_type;
I think the problem is that first WHERE clause inside.
I've been trying to put a formula in a text box within a form, with not much successes.
What I want the formula to do is:- From the Date field in the form, if there no date entry's then display a Blank, if there is only one date entered, I would like it to display the time in week since that entry. But if there are multiple dates entered I want it to display the Ave time between the dates, in weeks. BUT only as far back as 12 months. Any dates further back can be ignored.
I have a parameter query with a totals row that displays averages. Is there a way to have the average row use only specific records in its calculation based on one of the field's values WHILE still displaying all the records returned by the query.I want only data that has a "YES" value used in the average while still displaying the records marked as "NO"
I am trying to make a query that outputs the minimum "Need Year" AND ALSO if the need year was equal to 9999 it shows "NO DATA".
This is what I have so far for checking the minimum value:
field: Need Year: MinofList(PMS_output!pqi_ny,PMS_output!iri_ny,PMS_ output!sdi_ny,pms_output!sai_ny)
I am not sure if I should be putting it in the criteria to check whether this minimum value (need year) equals to 9999 or not and if it does, it says "NO DATA" instead of 9999.
I want to create a calculation query that uses different equations under certain conditions. Here's specifically what I need:
If the "Cost_Category" field is "Full Price" then the query uses the following calculation: Total_Cost: Sum(nz([Program_Cost])+nz([Millage_Fee])+nz([Auditorium_Cost]))
If the "Cost_Category" field is "BOCES" then the query uses the following calculation: Total_Cost: Sum(nz([BOCES_Number_of_Participants])*nz([Cost_Per_Person]))
I have successfully created these two queries individually, but combining them doesn't seem to work. Here's what I wrote:
It keeps coming up with errors, saying that I misplaced a comma, parenthesis or quotation. I've tried playing with it, changing the syntax slightly but it doesn't seem to work.
Basically I would like to populate column three "OUTCOME" according to the following logic:
if train A10 has multiple destinations (we see this in the second column) then in outcome I woudl like to have "multiple destinations" if the destination is only one (in case of trains A15 and A16) , then I would like to have that city in column 3 "OUTCOME".
I am trying to paste this formula into one of my queries, but I get the "the text is too long to be edited" when I paste the formula into the designated field. Is there anyway to get this paste without getting the error?
Each person have an employment date on which a 3 year cycle is based.
So at the end of their 3 years the cycle needs to update to the next 3 year cycle.
For example Person Employment date Cycle Start 1 01 Jan 2015 01 Jan 2015 2 01 Jan 2006 01 Jan 2012
In the case of Person 2 above the cycle would have started at 2006 - 2009 and then moved to 2009 - 2012 and then 2012 - 2015 and now 2015 - 2018
I have the following formula at the moment but need to make 100% I'm not missing something.
Also - would it be fine to run the update query each time for all persons or must it be only for the ones where the current date does not fall within the current 3 year cycle?
Need a formula which can capture filename for me. For example, I have a db saved at "C:DlocationofficeChina.accdb". Is there a way to capture "China" in a query?
I have a field in my query which returns results based on a formula that is a function of other fields. The results are: Pass and Fail. I want to make a query that returns only Fail rows. When I enter Fail as the criteria, a parameter box pops up requesting information be entered before continuing.
I have a routine that exports the results of a query to an Excel file. Is it possible to input the formula into the query so that the Excel values calculate?
This is the formula I am trying to pass to the "AZ" column of the Data tab
I have a table linked to SQL Server 2014. As SQL Server 2014 does not support calculated fields I created a query to use formulas. Now I want to write formulas on the existing fields ( TotalMarks ) of table Not to create new fields.
date formula that I was using in access 2007 doesn't seem to be working in 2010.
The formula that I had was
Between DateSerial(Year(Date())-IIf(Month(Date())<4,1,0),4,1) And DateSerial(Year(Date())+IIf(Month(Date())>3,1,0),3 ,31).
The calculation allowed me to count holiday hours taken between 1st April and 31st March. The problem is that it was working up till 31st march but is not now showing holidays taken since 1st April.
Hi All:running Access 2000.I have a qry with a formula field that performs some calculation (Extension: CCur(([OnHand]*[Price])/[UnitsPer]))and it works fine. How can I update the actual table with the result of the calculation for every record? The problem I have is that there's some null values in the results therefore when I try to get totals on that field, it errors out saying "invalid null use"thanks
Date of Birth (DOB) field etc. in one program are text - how do I make another file with the same data into number fields for Date of Birth field etc? When I copy data to file that has number fields the 09252004 is changed to 9252004. Can I get reports with the correct Date of Birth in them by moving data from text file to number file?
There is data entered monthly in file and formula has been set up for January, February etc as ---quarter: Int(([month]-1)/3)+1. I would like formula for the fiscal year for April to be counted as month 1, May - month 2, June as month 3, July as month 4, August as month 5, Sept as month 6, October as month 7, Nov as month 8, Dec as month 9, Jan as month 10, Feb as month 11 and March as month 12.
