asked on

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.

cyberkiwi

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.

pdvsa

ASKER

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.

pdvsa

ASKER

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

cyberkiwi

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.

pdvsa

ASKER

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

cyberkiwi

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

pdvsa

ASKER

will try to get to this today....thx...

pdvsa

ASKER

cyberkiwi: how could I modify it to update where LCNo = LCNo from both tables?

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial