Results 1 to 6 of 6
  1. #1
    Netopia is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    16

    Update Queries with Inconsistent Results

    I've got a situation where I need to parse data out of one field into others. For two updated fields that are empty (Field3 & Field4) it works great. For the third field (SubNo), I am getting unexpected results. If I remove the -1, then I get the data I would expect, but with a space at the end. Can't figure out what I'm doing wrong with the -1.



    As is usual, thank you in advance for any advice you can give.

    Joe

    Code:
    DB.Execute "UPDATE MailFile SET MailFile.Field3 = " _    
        & "Mid([SubNo_ORIG],InStr([SubNo_ORIG],'_')+1,2)" _
        & "WHERE (((MailFile.SubNo_ORIG) Like '*_*'));"
        
    DB.Execute "UPDATE MailFile SET MailFile.Field4 = " _
        & "Mid([SubNo_ORIG],InStr([SubNo_ORIG],'_')+3,6)" _
        & "WHERE (((MailFile.SubNo_ORIG) Like '*_*'));"
        
    DB.Execute "UPDATE MailFile SET MailFile.SubNo = " _
        & "Left([SubNo_ORIG],InStr([SubNo_ORIG],' ',-1))" _
        & "WHERE (((MailFile.SubNo_ORIG) Like '* *'));"

    Click image for larger version. 

Name:	2018-07-10 08_53_13-Access - CB.AICPA _ Database- C__Users_Joe_Desktop_CPD Stuff_Access_CB.AICPA.png 
Views:	8 
Size:	23.8 KB 
ID:	34691

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Maybe the trim function will lop off the space:

    DB.Execute "UPDATE MailFile SET MailFile.SubNo = " _
    & "Trim([SubNo_ORIG]) " _
    & "WHERE (((MailFile.SubNo_ORIG) Like '* *'));"

  3. #3
    Netopia is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    16
    Dave,

    Thanks for the response. I did add the trim function and get the results I want, but since I'm a newbie at this stuff, I also want to know what I did wrong so that I can do better in the future.

    Any idea why the weird return data?

    OH... and just to see, I replaced the -1 with 1 and with 0... all with the exact same results.

    Joe

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Here's a master's take on the subject:
    http://allenbrowne.com/bug-15.html

  5. #5
    Netopia is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    16
    WOW! That was an interesting read! I would never have suspected a bug in Access, but rather a bug with the way I was doing things. I guess as a neophyte to this stuff I always assume the problem is with my coding.

    Thank you much for the education.

    Joe

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You could also test that InStr([SubNo_ORIG],'_') is greater than 0 and skip or modify the update sql accordingly rather than looking for "* *".
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Inconsistent results with a simle query
    By slickdog in forum Queries
    Replies: 4
    Last Post: 10-07-2015, 06:01 AM
  2. Replies: 3
    Last Post: 07-30-2015, 03:59 AM
  3. Inconsistent results.. please help!
    By vikghai in forum Access
    Replies: 6
    Last Post: 02-09-2014, 05:37 PM
  4. Replies: 1
    Last Post: 09-01-2012, 06:58 AM
  5. Different results in queries
    By Vic in forum Programming
    Replies: 6
    Last Post: 11-06-2009, 10:58 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