Query Conditional Criteria
Jul 10, 2006
Hi guys,
I have a form with an option group. It has 2 options. The first one has a value of 1, the second has a value of 2. This option group is named "category".
Separate from this I have a query with several fields. In one of these fields, I need to enter a criteria that enters "LOE*" if the option group value is 1, or if the value is 2, I need it to enter "IDC*" in the criteria. My form that the option group is on is named "SelectWell". This is what I have tried so far:
Like IIf([Forms]![SelectWell]![category]=1,"LOE *","IDC*")
This works correctly if the option group value is 2, but doesn't work for the 1 value (LOE*).
Thanks,
Chris
View Replies
ADVERTISEMENT
Sep 21, 2006
Hi, I have the following situation:
I have two tables: userdata and superusers and a function username() which can be used for acquiring the username of the active user.
Table userdate contains a column 'username'. With the following SQL query the active user can obtain the records from the userdata table belonging to himself with a query like:
SELECT * FROM userdata
WHERE userdata.username = username()
Now, what I want to do is that IF the username (obtained using the function call (username()) exists in table superusers ALL records have to be shown, just with:
SELECT * FROM userdata
I want to do everyting with only one query, so that if the query is executed either all records from userdata are shown (when username is in superusers table) or only the ones that belong to the users.
Is there something like a conditional statement to create a conditional WHERE statement, or maybe another solution to solve this problem?
Thanks in advance.
View 5 Replies
View Related
Dec 23, 2014
I'm populating an unbound ComboBox with a Query via the QueryDesigner. The value of the Rowsource depends on the value of another ComboBox at the main Form.
After several hours of trial and error, I came to the solution showed at the attached image ("works"); my concern is.. why does my first approach does not work via the IIF clause? ("not work")...
View 2 Replies
View Related
Dec 9, 2005
Hello,
I've got a continuous form containing more than one record (5 to 30). I present the data of these records in text boxes. I need to color the boxes according to the data that they contain. This means that the same field can have different colors in different records. The total number of colors that is about 8 (correspondending with 8 different codes that can be in the data fields behind the text boxes).
Normally I would use Access standard conditional make-up function to implement the functionality described above. However the limit on the conditionl make-up criteria for each field is three (also if you use the formatconditions object in VBA) so that is not sufficient in this case. Is there any way to add more than three conditional make-up criteria to a text field?
Any help would be very much appreciated.
View 1 Replies
View Related
Feb 27, 2014
I am trying to design a delete query that has an additional criteria needed.
I want to delete identical IDs in one column only if there are identical values in an adjacent column.
So for example, in the table below I want to delete the last row where the duplicate ID is "2700023" because the Code field has identical values, "LRAC". I do not want to delete the second row because the Code field is different for the row.
ID Code
2699974 BCC
2699974 SRAC
2700023 LRAC
2700023 LRAC
View 1 Replies
View Related
Sep 24, 2014
I have a report which I would like to apply conditional formatting on multiple fields. I would like the conditional formatting to be based on two types of criteria
First criteria would be contracts that start with the year 2014. I used the following expression which worked - InStr(1,[ContractNr],"2014" AND [DirectorInCharge]= "Joseph Steinbok" or "Adam Godson")
To this expression I would also like to highlight fields which contracts start with 2014 AND have a particular Director assigned to it. For this I used the following expression - And [DirectorInCharge]="Name"
On their own, both expressions are working but I want to combine them. How do I do this? I've tried the following - InStr(1,[ContractNr],"2014" AND [DirectorInCharge]= "Name") but then nothing is highlighted. I also tried InStr(1,[ContractNr],"2014") AND [DirectorInCharge]= "Name" - in this instance EVERY record was highlighted.
View 4 Replies
View Related
Oct 21, 2006
I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).
However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.
Any help would gratefully be appreciated. Thanks
View 5 Replies
View Related
Jul 13, 2006
Hi - I wonder if anyone could possibly help, I have a list box that is populated by a query, which currently populates the list according to which site is selected within a combobox - what i would like to do in addition is to have a check box which controls how much data is shown, ie, if the checkbox is ticked show all data from April 1st onwards, if not ticked to only show the last 12 weeks (84 days)
I have tried to acheive this using the code below:
[Forms]![frmPccAnalysis]![cboSelect] --is used to select the site--
IIf([Forms]![frmPccAnalysis]![chkFilterLimit]=-1,(Between #01/04/2006# And Now()),(Between (Now()-84) And Now())) --is what i am trying to use to either restrict or show all data--
when i run the code i just get empty fields, if i manually enter the "Between #01/04/06# and Now()" statement in the criteria it works fine, just not with the conditial checkbox. Can anyone please point me in the right direction?
Many Thanks
Ian
View 4 Replies
View Related
Aug 24, 2007
My Access is very rusty and I really can't get my head round how to do this!
I have a simple caller i.d. system which detects incoming telephone calls. I have been running an append query where the incoming call number is a query parameter against a table of customer numbers and names. The append query basically adds call numbers, names and times to a call log table.
This all works fine! ...however I realise that if the incoming call number is not in the table of customers numbers and names, no log entry is created. What I wish to do is, if the number is not found to append it anyway together with the time of call. Basically if the query fails to find a result, append details anyway albeit without a customer name.
I'm sure this must be simple but I'm going round in circles. Any thoughts anyone?
Thanks
View 3 Replies
View Related
Nov 30, 2007
i have a query from 2 table. let's say table a & b. i have one form to show the query. the query show data based on what enter on the form.
when someone enter a value, on form, the query use it as filter for displaying data. my problem is one field of my query show data from the second table.
if input is AA, the data will be shorted by AA plus showing first colum of table 2.
if input is AB, the data will be shorted by AB plus showing second colum of table 2. etc.
so far, i make several query for each kode to display the corect column.
can i use 1 query to do that? if not, what should i use? macro/module? please the example aslo.
thanks
View 1 Replies
View Related
Jan 10, 2005
Hi,
I have a query that combines fields with an IIF statement adding a comma as the seperator "," is it possible to put a condition on it so that if a field had soley an integer or even a string with a value of 4 or less characters that the comma will be excluded, so for example:
A1 A2
1 Parlmont avenue
changes to:
1, Parlmont avenue
want it to change to:
1 Parlmont avenue
this is how the IIF statement looks like:
IIF(IsNull([I1].[A1]),[I1].[A2],[I1].[A1] & IIf(IsNull([A2]),"",", " & [A2])) AS Expr2
where I1 is table and A1,A2 are first and 2nd fields respectively, any suggesetions very much appreciated.
M-.
View 7 Replies
View Related
Mar 19, 2006
I have a query that brings up a job#, its start date (mm/dd/yyyy), and its
priority status (Low, Medium, or High).
Is there SQL I can use to make another query that will look at the start date,
and if it is a week old, change the priority status to Medium, and if it's a
month old, change the priority to High?
Thanks!!:)
View 4 Replies
View Related
Jul 7, 2006
HI
I am working on a database that holds communications from treatment episodes.
the sub form holds the communications related to that client and that treatment episode.
when you enter a date into the communication record a mesage box asks if this is a new treatment episode and if yes opens a new treatment record. If it is the same treatment episode it just remind you that you are in the same treatment episode.
what i would like to do as an additional cue to the user is that once a client has been discharged from the episode that all communication records change color.
I can get the field that says dc to change color but I want all the fields in all the records from that episode to change color.
any ideas?
thanks
View 1 Replies
View Related
Jan 3, 2007
Happy new year all. I have 2 tablesI have a query that if any tbl2.num=tbl1.num AND tbl2.action = 'confirmed' this row should be selected for deletion from tbl1. I've tried linking the tables and using an iif statement, but no good and I'm not sure what else to do- any ideas?Forgot to add- tbl1.num might or might not exist on tbl2
View 2 Replies
View Related
Feb 28, 2007
I have a query set up to prompt users when their license will expire (30 days out). Problem is that there are 2 types of licenses and some have different dates. I would like to have a report where I could see all the people that are about to expire either it be one license or the other or both. Thank you please help.
View 1 Replies
View Related
Feb 5, 2007
Dear all,
I have a query that is based on orders table and contains "Qty", 'UnitCost', and "ctualUnitCost", in addition to yes/no control to indicate if "SalesTax" is applicable.
I want an expression in the query to calculate total cost in one of 2 conditions:
if no sales tax then TotalCost = Qty*UnitCost
if sales tax is applicable then Total cost = Qty*UnitCost*1.1
how can I write this expression
thanks
View 2 Replies
View Related
Mar 29, 2007
how do i use conditional statement in sql? im using access 2003 as frontend and backend.
i would like to count the number of records in a table (ex. ENROLL) where there is only 1 instance of any value in a field (ex. STUDENT_NO) plus another condition where a given value (ex. 1) should exist in another field (ex. SECTION).
example
Table name: ENROLL
STUDENT_NO SECTION FEE
16 ----------- 1 ------ 75
16 ----------- 2 ------ 75
17 ----------- 1 ------ 100
18 ----------- 2 ------ 125
19 ----------- 1 ------ 75
19 ----------- 2 ------ 75
19 ----------- 3 ------ 75
20 ----------- 1 ------ 150
Parameters:
COUNT
The number of records (count) in the table where there is only one instance of the STUDENT_NO value and the corresponding SECTION value is 1
VALUE
The sum of the FEE values in the table where there is only one instance of the STUDENT_NO value and the corresponding SECTION value is 1
The answer should come out like this:
count = 2
value = 250
Can anybody please help me to construct the SQL statement?
Thanks in advance!
View 4 Replies
View Related
Mar 16, 2005
Hi,
I have problem in creating conditional expression for query table. I want to add IF Else statement in the statement so that if the value is more than 50, a new field called grade will display the grade of "A" in the new field.
Can anyone teach me how to write conditional expression.
Thanks
Dawn
View 4 Replies
View Related
Aug 20, 2007
I have data for hundreds of stores. The data was pulled for the top 15 items by store, so I cannot obtain only the top 5 items that I need. How can I query this data to extract only the top (or bottom) 5 Subjects, by store, based on the percentage column?
StoreSubjectSalesSales %
1516Fiction56431.5-24.15%
1516Audio Unabridged1650.8-231.04%
1516History / Military History10081.1-29.99%
1516Role Playing / Graphic Novels14773.9-20.27%
1516Mystery13152.6-19.84%
1516Audio Abridged1785.9-141.84%
1516SciFi / Fantasy27535.3-7.93%
1516Juv Audio/Video1580.6-100.13%
1516Biography8103.6-15.89%
1516Sports7910.8-15.64%
1516Current Affairs / Law8141.9-14.34%
1516Reference7183-16.22%
1516Juv Non-Bk4585.9-25.02%
1516Science / Tech2961.4-33.98%
1516Movies / TV / Music / Dance3395.3-29.46%
1872Fiction307344.3-7.49%
1872Business134307.5-13.48%
1872Psych / Self Improvement100650.4-10.05%
1872Audio Unabridged29165.9-27.32%
1872Cookbooks57463.3-13.56%
1872Computers59235.7-12.37%
1872Regional59883.4-11.22%
1872Health & Fitness64713.8-10.29%
1872Maps19358.4-27.66%
1872Current Affairs / Law47927.1-11.08%
1872Travel Foreign42583.7-12.27%
1872Religion / Bibles80255.6-6.07%
1872SciFi / Fantasy67641.4-6.49%
1872Study Aids / Notes38299-11.24%
1872Games41745.1-9.79%
View 2 Replies
View Related
Oct 17, 2007
I have an interesting report query I am working on. Is it possible to have a query that shows different fields for individual records depending on the content of the record? Let me see if I can explain. Let's say that I have a Status field that can be "Complete", "Active", or "Planned". And lets say also in that table I have Field1, Field2, and Field3. For each record, I don't want to include all three fields (Field1, Field2, Field3), but only one of these fields depending on what is in the Status field.
So, it would be something like:
Select Status, (if Status = "Complete", select Field1), (if Status = "Planned", select Field2), (if Status = "Active", select field3) FROM table;
So, the query would result in 2 fields for each record: the status field, and one of the three other fields depending on what is in the status field for that record.
Thank you kindly for your help.
View 2 Replies
View Related
Feb 13, 2014
i need to clean up a date field (I hate dates in access!). The field in the table (imported from a spreadsheet) has records where users have simply entered a date in the correct format and then others where users feel obliged to add comments after the date rather in the 'Comments' section. Therefore Cdate alone won't work.
So far i have tried:
Code:
IIf(Len([TabDL].[PROMISE]<>10),#1/1/1900#,CDate([TabDL].[PROMISE])) AS PromiseDt1
The intention being to insert a holding date 01/01/1900 if the field contains too many characters to be a date alone. I've tried multiple variations of this code but keep getting errors across the board. Is my logic or Syntax flawed?
View 11 Replies
View Related
Feb 4, 2014
how to conditionally format query results? I have read another forum post that says that query conditional formatting is possible.This database keeps track of projects and their associated tasks & statuses. I created different queries depending on task name( i.e. assignment date, approval date, etc.) Now, with some of the tasks, if it is overdue, it needs to be highlighted red. If it's cutting it close, should be yellow. I can't figure out how to set up a conditional formatting rule to address this. So instead I've had to create to separate queries for "red" and "yellow" rules, and display them separately in the Dashboard form.
View 4 Replies
View Related
Mar 6, 2014
So I have two tables:
users
primary key = user_id (AutoNumber)
surname (Text)
forename (Text)
...
user_change
primary key = user_change_id (AutoNumber)
user_id (Number) which relates to the user_id from "users" table
change_type (Text)
action_date (Date/Time)
...
In user_change I record any changes made to the users table.
What I am wanting to do, within a query, is pull basic details from the "users" table (forename, surname, etc.) which is working fine, but also add in SPECIFIC data from the user_change table if it exists.
I want to pull ALL rows from the users table, not just specific rows, and not just rows where my criteria for the user_change table match.
This is the data I want to pull from user_change...
The MOST RECENT action_date WHERE change_type is LIKE "*issued*".
However there won't be a change_type LIKE "*issued*" for everyone - I want it to be included only if it exists.
At the moment my query is ignoring any users who don't have a user_change record with "*issued*" in the change_type value. I'm also getting duplicate user rows where people have more than one value for "*issued*" - I only want the most recent one...
View 7 Replies
View Related
Apr 2, 2012
I use an access db that stores our customer info on a mssql backend. I need to be able to create a mail merge based on a customers renewal date. Now I do know how to set parameters in a query but is there a way I can use a access report to enter a month and day they are set to renew then have it create the mail merge based on their info?
The Layout is fairly simple just a renewal notice with what they have on their plan.
View 6 Replies
View Related
Mar 26, 2013
I have a simple crosstab query:
FirstName and Surname as Row Headings.
Date as Column Heading.
OnShift as a Value (Count).
These are from the table DailyActivityLogs. Also in that table is a choice field called Weather that lets you choose the conditions that day (Weather, Work, Part Weather, Subbed).
This is the report the crosstab query generates:
What I would like to do is colour the Count fields depending on what the weather was that day. It seems possible, as Weather sits in the same table, but the field Weather isn't in the crosstab query.
Can I do this?
View 3 Replies
View Related
Mar 5, 2014
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
View 1 Replies
View Related
