Update Qry

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.
pdvsaProject financeAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
Doesn't this condition cover it?

  AND [tblLetterofCredit].[LCNo] = [tblExcelImport].[LCNo]
0
 
cyberkiwiCommented:
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
 
pdvsaProject financeAuthor Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
pdvsaProject financeAuthor Commented:
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
 
cyberkiwiCommented:
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
 
pdvsaProject financeAuthor Commented:
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
 
cyberkiwiCommented:
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
 
pdvsaProject financeAuthor Commented:
will try to get to this today....thx...  
0
 
pdvsaProject financeAuthor Commented:
cyberkiwi:  how could I modify it to update where LCNo = LCNo from both tables?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.