• 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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