Results 1 to 8 of 8
  1. #1
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108

    how to format a string on insert command

    Hi, I have this simple insert sql statement



    Code:
    DoCmd.RunSQL "INSERT INTO tbl_Requisition_Exclusions (Item_ID, Item_ID_dashes) VALUES ('" & txtExcludeItem & "', '" & txtExcludeItem & "')"
    I can't figure out how to insert the second field with dashes.

    The value would be entered as 12345678911 in the text box and I need to save it as is in Item_ID and as 12-3456-7891-1 in the second field Item_ID_dashes.

    One way is to first enter it as is then update the table but I'd rather insert it the right way of possible as it should require less computing.

    Thanks.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    One way might be something like
    Left(txtExcludeItem,2) & "-" & Mid(txtExcludeItem,3,4) & "-" & Mid(txtExcludeItem,7,4) & Mid(txtExcludeItem,11)

    However, I have left off the contatenation of quote delimiters just to show the gist of an expression, because I would use string variables rather than a field or control reference. If you did so, you should not need them as vba side of sql building should pass the reference as a string if memory serves. These days it may not be serving so well. I would have to test to be sure.

    Also, the sample expression assumes that the field values will always be as you show, with the possible exception that it won't matter how many characters will come after the last dash as they would all be returned as it is written.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    I'll see if I can change to a string variable. Yes the string would always be of 11 numerical digits, I added validations for that already.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I did a test. Memory seems to be OK on this point today! Works, but I forgot the last appended dash.

    Debug.Print Left(strIn, 2) & "-" & Mid(strIn, 3, 4) & "-" & Mid(strIn, 7, 4) & "-" & Mid(strIn, 11)
    Output: 12-3456-7891-1

  5. #5
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    ok that works, thank you!

    Code:
    DoCmd.RunSQL "INSERT INTO tbl_Requisition_Exclusions (Item_ID, Item_ID_dashes) VALUES ('" & idVar & "', '" & Left(idVar, 2) & "-" & Mid(idVar, 3, 4) & "-" & Mid(idVar, 7, 4) & "-" & Mid(idVar, 11) & "')"

  6. #6
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Excuse me if I miss something, but, why not Format("12345678911","00-0000-0000-0") ?

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by accesstos View Post
    Excuse me if I miss something, but, why not Format("12345678911","00-0000-0000-0") ?
    Obviously that was too simple for me!
    Perhaps I was too focused on the possibility of more than 11 characters, but that was answered after my first post.
    Also, the sample expression assumes that the field values will always be as you show, with the possible exception that it won't matter how many characters will come after the last dash as they would all be returned as it is written.
    Thanks for the simpler idea. I suppose some might argue that the field value should be stored without the dashes and formatted that way in the form. That would probably be my approach.

  8. #8
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by Micron View Post
    Thanks for the simpler idea.
    You're welcome!
    Sometimes, all of us, we are looking for the elaborate solution and we lose the simple.
    Quote Originally Posted by Micron View Post
    I suppose some might argue that the field value should be stored without the dashes and formatted that way in the form. That would probably be my approach.
    One of them is me. No need one more field for an already stored value.

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

Similar Threads

  1. Insert (-) in a string
    By DaveT99 in forum Queries
    Replies: 2
    Last Post: 05-06-2018, 08:48 AM
  2. Replies: 2
    Last Post: 02-28-2017, 01:27 PM
  3. Error on INSERT INTO string built with VBA
    By sstiebinger in forum Queries
    Replies: 4
    Last Post: 11-30-2015, 03:07 PM
  4. Replies: 2
    Last Post: 04-05-2015, 06:06 PM
  5. Format within a string
    By wdrspens in forum Reports
    Replies: 1
    Last Post: 04-16-2011, 05:51 PM

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