Parse numeric substring, generate accounting key

SomeLinuxGuy

New Member
Joined
Jun 30, 2020
Messages
1
Hello,
I'm a full time software developer, helping out a charity with their Access (.accdb) database in my spare time. There I need to work with an existing database that connects basic entities from one table with actions to perform on them in another table; everything is properly linked (1 entity -> n actions) and works as expected.
Now I have to add a code following a specific structure extracted from existing columns to the actions to make bookkeeping happy, and I encountered some basically simple issues I have problems solving with Access. I do not own a copy of Access (or Windows, for that matter), so I can't play around too much exploring the Access "API". Any help is appreciated, especially if it uses as little VBA as possible (where do I put that in the first place? in addition, commands are localised so VBA-solutions are of limited use on my regional system anyways); I'm fine with extensive SQL, though.

The rows used to calculate the key will contain, among others:

Code:
{
  internal_region_id: Integer,
  human_readable_entity_id: String.matching(/\w{2}\d+/),
  date: Date
}
The key will be a string value, constructed as follows: `<constant prefix><string from internal_region_id><calculated key string from human_readable_entity_id><last two digits of year from date>`.
  1. Will simply using the concat symbol & be enough to concatenate a string with a numeric value or will I need to to string conversion first?
  2. The calculated key value will be a starting number plus a number that must be parsed from the substring of another column's value. The column in question has a two-letter prefix followed by the numeric part without separators. Basically I need to calculate something like `100 + getTheNumericPartOf("ab123")` where the number of digits may vary. Reading through the documentation of string functions I could not find anything that would remove n chars from the beginning of a string or match a regex, so what would be the simplest way of getting that numeric part?
  3. Getting the last two digits of the year (date value) should be easy by reading up a little on the string format functions, right?
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
Windows
Hi, in MSAccess the MID function would get the last digits of a string skipping the first two:
Code:
SELECT Mid(MyField,3) FROM MyTable
Last two digits of the year could in fact be the same (Right() would also work):
Code:
SELECT mid(year(Date()),3) FROM MyTable
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,550
Messages
5,512,010
Members
408,872
Latest member
Lorid24

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top