Results 1 to 5 of 5

String within a string

  1. #1
    Nobby2193 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2012
    Posts
    9

    String within a string

    Hi All
    I have this great line of code in excel that helps with my banking


    =IFNA(INDEX(codes!$B$3:$B$1901,MATCH(1,INDEX(ISNUM BER(SEARCH(codes!$A$3:$A$1901,E2))*1,0,0),0)),"")


    Initially I downloaded my bank statement and looked at a common string in the item bought
    IE CARD PAYMENT TO TESCO STORE 2860,20.00 GBP, RATE 1.00/GBP
    the common string would be CARD PAYMENT TO TESCO STORE


    I store this in my codes worksheet and add a code number to it ( so if the CARD PAYMENT TO TESCO STORE comes up again it is given the same code number
    I also add a category Number so I know that this item is for the food category




    I have to then transfer this to access to have a meaningful conversation about my wife's spending habits


    Is there a way of
    Having a table with Codes IE
    Description Code Category
    CARD PAYMENT TO SHELL 98 43
    CARD PAYMENT TO TESCO STORE 111 23


    and having a table with accounts IE
    01/01/2018 CARD PAYMENT TO TESCO STORE 2860,20.00 GBP, RATE 1.00/GBP ON 29-12-2017 20.00
    02/01/2018 CARD PAYMENT TO TESCO STORES 3078,6.55 GBP, RATE 1.00/GBP ON 30-12-2017 6.55
    02/01/2018 CARD PAYMENT TO WILKO RETAIL LIMIT,5.00 GBP, RATE 1.00/GBP ON 30-12-2017 5.00


    And then doing something like


    lookup up CARD PAYMENT TO TESCO STORE in tblcodes and compare it to every record in Tblaccounts
    If CARD PAYMENT TO TESCO STORE in tblcodes matches CARD PAYMENT TO TESCO STORE 2860,20.00 GBP, RATE 1.00/GBP ON 29-12-2017 then add the code number and category no


    If CARD PAYMENT TO TESCO STORE cant be found in the tblaccounts then add a zero for further investigation


    I attach a spreadsheet to give an indication of how excel does it


    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,138
    If your goal is to eliminate Excel and convert to Access database, good decision.

    A query can return records from Accounts that do not have match in Codes.

    SELECT Accounts.Description, codes.Code
    FROM codes RIGHT JOIN Accounts ON codes.Code = Accounts.Codes
    WHERE (((codes.Code) Is Null));

    An UPDATE query can update records in Accounts where a Description can be matched:

    UPDATE [Accounts], Codes SET Codes=Code WHERE Codes Is Null and InStr([Accounts].Description, [Codes].Description)<>0;


    Advise not to give tables and fields exact same names.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  3. #3
    Nobby2193 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2012
    Posts
    9
    Hi June 7 and thanks so much for the prompt reply

    Its only when I look at replies to questions in the forum that I realise how poor I am at vba

    I need to ask if you would spell this out a bit for me as I keep getting join or syntax errors that I know is me and not you

    I have changed the tables to avoid confusion and list below the fields

    TblAccounts
    AccountsID Autonumber Primary key
    Accountcode number
    dated date/time
    description short text
    money in currency
    money out currency

    TblCodes
    codeid autonumber Primary key
    codeDescription short text
    codeNo number



    Many Thanks

    Rob

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,138
    Post your attempted query SQL statement.

    Suggest no spaces in names. If you do, must enclose in [ ], like [money in] and [money out].

    Instead of "money in" and "money out", have a single field for money: TransactionAmount, then another field: TransactionType.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  5. #5
    Nobby2193 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2012
    Posts
    9

    string in string

    Quote Originally Posted by June7 View Post
    Post your attempted query SQL statement.

    Suggest no spaces in names. If you do, must enclose in [ ], like [money in] and [money out].

    Instead of "money in" and "money out", have a single field for money: TransactionAmount, then another field: TransactionType.
    Hi

    I've tried query you sent and cant get it to work

    I realise I am a pain for asking but there have been no other replies I can work with
    Attached Thumbnails Attached Thumbnails Capture1.JPG  

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

Similar Threads

  1. Replies: 5
    Last Post: 06-15-2018, 02:14 PM
  2. Replies: 5
    Last Post: 02-20-2018, 06:25 PM
  3. RegExp to Find String at END of a String
    By Micron in forum Programming
    Replies: 3
    Last Post: 01-17-2018, 08:43 PM
  4. Replies: 6
    Last Post: 06-20-2016, 12:29 PM
  5. Replies: 2
    Last Post: 04-05-2015, 05:06 PM

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
  •  
Tech Forums: Microsoft Office Forums