Conditional Query??

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 Replies


ADVERTISEMENT

Conditional Query Using Between

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

Conditional Query?

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

Conditional Query

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

Conditional Formatting Query

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

Conditional Formating Of Query

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

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 2 Replies View Related

Query With Conditional Criteria

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

Conditional From 2 Tables In Query

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

Conditional Formatting With A Query?

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

Query To Calculate Conditional Field

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

Need Help How To Construct Query With Conditional Statement

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

How To Write Conditional Expression In Query?

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

Conditional Query For Selecting Different Fields For Each Record

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

Queries :: Conditional Change To Date Query?

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

Queries :: Conditional Formatting Query Results

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

Queries :: Populating ComboBox Via Query With Conditional Criteria

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

Queries :: Conditional Query Pulling Data From Two Tables

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

Creating Conditional Mail Merge From Parameter Query

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

Queries :: Conditional Formatting On Column Not Listed In Crosstab Query

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

Queries :: Conditional Query To Post Result In Field And Filter Result Records?

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

Conditional Formating Help Please

Apr 17, 2006

Good morning, all...

I have an unbound box on a form and, within the box are 25 strategically placed "X" marks (text fields) named X1 - X25. I want only one of the X marks visible based on the value of two fields, i.e., if field "Impact" = 0.5 and "Probability" = 0.3 then X23 will be visible.

Could someone please get me started with the code to accomplish this?

Thanx in advance for your assistance.
Karen :)

View 2 Replies View Related

Conditional Formating

Jul 13, 2005

Trying to setup a conditional format for a field on a form based on conditions from another field.

I have a form with [Field1] and [Field2]

I have a 3rd field called [New SMF]

What I want to do is have conditions on [New Field] so that if the value is greater than Field1 or less than Field2 the background of new field turns red.

How can I do this?

Jon

View 4 Replies View Related

Conditional Formatting

Apr 18, 2006

Hi Guys,

I've a question and I hope you can help me out.......
I'm trying to use Conditional Formatting Options on a Text box called "Date". I'd like that this box become Red if its value is between value1 from Text box "Start Date" and value2 from Text box "End Date" or Green otherwise (in this way the color change dynamically every time I change the values of Text Boxes "Start Date " and "End Date"). How can I do that? What I need to type in the Conditional Formatting Windows?

Thanks a lot for you help!!!!

Ciao Ciao

View 1 Replies View Related

Conditional Formatting

Aug 13, 2007

Hello

On my Database I have used Conditional Formattion to change the colour of the qty to highlight when the qty is getting low however its not as good as I would hope as there are different type of definition of quantity (D of Q) e.g. Each (single items such as a screw) and Roll (such as cord and tape)

Conditional formatting doesnt work well because if I have it as less than 10 the qty is in red, its ok for the each items however not the roll as 10 rolls of tape is more than we would need.

I am just wondering if anyone knows what the code would be to change the colour depending on the qty and the D of Q

All i have got so far is:
If me.qty <10 and DofQ = EA then

Now im stuck for the change colour part

Any help would be great

View 13 Replies View Related

Conditional Formatting Help

Sep 2, 2007

Hi Guys,

I'm after an easy way on being able to change the colour of a field once it a certain figure is reached

For instance

Standard field colour for anything 1-99
100+ changes to red

Would any be able to help me out please?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved