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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,669
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,934
Messages
5,483,774
Members
407,410
Latest member
catherinejoy

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top