Solved

Update Qry

Posted on 2010-11-10
9
365 Views
Last Modified: 2012-06-27
Experts,

I have a tblLetterOfCredit and it has some blank fields.
I need to update those blank fields based on another table I imported from Excel [tblExcelImport].
I can match between the two table on LCNo.

I think there are two ways to do this:  canned update qry in MSAccess or by code

~~~~~~~~~~~
Maybe it is easier to see In code (it would be something like this):
If [tblLetterofCredit][LCNo] matches [tblExcelImport][LCNo] then update APPLICANT on tblLetterOfCredit from [Applicant] in the excel file BUT ONLY IF LCNO UNDER TBLLETTEROFCREDIT IS BLANK.

I hope my explanation is clear enough and please ask if you have questions.
0
Comment
Question by:pdvsa
  • 5
  • 4
9 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34106207
update [tblLetterofCredit],[tblExcelImport]
set [tblLetterofCredit].APPLICANT = [tblExcelImport].APPLICANT
where nz([tblLetterofCredit].APPLICANT,"") = ""
  AND [tblLetterofCredit].[LCNo] = [tblExcelImport].[LCNo]

I think you mean

BUT ONLY IF __APPLICANT__ UNDER TBLLETTEROFCREDIT IS BLANK.
0
 

Author Comment

by:pdvsa
ID: 34106386
OH YES...that is right I did mean Applicant.  Thanks for that.  Let me test it.  I did not include all and so I need to use what you said and modify it a little.  
0
 

Author Comment

by:pdvsa
ID: 34113749
I get an error of "type conversion failure.  I wonder if this could be becuase the field I am trying to update is a combobox ([tblLetterofCredit].APPLICANT).  I remember now that I tried doing this before and ran into the same issue.  

What do you think?
untitled.JPG
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34114500
Is Applicant the only field that is being updated?
Please list the fields and types being updated to/from on both tables.
I think you need a conversion somewhere.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:pdvsa
ID: 34117737
Yes, Applicant is the only field being updated.  The property is Number with a row source qry in it on tblLetterOfCredit.  It is acombobox.  

On tblExcelImport, applicant is text.  

(doing this from memory). Pretty sure it is correct.  

Any ideas?   Thanks
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34118630
The source from tblExcelImport may have invalid number, or some fields that are blank strings.
You can try this

update [tblLetterofCredit],[tblExcelImport]
set [tblLetterofCredit].APPLICANT = [tblExcelImport].APPLICANT
where nz([tblLetterofCredit].APPLICANT,"") = ""
  AND [tblLetterofCredit].[LCNo] = [tblExcelImport].[LCNo]
  AND val("0" & [tblExcelImport].APPLICANT) <> 0

It will omit all the invalid tblExcelImport.Applicant values
0
 

Author Comment

by:pdvsa
ID: 34145449
will try to get to this today....thx...  
0
 

Author Comment

by:pdvsa
ID: 34151756
cyberkiwi:  how could I modify it to update where LCNo = LCNo from both tables?
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34151937
Doesn't this condition cover it?

  AND [tblLetterofCredit].[LCNo] = [tblExcelImport].[LCNo]
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now