Improve company productivity with a Business Account.Sign Up

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

Update query based on number of digits

I would like to make an update query so that it searches for records in the "SIC Code" field that contain 7 numbers and updates the "Update SIC Name" field with the contents of "SIC Name" field.  How is this done?  Thanks.
Sample.accdb
0
daisypetals313
Asked:
daisypetals313
  • 4
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Would this work:

DoCmd.RunSQL "UPDATE YourTable SET [Update SIC Name]=[SIC Name] WHERE Len([SIC Code]) = 7"

Note that does not check whether "SIC CODE" contains 7 NUMBERS, it just checks whether it contains 7 characters.
0
 
daisypetals313Author Commented:
I inserted:
DoCmd.RunSQL "UPDATE [Sample] SET [Update with SIC Name]=[SIC Name] WHERE Len([SIC Code]) = 7"

And received error message:
Invaled SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'
0
 
daisypetals313Author Commented:
This worked for me, but I'm not sure how it's different

UPDATE sample SET sample.[Update with SIC Name] = [sic name]
WHERE (((Len([sic code]))>7));
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
mbizupCommented:
Looks like you entered your SQL statement directly in the query builder.

Scott's query is exactly the same thing, valid SQL syntax embedded in VBA, which will run if you test it in a command button click event or similar.
0
 
daisypetals313Author Commented:
Thanks mbizup for the explanation.  I think the query builder is easier for me personally (still learning :) but good to know there are multiple ways.
0
 
daisypetals313Author Commented:
Thanks.
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

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.

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