Results 1 to 10 of 10
  1. #1
    moorecurls is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    24

    Post String Right Function with Find using another field

    I have a database that has a "Code" field (which is short text) within the code is the two character country code, which can be at any length within the "Code" field. I have another field in the database with the "Country" (also short text) that would be found in the "Code" field. Within the "Code" field after each "Country" two digest is the invoice number. I need this separated into a new field I've called "Invoice" (also short text). I've tried many Right Function formulas but I keep getting just the ending numbers for the invoice, I need the full invoice number.


    FindCountry: InStr([Code],[Country]) This gives me the starting character for each country. I know that each country is two digest.
    Below is what I've tried so far:
    Invoice: Right([Code],Len(FindCountry])-InStr([Code],-))
    Invoice: IIf(InStr([Code],[Country]),Trim(Right([Code],InStr([Code],[Country])-1)),"")
    Ex Code: Invoice that I'm expecting:
    FRBCJM12345 12345
    FRBCFRBIAT71-5897 BIAT71-5897
    FRBCCO-NB-01090 -NB-01090
    I bolded the country within the code for emphasis. Please let me know how to get the full invoice number to the right of each country code. Thanks.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I take it the code isn't always the 5th and 6th characters? That would be easy. In your second example, you're looking for a country code of FR, but the code actually starts with FR so you'd have to account for that.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    If all codes start with FRBC:

    InvoiceDouble:Mid(Replace([CODE],"FRBC",""),3)

    Looks like the invoice is being duplicated, this would return one instance:
    Invoice: Left([InvoiceDouble],InStr([InvoiceDouble]," ")) - which is the equivalent of:
    Invoice: Left(Mid(Replace([CODE],"FRBC",""),3),InStr(Mid(Replace([CODE],"FRBC",""),3)," "))

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to Paul's comment, I think you have to do some manual checks since FR which is a country code occurs twice in
    example 2. So finding FR in a string is not sufficient to isolate Country code.
    ??Do all records start with FRBC??

    If so, then you could check

    CountryCd: Mid([Code],5,2)
    InvoiceCd: Mid([Code],7)


    Good luck with your project.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Gicu View Post
    If all codes start with FRBC:

    InvoiceDouble:Mid(Replace([CODE],"FRBC",""),3)
    What's the point of the Replace() function? If all codes start with FRBC and the country code is 2 digits all you need is the Mid() function. Your code will give nothing different than the Mid() function with a start character of 7.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Of course Paul, that is exactly what Orange posted, I was just trying to visually isolate the country code and the invoice(s).

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Yes, that's the easy answer I alluded to if the code isn't always the 5th and 6th characters. I don't believe there's duplication of the invoice, rather the field and the desired result:

    Ex Code: Invoice that I'm expecting:
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    This is what I mean Paul, Mid([Code],7) would return what seems a duplicate invoice number:

    FRBC
    JM 12345 12345
    FRBCFR BIAT71-5897 BIAT71-5897
    FRBCCO -NB-01090 -NB-01090
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The OP will have to clarify, but I interpreted the quoted bit to mean that what was shown was:

    FieldContents DesiredResult

    In other words, there is no duplication.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Now I see it, you're absolutely right, sorry for the confusion I might have caused!
    Need some stronger glasses....
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 6
    Last Post: 02-26-2020, 10:03 PM
  2. Replies: 3
    Last Post: 09-22-2016, 11:16 AM
  3. Replies: 6
    Last Post: 06-20-2016, 01:29 PM
  4. Function to find a number in string
    By LonghronJ in forum Modules
    Replies: 3
    Last Post: 09-28-2015, 02:15 PM
  5. Replies: 1
    Last Post: 11-30-2012, 05:57 AM

Tags for this Thread

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