iif(isnull( in Query

I have a [Company] and or a [Contact]
The Client: is the [Company] if the [Company] isnull then [Contact]

How do i put this in a query statement

9/13/2007 8:05:43 AM
access.queries 6343 articles. 1 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 7

Client: IIf(IsNull([Company]),[Contact],[Company])



> The Client: is the [Company] if the [Company] isnull then [Contact]
> How do i put this in a query statement

9/13/2007 8:23:54 AM
On Sep 13, 6:23 pm, "Rob Parker"
<NOSPAMrobppar...@optusnet.com.au.REMOVETHIS> wrote:
> Client: IIf(IsNull([Company]),[Contact],[Company])
> HTH,
> Rob
> > The Client: is the [Company] if the [Company] isnull then [Contact]
> > How do i put this in a query statement- Hide quoted text -
> - Show quoted text -

Thanks for that

9/13/2007 11:34:21 AM

Similar Artilces:

How to update data in a query
One part of my database is used to keep track of contacts within clients. I created a query using a table and a crosstab query and exported it to excel. I had staff in my office update the contact information. Now I want to update the query with the new information from the excel spreadsheet. The spreadsheet includes updates to existing data and new record. How can I do this? It does not look like an update or apend query are what I am looking for. Thanks Brian On Mon, 5 Apr 2010 09:45:04 -0700, AccessIlliterate <AccessIlliterate@discussions.microsoft.com> wrot...

How do this tricky query?
I have 2 tables, a CASE table and an INVESTIGATOR table. This is a one-to many relationship. Each Investigator has an Assign Date and a Completion Date. There may be zero, one or many investigators per case but only one at a time. Completion dates could be missing: Investigator1 Assigned 12/10/09 Completed 12/10/09 Investigator2 Assigned 12/10/09 Completed null Assigned dates could be the same e.g. Investigator1 Assigned 12/10/09 Completed 12/10/09 Investigator2 Assigned 12/10/09 Completed 12/11/09 Or, different e.g. Investigator1 Assigned 12/10/09 Completed 12/11/09 ...

A query that shows "deletable" records
Hello Can anyone tell me how to find records in a table that has no related records in two other tables? I want to find the records that I can delete without having to just try record after record. It woud be nice if I also could delete records from that query. Thanks in advance. Lasse T ------------ Lasse I believe the Query Wizard includes a "not in" query. It sounds like you are trying to find records in Table1 that are "not it" Table2. Regards Jeff Boyce Microsoft Office/Access MVP "Lasse T" <cokew@home.se> wrote in message news:ICDMh.36...

Possible to Import Access queries to Excel and maintain the formulas??
Hello: I am trying to confirm whether it is possible to import access queries intto Excel and keep the formulas so that they will still exist in Excel. Is that possible to do? Thanks! Hi Fred, not without writing code -- and, depending on your calculations, this may be very difficult Warm Regards, Crystal * (: have an awesome day :) * MVP Access Remote Programming and Training strive4peace2006 at yahoo.com * fred.sheriff@gmail.com wrote: > Hello: I am trying to confirm whether it is possible to import access > queries intto Excel and keep the formulas so that t...

Passing an argument from a query to a command
I am trying to send a report using an email address in a DB. I wrote the following code for it. Private Sub Reminder_Letter_Command_Click() On Error GoTo Err_Reminder_Letter_Command_Click Dim stDocName As String Dim SendTO As String stDocName = "BG Reminder Letter" RunQuery MsgBox "This is After RunQuery " & SendTO SendTO = [Queries]![BG email only from email from ae code].EmailAddress MsgBox "This is SendTO 2 Value ==== " & SendTO DoCmd.OpenReport stDocName, acViewNormal DoCmd.SendObject acSend...

Editing MS Query
Hello, I am having a problem editing queries created in MS Query. I create the queries without any problems, and am able to add criteria and bring the query into Excel. But when I want to edit the query, I get the SQL box that pops up, and it is not easy to edit. Any suggestions? Thank you in advance. ...

USING IsNull Function in PivotTable
Thanks in advance for the feedback!! I create Pivot Tables that use the following code to create a calced field. The only problem is I get a table that has tons of zeros in it. I can't use the universal turn of zeros on worksheet because there are some zeros that don't result from a calced field that should be displayed. I was wondering if anyone knew how or if I could use the IsNull function so that resulting display would be a blank cell instead of zero. When I replace ,0 in the formula with ,"" the entire calced field disappears. ActiveSheet.PivotTabl...

Problem With Query Criteria
This is probably not very hard but I am very new to access and am trying to build an entire database. I am making a query from a table and I need to pull out records that are done in April in the Month field but I want it to leave out the records that are 13/1 and 13/2 in the Sheet No field. It will let me filter the April records taking out the 13/1 or the 13/2 but if i try to ask it to leave out both 13/1 and 13/2 it will no longer only take the records from the month of April. Any suggestions on how to learn more about expressions as well would help! -- Chels We're not the...

Running Sum Within Union Query
I posted this problem previously, received a solution that I thought would work but it became too large and received a "Query is too complex" error. So, I'd like to approach this a different way: I have created a Union Query to consolidate results from separate queries, each one of which represents results for a single month: SELECT ReportMonth, MonthlyRev, MonthlyCost FROM qryMonth1Total UNION SELECT ReportMonth, MonthlyRev, MonthlyCost FROM qryMonth2Total UNION SELECT ReportMonth, MonthlyRev, MonthlyCost FROM qryMonth3Total; etc. (for 12 months) Results show as: ReportM...

Running Update Query in VBA fails
Access 2003. I have this Update query called qryTopSalesUpdate :- UPDATE (tblCompany INNER JOIN tblContacts ON tblCompany.ID = tblContacts.ID) INNER JOIN tblOrders ON tblCompany.ID = tblOrders.ID SET tblCompany.Category = "3" WHERE (((tblCompany.Category) Like [Forms]![frmMailShot]![txtTopCategory]) AND ((tblContacts.TeleCalls)<>"Invalid") AND ((tblOrders.InvoiceNum) Is Not Null) AND ((tblOrders.OrderDate) Between [Forms]![frmMailShot]![txtStartDate] And [Forms]![frmMailShot]![txtEndDate])); which sets the Category Text field to '3' for the gi...

Combo Box executing query, issues
I am trying to set up a combo box that will execute a query. I've searched the site and haven't found anything that seems to help. Here is my situation. I have a combo box, cboZoneSelect, that has a source from a table, tblZone. The combo box populates fine with no problems. I have a query that pulls Table: tblZone tblDetail Fields: tblZone.Zone tblDetail.ZoneId(fk) tblDetail.Country tblDetail.Product tblDetail.StandardLevel tblDetail.PackageType. Under tblZone.Zone I have the criteria set to [Forms]![Switchboard]![cboZoneSelect].[AfterUpdate] When I run the query it requ...

Hopefully a simple combobox SQL query
Hi there. I've got a series of comboboxes: Manufacturer Category Sub-Category What I'm doing is making each box populate based on what is input into the one above. This I'm fine with. What happens when you select is that the top line of the combobox is empty and then the Select Distinct results appear below. What I'm would like to happen is that the box has a default of <all> in it and then the select results come below when you click on the drop down. I've tried setting the box default to <all> but the boxes still have a blank top line. Can anyone assist?...

Form parameter Query
I'm hoping someone can help me with this . I'm trying to get my query to use a form to get the query parameters. When I run the query, it displays the text "[Forms]![frmEE]![cboEE]" as a prompt rather than displaying the form where the user can make a selection. I'm using the build wizard to construct the following query: SELECT Employees.* FROM Employees WHERE (((Employees.LastName)=[Forms]![frmEE]![cboEE])); -- Any and all contributions are greatly appreciated ... Best Regards Thanks Karl. You've been a big help. Have a good one. "KARL DEWEY"...

Forms and Reports from CrossTab queries
Hi All, I have read a few postings in this area, but they all seem to be very specific to particular situations. Can someone please guide me towards the best solution to my dilema? I have a form with two list boxes which contain many variables (answers to specific questions from a survey). When two different questions are slected I run a cross tab query resulting in a dymanic result of rows and columns based on the selections. I now want to display the result as a datasheet on my form and in a similar fashion as a report. Any help is, of course, greatly appreciated. Regards, John D...

How to enter a long address to new web query
sup pros something like this http://tinyurl.com/e2n5e when i paste it , i get the message:- The text string you entered is too long. Reduce the number of characters used or cancel the entry -- Pivotrend ------------------------------------------------------------------------ Pivotrend's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=4062 View this thread: http://www.excelforum.com/showthread.php?threadid=400401 You should be able to get rid of http:// Unless you are still using IE2 or 3 "Pivotrend" wrote: > > sup pros > something like th...

Update query question 04-26-10
Hello, I would like to update a record by looking at the value from a form combo box column(1). I have tried Forms![Lab Works Manager]![cmbTo].column(1) but it does not work. The bond column(0) has the ID, but in this case I need to use the actual value from column(1) and not column(0). Any idea how? Thank you, Silvio Add a hidden control to your form and set its value to =Forms![Lab Works Manager]![cmbTo].column(1) Then you can reference that hidden control in the query. Your other option is to write a VBA function and pass in the name of the form, the name of the co...

Query to see what percentage of people scored a specific number
I am fairly new to access and have created a great database. However the queries are now becomming more complicated than I expected. I have two fields (Goal One and Goal Two) the nurse asks the patient have they met their goal. They use a 1-5 rating scale. 1=no, 2=sometimes, 3=likely, 4=most and 5=always. They select the number from a drop down list. I need to see how many patients were given a one, a two, a three etc.... and what percentage of patients were given a three or higher. I need to know this for goal one and goal two seperatly. There are many ways. A relatively simpl...

Use SQL to delete record from underlying query
I have a form that is a main and subform. The subform uses dlookup to find multiple values that are used as defaults for the current record. This process is complex and ends up using code to modify bound fields. This works great until I want to cancel the update. If the user wants to add a record, this form is loaded and all of the prior settings from the underlying tables are brought in. If I then click Cancel to close the form and not complete the new entry, the record is saved anyway. I understand why the record is saved by Access. I wanted to delete the just added record as a roundabout ...

query returns in different columns projects from different phases
I have a tblProject that contains projects and their phase of execution. Each project can be in one of the following three phases: Portfolio, Pre-project or Execution I want to show a table in a form containing the names of projects in each phase. I thought of using a query where the columns are the project phases and the query would return the names of the projects in each of these phases. Let’s say the query result would be: PORTFOLIO PRE-PROJECT EXECUTION Projetct1 Projetct4 Projetct5 Project2 Project7 Projetct6 Projetct3 ...

Access 2003 VB Code Export Query Failing with Access 2007 user
I have a set of excel export queries I created in an access 2003 DB. Now, some of my users have upgraded to Access 2007 and are running in to a "Parameter" error when the execute the "canned" excel export queries I created for them. My only presumption is that it is due to the fact that I have hardcoded the export function, to go to an Office 2003 Excel file, and for whatever reason, Access 2007 cannot handle this. This make sense? And if so, any suggestions? I suppose if there was an easy way to test what version of office/excel they are running, I could change...

Passing parameters to a Web Query
I'm not an Excel expert so bear with me. I want to create a Web Query but I want to pass parameters from the Worksheet itself... Something like this: http://webSite/somepage.asp?startDate=summary!a1&endDate=summary!b1 Essentially, my ASP page needs a start and end date to pass to an SQL stored procedure. Is this even possible and if not, do I have any other options? Took me a while, but I found how to do this. "Raymond D'Anjou" <rdanjou@kindervillexxx.com> wrote in message news:%23R$ohXceKHA.5136@TK2MSFTNGP02.phx.gbl... > I'm not...

How to Restrict Users to view/go to Query Design/Sql mode in MDE/M
I want to secure my Access MDE file so that My users can not go to Design / SQL view of the query. Currently after opening query through command button provided by me on form , any user can press right click button & Go to design/Sql view of the query & fiddle with it. I want to keep enable right click mouse button but disable "Query Design" option. Like for forms & reports design option is not available after creation of MDE file. I am not in a position to use Security & group wizard provided by Access. Please Guide. Thanks . Girish Disabled the default short...

Nested query with combined key
Hi I am working on an existing database that has three tables, no option to change the database structure, but have to work on it. CREATE TABLE Building ( b_id INT PRIMARY KEY, b_name char(50)); CREATE TABLE Door( d_id INT PRIMARY KEY, d_name char(30), d_key char(10), d_BuildingID int references Building(b_id)); CREATE TABLE History ( h_key char(40) PRIMARY KEY, transaction_time DATETIME PRIMARY KEY); The h_hey in the History is actually d_id + "-" + d_key, now I need to join three tables, get the building, door and associated last transacti...

Combine data from 2 queries
I have 2 tables of data that have sku #, size and color. Table 1 is a reference with all items. Table 2 has on hand data. I am looking to create a query to pull into table 2 additional information from table 1. I have attempted to create a query for each table and add in a concatenation of sku, size and color so that I can join the tables and extract information to create a table and/or report. When I attempt to create 1 query from both of these queries I'm getting error messages...help - is there an easier way to build this (I will need to refresh my tables on a reg...

query-based distribution group question
I need to build a list based on multiple departments. This doesn't seem possible with the creation wizard. How do I go about doing this. For example Alpha division list based on department equal to aa or ab or ac TIA If you're using department as the attribute to filter on, the ldap filter for the dynamic distribution list would look something like: (&(objectClass=user)(mailnickname=*)(|(department=aa)(department=bb))) -- Bharat Suneja MVP - Exchange www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "Fuzzy Logic" <bob@arc.ab....