Solved

Update Query - leading Zeros or dashes

Posted on 2013-01-20
17
468 Views
Last Modified: 2013-01-20
Experts,

I have been updating with an update query with no issues until today.
I noticed the query will not update if there are either dashes or leading zeros in the data I am importing.

I import data from excel then update my db data according to the where condition.
The update query updates if ([Import-DnB].[L/C Ref])=[LCNo]

The problem record was as follows: (notice leading zeros and dashes)
[Import-DnB].[L/C Ref] = 00440-02-000-2585

The record would not update with that type of number
I thought there could be some issue with the leading zeros or dashes.
I changed [Import-DnB].[L/C Ref] to 1111 (removing the dashes and leading zeros) and my problem was solved but I have to be able to update with leading zeros or dashes.

I imagine some experts have encountered this before and know how to modify the below sql to allow the update even with dashes or leading zeros.
 
The fields are formatted as TEXT and not number as L/C Ref has letters sometimes.  

Here is the SQL of the update query:
UPDATE [Import-DnB], tblLetterOfCredit SET [Import-DnB].LCID_dnb = [tblLetterOfCredit].[LetterOfCreditID]
WHERE ((([Import-DnB].[L/C Ref])=[LCNo]));

thank you for the help...

UpdateQry
0
Comment
Question by:pdvsa
  • 8
  • 5
  • 4
17 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38799007
Try changing your criteria

Where cstr([l/c no]) = cstr(lcno)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38799010
L/c no should be l/c ref in my previous comment
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38799013
are the LCNo does not have leading zeros?

try this

WHERE Replace([Import-DnB].[L/C Ref],"-","")="00" & [LCNo];
0
 

Author Comment

by:pdvsa
ID: 38799055
mbizup:
I get an error with placing the cstr:
UPDATE [Import-DnB], tblLetterOfCredit SET [Import-DnB].LCID_dnb = [tblLetterOfCredit].[LetterOfCreditID]
WHERE ((cstr([Import-DnB].[L/C Ref])=cstr[LCNo]));

Capricorn:
I changed the where condition but after I did it did not return any results.  

thanks....
0
 

Author Comment

by:pdvsa
ID: 38799058
The LCNo's do have to remain intact meaning I can not modify the LCNo.
0
 

Author Comment

by:pdvsa
ID: 38799060
or LCRef...I have to import the data as is.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38799073
why don't you try the where clause i posted?
0
 

Author Comment

by:pdvsa
ID: 38799090
Capricorn, i did try it.  Maybe you missed my response.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38799100
so, what is the result?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38799210
Using a cellphone so I can't easily copy paste your code, but your parentheses are mismatched the way you applied the second cstr.  It should end with

= cstr([lcno]));
0
 

Author Comment

by:pdvsa
ID: 38799220
Capricorn, i think your method is altering the LCNo if not mistaken.  I have to keep the integrity if the text.  I changed the where condition to what you said but when ran there were no results (pressed the datasheet to see if any results).  If i ran, i dont think it would have updated.

I am on cell.  Can not test.  Will do so soon.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38799254
<, i think your method is altering the LCNo if not mistaken>
<I have to keep the integrity if the text.>

the  LCNo  will not change

answer this question

are the LCNo does not have leading zeros?

or post sample values of [ LCNo ]
0
 

Author Comment

by:pdvsa
ID: 38799272
Oh ok i hought it was Replacing... Sorry.  They do have leading 0's.  The format is text if that makes a difference.  Will be at computer in a couPle hours.
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 38799329
< They do have leading 0'>

ok, then try this

WHERE Replace([Import-DnB].[L/C Ref],"-","")= [LCNo];
0
 

Author Comment

by:pdvsa
ID: 38799533
Capricorn:  that worked.  ...thank you.

Mbizup:  
I tried to add the paren but still had a syntax.  I added another paren at the very end (as it highlighted the very end) but said "invalid use of Null" when ran:
UPDATE [Import-DnB], tblLetterOfCredit SET [Import-DnB].LCID_dnb = [tblLetterOfCredit].[LetterOfCreditID]
WHERE ((cstr([Import-DnB].[L/C Ref])=cstr([LCNo])));
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38799554
Your syntax still looks off.

Just a couple of general tips with these sql statements...

Access throws in a lot of unneeded parentheses that tend to confuse/complicate things.  These SQL statements are generally easier to understand and to write correctly if you keep the parentheses to a minimum (either by custom coding or by removing the extra parentheses that Access adds).  That's why I wrote my original post the way I did (you don't need all of the parentheses and brackets that you have in your SQL statements):

Where cstr([l/c ref]) = cstr(lcno)

That said - my original suggestion was a guess based on a misunderstanding of the question.  I didn't realize that you were actually trying to match text in one field with different text in another field - so this suggestion, even if the syntax is corrected is not going to work.
0
 

Author Comment

by:pdvsa
ID: 38799594
ahh I see.  Thanks for the tip.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

932 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