Results 1 to 2 of 2
  1. #1
    JohnPapa is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Sep 2011
    Posts
    1

    SQL statement

    I am trying to update field tblTo.strAdd based on the values of
    tblFrom.str1 and tblTo.str1, with a single SQL statement


    Assume tblFrom.str1 has only one record where string field
    tblFrom.str1 = 456


    tblTo.str1 has string fields 456234, 456789, 4522, 458, 456 with
    tblTo.strAdd being blank in every case.


    I would like to use a single SQL statement to update tblTo.strAdd with
    the value 456 for all tblTo.str1 that BEGIN WITH 456, in this case we
    will update the 3 records where tblTo.str1 is 456234, 456789, 456


    I tried using something like the following in the WHERE of an UPDATE
    but with no success


    (tblTo.str1 LIKE ' " & strValue & "%' )
    where strValue = "456"


    The above is ADO. For DAO I would use * instead of %


    Any ideas?

  2. #2
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I ran these two SQL statements, the select query to see if the correct records were selected, then the update to do the update. Of course, you would have to change them a little to run in VBA.


    Code:
    SELECT tblTo.str1 FROM tblTo WHERE Left(tblTo.str1,3)= 456
    
    UPDATE tblTo SET tblTo.strAdd = "456" WHERE Left(tblTo.str1,3)= "456"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 08-17-2011, 01:49 PM
  2. BETWEEN in a FROM Statement
    By Cyborg in forum Queries
    Replies: 6
    Last Post: 05-12-2011, 01:54 PM
  3. If Statement
    By ajolson1964 in forum Access
    Replies: 1
    Last Post: 05-11-2011, 07:51 AM
  4. Help with IF statement
    By mkallover in forum Programming
    Replies: 7
    Last Post: 01-04-2011, 04:47 PM
  5. If Then Statement Help
    By Kapelluschsa in forum Programming
    Replies: 5
    Last Post: 08-11-2010, 09:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums