Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 481
  • Last Modified:

Update Query - leading Zeros or dashes

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
pdvsa
Asked:
pdvsa
  • 8
  • 5
  • 4
1 Solution
 
mbizupCommented:
Try changing your criteria

Where cstr([l/c no]) = cstr(lcno)
0
 
mbizupCommented:
L/c no should be l/c ref in my previous comment
0
 
Rey Obrero (Capricorn1)Commented:
are the LCNo does not have leading zeros?

try this

WHERE Replace([Import-DnB].[L/C Ref],"-","")="00" & [LCNo];
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
pdvsaProject financeAuthor Commented:
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
 
pdvsaProject financeAuthor Commented:
The LCNo's do have to remain intact meaning I can not modify the LCNo.
0
 
pdvsaProject financeAuthor Commented:
or LCRef...I have to import the data as is.
0
 
Rey Obrero (Capricorn1)Commented:
why don't you try the where clause i posted?
0
 
pdvsaProject financeAuthor Commented:
Capricorn, i did try it.  Maybe you missed my response.
0
 
Rey Obrero (Capricorn1)Commented:
so, what is the result?
0
 
mbizupCommented:
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
 
pdvsaProject financeAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
<, 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
 
pdvsaProject financeAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
< They do have leading 0'>

ok, then try this

WHERE Replace([Import-DnB].[L/C Ref],"-","")= [LCNo];
0
 
pdvsaProject financeAuthor Commented:
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
 
mbizupCommented:
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
 
pdvsaProject financeAuthor Commented:
ahh I see.  Thanks for the tip.
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.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now