Append query with "if" on possible missing field.

I want to do an append query from table-1 to table-2 but the Table-1 may be 
missing a field [Rep] if so I want to pick up the [Rep] from table-3

Im appending to a field [Rep] in table-1

So far Ive tried building an update query with table-1 & table-3 at the top 
appending to Table-2
then in the boxes at the bottom - tried

Exp: iif(nz(isnull([table-1]![Rep])),[table3]![Rep],[Table1]![Rep])

but all this does when there is no [Rep] field in table-1 is prompt of the 
variable input..

Anythoughts would be much appreciated.? 
0
Utf
12/20/2007 5:36:00 AM
access.queries 6343 articles. 1 followers. Follow

3 Replies
1328 Views

Similar Articles

[PageSpeed] 7

Hi Rob,
here is a 2 step way to do it.
Append the data from table 2 to table 1.
Now to fill in the blanks in table 1, append from table 3 to table 1 but 
only for those records where [rep] is null.
Others may be able to give you a 1 step process.

Jeanette Cunningham

"Rob - IT Data Services" <RobITDataServices@discussions.microsoft.com> wrote 
in message news:225E4D72-01DA-44D4-B2A0-60FA8F7B5073@microsoft.com...
>I want to do an append query from table-1 to table-2 but the Table-1 may be
> missing a field [Rep] if so I want to pick up the [Rep] from table-3
>
> Im appending to a field [Rep] in table-1
>
> So far Ive tried building an update query with table-1 & table-3 at the 
> top
> appending to Table-2
> then in the boxes at the bottom - tried
>
> Exp: iif(nz(isnull([table-1]![Rep])),[table3]![Rep],[Table1]![Rep])
>
> but all this does when there is no [Rep] field in table-1 is prompt of the
> variable input..
>
> Anythoughts would be much appreciated.? 


0
Jeanette
12/20/2007 6:17:38 AM
Oops, that second query should be an update query, not an append query.

Jeanette Cunningham


"Rob - IT Data Services" <RobITDataServices@discussions.microsoft.com> wrote 
in message news:225E4D72-01DA-44D4-B2A0-60FA8F7B5073@microsoft.com...
>I want to do an append query from table-1 to table-2 but the Table-1 may be
> missing a field [Rep] if so I want to pick up the [Rep] from table-3
>
> Im appending to a field [Rep] in table-1
>
> So far Ive tried building an update query with table-1 & table-3 at the 
> top
> appending to Table-2
> then in the boxes at the bottom - tried
>
> Exp: iif(nz(isnull([table-1]![Rep])),[table3]![Rep],[Table1]![Rep])
>
> but all this does when there is no [Rep] field in table-1 is prompt of the
> variable input..
>
> Anythoughts would be much appreciated.? 


0
Jeanette
12/20/2007 6:18:35 AM
I think the nz and isnull together as you are using them may be the problem:

Try
Exp: Nz([Table-1]![Rep], [Table3]![Rep])

-- 
Dave Hargis, Microsoft Access MVP


"Rob - IT Data Services" wrote:

> I want to do an append query from table-1 to table-2 but the Table-1 may be 
> missing a field [Rep] if so I want to pick up the [Rep] from table-3
> 
> Im appending to a field [Rep] in table-1
> 
> So far Ive tried building an update query with table-1 & table-3 at the top 
> appending to Table-2
> then in the boxes at the bottom - tried
> 
> Exp: iif(nz(isnull([table-1]![Rep])),[table3]![Rep],[Table1]![Rep])
> 
> but all this does when there is no [Rep] field in table-1 is prompt of the 
> variable input..
> 
> Anythoughts would be much appreciated.? 
0
Utf
12/20/2007 3:07:02 PM
Reply:

Similar Artilces:

Possibility to quick modify protected cells in shared documents
I am sharing a workbook with my staff. I am looking for a possibility to change a cell content from specific cell while the other users cannot. (I know you can lock the cells and un-protect, but that's only possible if I disable sharing) The best would be if a password request pop-up appears after I focus on the cell. What is the VBcode if I need it? Bart 2003 Your code won't be able to unprotect a worksheet either--the same rules for sharing applies to it. But you can use: Tools|Protection|Allow users to edit ranges and give that range a password that only you know. You'll ha...

passing value from a form with IIF statement in a query
I have a form with a combo box that returns either a 1 or 2. If 1 is selected , the query would select the records that corresponds to the person with an ID of 1. If the value is 2, I want to select everyone who is NOT a 1. Using that value from the combo, I want to run a query that incorporates the following IIF statement: IIf([forms]![dlgRxRptByMonth].[repDoc]=1,1,>1) If I select the 1 in the combo box it produces the correct data. IF I select the value 2, the query runs without an error BUT produces no data. (I actually tried to insert the >1 as the value in th...

Check if field is empty (what code to use)
In a form's BeforeUpdate I check if certain fields are empty, and if that's the case I cancel the BeforeUpdate. I always use this kind of code for that: If IsNull([Surname] or Surname = "" then However, I noticed that others use other ways for checking this: If Len(Nz([Surname],"")) = 0 Then If Len(Trim([Surname] & "")) = 0 then If RTrim([Surname].Text) = Nothing Then If Nz([SurName],"") = "" Then I'm wondering what I can use best in this case. Also I'm wondering, why do you have to check for a ""...

How do I identify a blank field?
Hi I am trying to stop a user adding data into a subform before it's parent record has been created, so I am testing the key field of the parent to see if there is one: If Forms![Order Form]![Order Details].Form!PK_Order = "" Then MsgBox "You need to start by clicking the New Order / Enquiry button or the Quotation button." Else MsgBox "Order number '" & (Forms![Order Form]![Order Details].Form! PK_Order) & "'" Now the display following quite clearly shows me, the value of PK_Order is '' (that's the quotes on ei...

Modify Address Book/GAL Fields/Tabs
I have an Exchange 2003 environment (SP2 is applied). We use Cisco VoIP for voice communications, and have added everyone's extensions to the ipPhone field in ADUC. By default, the address book does not show the ipPhone field. Is there a way to map the ipPhone attribute to a field which does show in the address book (perhaps Business 2) or to add a tab to the address book with ipPhone information? Thanks in advance, David J. Baranski Network Administrator Electrical Equipment Company ...

Form Field won't update two different tables
I have a from field that I added to a form. All other fields in this form are linked to Table 1 (the form was created to fill Table 1) but this new field needs to update Table 2. I have the field linked correctly as I can see but it does not appear to be working any ideas as to why? First, it is not a field, it is a control. Only tables and queries have fields. Forms and Reports have controls and controls can be bound to fields in the form's recordset. This is the problem you are having. The control you are using cannot be bound to a field not in the form's recordset. It ...

Subject and Conversation fields different
I'm curious to know how the Conversation field is filled in outlook. The reason I ask is that not always, but some times, the subject line does not match the conversation field in new emails that I receive. Any explanation? ...

Show field if there is data in another
Hi I wonder whether someone can help me a with a problem I have. I have a report which contains tracked data changes. What I would like to do is where for example the 'DateOfInvoiceChanged' field is populated with a date, I would like the 'DateOfInvoice' i.e. the associated field to be visible, but, I'm not quite sure where to start to be honest. Could someone perhaps point me in the right direction please? Many thanks Chris -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201001/1 hobbit2612 via Ac...

How to know if a sale its enabled or desabled... (what table field #2
Hi, i have some useful RMS views at SQL Server level, the thing is that i cant find the field with specify if enabled or disabled a sale, i use SalePrice, StartSaleDate, and EndSaleDate, but no the enabled or disabled sale... which one is this? thanks in advance... Aldo ...

vbs to read 3rd column field data & check if exist in other file
There are 2 text files: (1) Text file with 4 columns, each fields are separated by tab-delimited. Each line represent one record. (2) Text file with 1 column only, it is a list of the data for look-up. I wish to read the data in third column (field) in the first text file, and then check against the second text file. If the data can be found in the second text file, then read next line in first text file. If the data cannot be found in the second text file, then this field content will be replaced with a fixed text (hardcoded). Please advice and many thanks in advanc...

Publisher2003 Certificate Template "Missing Clip Art" Error
Installed, removed, and re-installed Office 2003 Professional (Enterprise Edition) in Windows XP Pro (new & fresh build). Applied all Windows & Office updates. When picking Certificate "Appreciation 3" (in Print Publication view, Publication Designs\Apply a Design) this error is displayed: "The wizard cannot find clip art needed for this design. Your publication may not look as it was intended to look. Run setup again to reinstall the missing clip art file. For more information on running Setup press F1 now." We tried reinstalling, and removing/installing to ...

How do you insert a movie inot a Publisher web page, if possible?
I am putting together a disc for the local wrestling team and am using publisher to create a "web page" type of thing and am going to copy it to a disc for the members of the team. I am trying to insert a highlight movie of the wrestlers but can't make it work. Am I trying to do something that can't be done? If it can be done how do I do it? refer to http://msmvps.com/blogs/dbartosik/articles/80561.aspx and http://msmvps.com/blogs/dbartosik/articles/80805.aspx -- David Bartosik - [MSFT MVP] http://www.davidbartosik.com "Steve" wrote: > I am putting to...

Opening a form to a specific Record based on a field in the form
Hi all, I need some help with some coding. I have a database that containes about 25,000 records. Right now I have a form that opens to shows the records, some fields already have information contained in it. Other fields the users will have to update the form/table with additional information. The users have to take "ownership" of the Record by providing their ID, Name(this populates based on their ID number using Dlookup), and ownership date. When they open the form I want it to open to the first record that currently does not have an owner. Currently the form opens to the first r...

IFRAMES offline....is that possible?
Hi, We have probably all seen the IFRAME solution in which CRM's internal webpages are shown in an IFRAME, e.g seeing assoicated activities on the contact general tab... However,when the user goes offline, the local cassini server takes over. Does the internal webpage IFRAM solution still work? Does this work offline? Thanks M Think it depends on what you're planning to display in the IFrame - typically this will be a link to some related web page so the question is can you reach that web page when off-line from CRM? If that's possible then you might be able to some jav...

Access to Excel: Identify attachment field in access database
Hi All, How do I query a database field of type attachment? For eg: I have a database field of type attachment and I want to run a query in excel that identifies columns with no attachments and some other criterias and populates it. here is the query I have so far: If rst.fields("7") Is Null and rst.fields("5") = "Chicago" Then Me.File.Value = "NO" End If Hope I made it clear Thanks in advance You have two choices. 1) Use a SQL statement that gets only the records that meet your requirements by filtering the Da...

Delete Field Data if Another Field Has Data
Hi, I wonder if someone may be able to help me please. I have a subform called sfrmJobs which contains amongst others two fields, one called 'Predicted Date', the other ,'Actual Date'. What I would like to be happen is, if a date is keyed into the 'Actual Date' box, any date keyed into the 'Predicted Date' text box is deleted and then locks the 'Predicted Date' field for that record, but, to be honest I haven't got a clue where to start, or indeed whether it's possible. Could someone possibly help me put with this problem please? ...

Change color on date field in form for re-certification
Hello. I have two different date fields on a form, but I would like them to change color depending on how close to the re-certification date it is whenever I open the form or open a report showing the same info. On each of the date fields, I would like to have them green for anything over a month away, yellow for anytime within a month, and red for anything expired. One date field [PTDate] needs re-certified every 6 months, and the other date field [WeaponDate] needs re-certified every 12 months. I've been struggling with this for several days now and haven't been able t...

Restore Database to new server with different name. Is it possible?
I have a situation where we need to go back to view old information from an exchange backup (server A) from 3 months ago. (Server A) is still online. Of course I know I am going to need to build exchange on another server but does this temp exchange server need to have the same name as (server A) for me to view the data? Monica: Yes, it's possible and commonly done. No, you don't need to have the same server name, or even the same domain name. Please refer to http://www.microsoft.com/exchange/ for articles on restoring Exchange. Regards, Martin "Monica White" <an...

NT4 migration, need simple answer if possible
currently have an NT4 domain with exchange 5.5. (1 pdc, 2 bdc's, 1 exchange server). we are moving to server 2003 and exchange 2003. im getting very conflicting reports about how best to go about this. one side says to bring a new machine online as an NT4 bdc, promote it to pdc, run server 2003 on top of it as an upgrade etc... the other side says bring a clean 2003 server online, then use the AD migration tool to move everything from the NT4 domain over to AD. and this isnt even taken into consideration how we're going to handle the exchange move! is there anyone that has 100% success...

Query Question regarding IIf's
In the query I'm trying to make, I have 7 fields. ContractNumber, CostCode, CostType, EstimatedCost, ActualCost, EstimatedHours, ActualHours. I have written the following IIf statement to put in this query. =IIf(IsNull([costcode]) Or IsNull([costtype]) Or IsNull([EstimatedCost])=0,0, IIf([costcode]="013210" and [costtype]="05320",[txtEstimatedCost]/[txtCraftLabEst]. The txtEstimatedCost and txtCraftLabEst are fields on my report that I want to use this IIf statement in. Also, this is only a very small portion of the actual IIf statement that needs...

Is it possible to compare size for exe file and lib on different OS
Hello! I have one system that runs Microsoft Windows Server 2003 R2 and one system that runs Microsoft Windows XP SP 2. I use Visual Studio 6.0 and MFC. The application contains of many cpp files and h files. The application build some lib files which is used when the exe files is being built. If I build these LIBs and exe files on Microsoft Windows XP SP 2 and note the size for the exe files and the lib. I then copy all the source file into the Microsoft Windows Server 2003 R2 and then build the lib files and the exe files. So note I now have exactly the same source files(cpp and h.) on...

Need a query to return a value if out of range
I have the table below. I need to create a query that will return a score based on a passed Age and a passed LimitValue. For example: If Male= True, Age = 33 and LimitValue = 70 I need to return a score of 4. Because Age <= AgeHighLimit and Male = True when the LimitValue >= HighLlimit (60) in the table the highest score should be returned. Likewise, if Male= True, Age = 33 but LimitValue = 24 I need to return a score of 1. Because for that age and gender when the LimitValue <= HighLlimit (25) in the table the lowest score should be returned. I can write a query that ...

Simplify Code-Delete Query
Towards the bottom, I am having to delete a query and then rebuild another to make this work. Looking for a simpler/cleaner method. Any help appreciated. Private Sub cmdOK_Click() On Error GoTo Err_cmdOK_Click Dim varItem As Variant Dim strWhere As String Dim strWhere1 As String Dim strWhere2 As String Dim lngLen As Long Dim strDelim As String Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim strSQL As String Dim strDoc As String Dim strDoc1 As String With Me!lstGroup For Each varItem In .ItemsSelected If No...

Difference between Status and Status Reason fields
Hi, Can someone please help me understand the difference between Status and Status Reason fields in CRM records, such as Accounts? They seem to always be Active or Inactive together and filtering in Advanced Find by one or the other returns the same count. I know one is supposed to be a system flag and the other for users or something like that. Any explanation is appreciated. Thank you. On Jul 2, 9:20=A0pm, msandid <msan...@gmail.com> wrote: > Hi, > > Can someone please help me understand the difference between Status > and Status Reason fields in CRM records, such as Ac...

Pivot table, IF function, calculated item versus calculated field
Trying to make my pivot tables more useful by customizing my own formulas. I would prefer to use the following formula within a pivot table but can't seem to get the result I want. I'll add it to the source data if I have to but suspect my lack of pivot table knowledge is the problem. Assume Source data is Description Title Period Amount apples Miss 12 1000 pears Miss 12 500 geoff Mr 12 6000 able Mr 36 600 Formula I would add a column Yearly to the source data =if(period=36,0,Amount) and then t...