Results 1 to 7 of 7
  1. #1
    Nobby2193 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2012
    Posts
    12

    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 online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Nobby2193 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2012
    Posts
    12
    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 online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

    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  

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Your query is not what I suggested. Table row should be Accounts. Manually change or can Copy/Paste suggested sql statement into SQL View of query builder then switch to Design View.

    BTW, correct spelling: category
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Nobby2193 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2012
    Posts
    12

    string in string

    Quote Originally Posted by June7 View Post
    Your query is not what I suggested. Table row should be Accounts. Manually change or can Copy/Paste suggested sql statement into SQL View of query builder then switch to Design View.

    BTW, correct spelling: category
    Hi and thanks again for your help

    Its running great now

    rob

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

Similar Threads

  1. Replies: 5
    Last Post: 06-15-2018, 03:14 PM
  2. Replies: 5
    Last Post: 02-20-2018, 07:25 PM
  3. RegExp to Find String at END of a String
    By Micron in forum Programming
    Replies: 3
    Last Post: 01-17-2018, 09:43 PM
  4. Replies: 6
    Last Post: 06-20-2016, 01:29 PM
  5. Replies: 2
    Last Post: 04-05-2015, 06: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
  •  
Other Forums: Microsoft Office Forums