Solved

Update Qry

Posted on 2010-11-10
9
353 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

744 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

11 Experts available now in Live!

Get 1:1 Help Now