Create Conditional Expressions In A Table
Sep 27, 2014
I am trying to create a conditional expression in a table.I am trying to say the following:
If [KIND] is "DEER", return "70", Otherwise, if [KIND] is "ELK", return a calculation that is [WEIGHT]*.7.
I am hung up on the calculation for the "ELK." If I write IIF([KIND]="DEER","70", IIF([KIND]="ELK", "[WEIGHT]*.7","OTHER")).
I can enter the expression, but when I open my Form and select "ELK", the field that is supposed to have the calculation just shows [WEIGHT]*.7.....it does not perform the calculation.
View Replies
ADVERTISEMENT
Mar 6, 2013
I want to have Access run a query, open a report, and print the report based on the ID number of the form that I have open. Is this possible?
The situation would be, I have a form that uses the the primary key as a record number. After I enter the data into the form, with the form open I want to add a button to open and print a report of the current record number.
View 3 Replies
View Related
Sep 6, 2005
I guess I need to create an if else statement for one of my fields in a query. The query basically calculates a dollar amount from certain fields in one of my tables and sums them together at the end with an expression.
I also have a true/false field in the same table. and if it's true then the expression in my query must equal 0, if false then it sum up the fields. Do I need to create a module for this or is it possible to do this directly in query? And if so, how?
Thanks,
View 2 Replies
View Related
Jun 22, 2006
I am trying to implement a conditional validation Rule. I think it goes like this:
=IIf(([cmbCC].[Column](0))=1,Between 10 And 20,6)
For some reason the true part (Between 10 And 20) is not working even though if I enter a number like :
=IIf(([cmbCC].[Column](0))=1,20,6)
is working just fine. Can someone help me on this please?
View 4 Replies
View Related
Feb 22, 2006
First off, I am not sure if this belongs in the queries forum but it seems most of the question is geared towards a table so I have posted it here.
At the moment I have a query that contains several fields that use nested IIf statements to determine which set of rules to apply. I was wondering if it is possible to somehow set the IIf variables up in a table and then for just to lookup the table in the query.
For example, one of my simpler IIf's looks like this...
Cost:
IIf([AType]='Skill',
IIf([StartDate]<#01/08/2004#,
IIf([AOP]>0,
([BasicRate]-[Frame])/[AOP]/100*[Proportion]),
([BasicRate]-[Frame])/[OP]/100*[Proportion]),
([BasicRate]-[Frame])/[OP]/100*[Proportion])
So I was thinking could I setup a table to show:
FieldName AType StartDate AOP Calculation
Cost Skill <#01/08/2004# >0 ([BasicRate]-[Frame])/[AOP]/100*[Proportion])
Cost <>Skill Is Not Null Null ([BasicRate]-[Frame])/[OP]/100*[Proportion])
and then somehow perform the calculation rather than just look it up.
The main reason is it would be so much easier to see what is being calculated rather than try and weed my way through nested IIf statements.
Can anyone shed any light on this or maybe even a better way to accomplish what I am after?
View 1 Replies
View Related
Jan 30, 2013
I have two tables "Funds" and "Classes" with one-to-many relationship, resp. (through the "Fund_name" fields). There is the field "Active" in the "Funds" table and the field "Open/Closed" in the "Classes" table. What I would like to do is to put "No" value into the "Active" field record if the corresponding (with respect to the relation of course) "Open/Closed" field records have all values "closed". Otherwise put "yes". Is there any way, how I could make this?
View 14 Replies
View Related
Nov 4, 2013
I have a table in access and I need to format it so that when column "Status" = "Closed" column "Closed" = "yes". so it will automatically mark column "Closed" "yes" if "status" says "closed". Instead of having to change both columns all the time.I know how to do that in MS Excel, but is it possible in MS Access 2013?
View 3 Replies
View Related
May 14, 2007
Hi all-
I work for a staffing firm and I'm trying to figure out a way to turn exports from our vendor-based sql gui (i.e. I can only get .xls files out of it, can't just run SQL queries) into spiffy reports using Crystal Reports. My grand plan is to use Access to process these various XLS files (one for each client) by linking them to an Access DB and coding a table to conditionally tally individual records based on different criteria.
The criteria are company name, position name, date submitted, active, and status. I was able to code an Excel template that did the trick perfectly, but required each file to be edited to reflect the new report format, and updated as more positions are added. My question is this: is there some way for me to create a DB that links to the XLS files for each client and puts each company name, position name, date submitted (pulled straight from XLS files) and then the following tallies:
submitted (just # of pos name by date)
accepted (status != "s" AND active = "y" by date)
etc.
I don't really expect someone to do all my coding for me, maybe just point me in the right direction, as the only coding outside of excel formula coding (which I feel doesn't really count despite my proficiency) that I've done has been over a decade ago. Any suggestions?
View 4 Replies
View Related
Oct 15, 2014
I have a form which basically has - employee, work done, hours and a calculated field (w/hour) of work done/hours
I can manually set the conditional formatting however as time changes targets will go up and it would be easier to update the targets in another 'admin form' (i can manage that part)
Its just referencing the other table to say the likes of :
if work per hour is greater than tbltargets.convtargetmid then vborange, if work per hour is greater than tbltargets.convtargethigh then vbgreen else vbred.
View 3 Replies
View Related
Apr 11, 2013
I am writing some iif conditional statements in one of my tables to assign new values in that table.
Code:
FP: IIf([cohort with status].[Intake Date] Between #04/01/2012# And #26/04/2012#,201201) Or IIf([cohort with status].[Intake Date] Between #27/04/2012# And #24/05/2012#,201202)
When I run it, I am getting some -1 values in the new column FP.
but if I get rid of the second IIF, then it works.
how do you combine multiple IIF statements in a query?
View 1 Replies
View Related
Mar 4, 2008
I have a table that has questions that require Yes/No answers, simple done, however now for the tricky (or at least for me, VV new to Access) I need one of the fields to add up all the Yes and asign a value so for example:
Q1 Q2 Q3 Q4 Q5 Result
Y N Y Y N 3
How do I do this, I asume it is down to the expression for 'Field' Result but what should that expression be and where do I put it?
View 7 Replies
View Related
Jul 29, 2006
Help me what is wrong with this expression:eek: :
=IIF( [eBay Method] ="Auction",
IIF( [Start up Price] <=.99,.2,
IIF( [Start up Price] < = 9.99,.35,
IIF( [Start up Price] < =24.99,.6,
IIF( [Start up Price] < =49.99,1.2,
IIF( [Start up Price] < =199.99,2.4,
IIF( [Start up Price] < =499.99,3.6,
IIF( [Start up Price] >499.99,4.8," "
))))))),
IIF( [eBay Method] ="Buy it Now",
IIF( [Start up Price] <=9.99,.05,
IIF( [Start up Price] <=24.99,.1,
IIF( [Start up Price] <=49.99,.2,
IIF( [Start up Price] <=49.99,.25," "
))))))
View 4 Replies
View Related
Nov 4, 2014
I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).
Currently, I setup the query to pull info from the form field like this:
DateField: [Forms]![frmmain]![DateField]
However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).
View 6 Replies
View Related
Jun 4, 2015
I have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.
If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.
Note: except the Name column, none of these tables have any other columns in common How do I go about this?
View 7 Replies
View Related
May 1, 2012
Here is what I am trying to do: I have form, and a table which is created from this form. The data is entered a second time using the form to make sure there are no discrepancies.
Any easiest way to implement this? (create a new table (which is duplicate), and then compare them?
View 5 Replies
View Related
Jul 13, 2005
I'm using Windows 2K SP4 and have just installed Office 2003. Everytime I open Access I get a message asking if I want to block unsafe expressions (presumably macros). How can I turn this off?
BTW I've downloaded all of the security updates, which are recommended by MS.
View 7 Replies
View Related
Apr 5, 2007
Hi,
Can anyone tell me why it is that when i have written a DB on one machine using Access 2003 and then subsequently put that DB on to another machine i get an error relating to unsafe expressions?
why does this happen and is there a away in which can stop this error message from appearing?
I am trying to implement the DB i have written at work but it does not look good when i get a constant error message appearing when ever i open the DB.
Thank you in advace for your help
Regards
Bev
View 3 Replies
View Related
Feb 29, 2008
1)I have a list of stock opening prices and closing prices for March. i need an expression or function that will calculate which company's stock price changed most during the month (difference between opening and closing price). 2)In the stock table, I have dates given to me in the following format mm/dd/yyyy. Lets say I want it only to display the date so it only shows the month but not the year of the day. What function will give me this result?Thanks
View 1 Replies
View Related
Jan 8, 2007
I have a complicated question here. I am currently building a db for career development in my workplace. The main fields that i am having some issues with is the following. I have three fields labelled as the following:
1)Last Review date
2)Next review date ( i have this entered in as an auto generated date-- will add one year from the last review date)
3)Status
Now here is my question:
I want my status field to auto generate as well-- say if the last review date was in 2005 and the next review date was dec.12/06-- then the status would currently be overdue.
Is there any expression to use to auto generate the status field to say whether it is overdue or completed? or is there any easier way to do this?
View 5 Replies
View Related
Jul 8, 2005
I’m starting to wonder how I ever made anything work, before I found this place.
I built 2 queries and put them in a query
They share a common field
The join type is all record from Query1 and Matching from Query2
Next I added a count field for each
Then I added a calculated field that subtracts the count from Query1 to Query2.
So far it all works
When I add >0 to the criteria for the calculated field a dialog box pops up asking for the values of the fields expressed the calculated field.
View 2 Replies
View Related
Jul 19, 2006
i have a master table with various lookup feilds. i want to create a querie that creates a text expression feild called description and it is based on the info provided in the lookup feild. problem is when i create this querie from the location master it provides the id's from the lookup colums and not their text.
any solution
View 4 Replies
View Related
Aug 1, 2006
Hi experts
I have some problems consists of using Conditions.
I have 4 tables in Access:
tbl1_School names, addresses, ID_Number etc..
tbl2_ School features with Number of Clesses, Number of Students, total Area etc.
tbl3_teaching stuff with names, occcupations, Ages ets. and
tbl4_nonteaching personal with names, occupations. ages, etc.
References is ID_Number of schools
I make Query with relations, but do know how to build Expressions Builder for this:
Question 1.
a)If school have between 300&500 Students there are ig.18 teachers,
b)if schools have between 500&800 Students there are ig.25 teachers and until 1500 Students there are ig. 50 teachers.
Question 2.
c)if school have area until 400 m^2 there are needed ig. 8 nonteaching personel,
d)if school have from 400 till 600 m^2 there are needed ig. 15 nonteaching personel.
Numbers I specified like examples ig. were prescribed bye regulation.
If someone want to help me how can I use Expression Builder or VBA Code it would be appreciated.
It means I should use Condition Commands.
Thank You for help.
View 6 Replies
View Related
Jul 17, 2007
Is there anyway to use an if statemet to define the criteria for a query?
as in
if forms!RunReport!cboname <> "" then
query all names that equal forms!RunReport!cboname
end if
also is there any way to use a dlookup function (or another similar function)
to define several criteria for my query
ex.
i have a table, "modules", with a list of modules, each classified as "digital" or "analog" and i want to view all records containing analog modules from a different table, "info"
thanks for any help in advance
View 4 Replies
View Related
Feb 29, 2008
Hi all!
I'm new to this forum and could use some help on a simple Access app. I have attached the relations for the db. I need to collect some data to a subform as:
tblFunctionCodes.FunctionCode,
tblAccounts.AccountNumber,
Monday (hours),
Tuesday (hours),
Wednesday (Hours),
Thursday (hours),
Friday (hours),
Saturday (hours),
Sunday (hours)
Where I have the tblEmployees.EmployeeID, tblDates.DateWeek and tblDates.DateYear as variables.
The weekdays need to be an expression with my current design, which seems to work ok from Monday to Saturday, but when I throw in Sunday Access says the query is too complicated.
I have this query to get the data, but with no expressions (can't get it to work):
SELECT tblFunctionCodes.FunctionCode, tblAccounts.AccountNumber, tblRegistrations.Hours, tblDates.DateWeekDay
FROM tblFunctionCodes INNER JOIN (tblEmployees INNER JOIN (tblDates INNER JOIN (tblAccounts INNER JOIN tblRegistrations ON tblAccounts.AccountID=tblRegistrations.AccountID) ON tblDates.DateID=tblRegistrations.Date) ON tblEmployees.EmployeeID=tblRegistrations.EmployeeI D) ON tblFunctionCodes.FunctionCodeID=tblRegistrations.F unctionCodeID
WHERE (((tblRegistrations.EmployeeID)=[intEmployeeID]) AND ((tblDates.DateWeek)=[intWeek]) AND ((tblDates.DateYear)=[intYear]));
I've been trying to use that as a foundation for other queries but I just can't get it to work. Does any of your guys have any ideas? Maybe an INTERSECT query??
Anyway I'm out of ideas and very pressed to makes this work. Any ideas are welcome!
Thanks in advance, jon.
View 10 Replies
View Related
Jul 6, 2005
Hi all, I'm writing a database basically as a favour to someone. I'm a sql analyst/programmer so my access and vba skills are a little limited and I hope someone can help me with a problem I have.
Part of one of my forms is a flow chart style set of Yes/No questions and the user answers each question. For each question I have created a (hidden) text box which calculates a score (5 for no, 10 for yes) which I have done with a IIF expression in the Control Source of the text box. These are then totalled to give an overall score.
This score is then used to calculate a grade (A or B) and a percentage score using the same IIF method in the control source.
What I want to do though, is use this grade and percentage and write it to the individual record as data in the table - to use in reports and such like.
Is there any way I can do this? I've tried a few bits of VBA but to no avail. Is there a way that this can be done? Or have I approached this in entirely the wrong way??!!? :confused:
Any help would be greatly appreciated!
View 2 Replies
View Related
Nov 24, 2005
Hi,
Is there a way on Access that when a total is calculated using an expression on a form that it could update the table itself with that total?
Thanks for any help!
Roly
View 1 Replies
View Related
