Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Update Qry

Posted on 2010-11-10
9
Medium Priority
?
423 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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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
 

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 2000 total points
ID: 34151937
Doesn't this condition cover it?

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

927 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