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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,676
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,102,689
Messages
5,488,273
Members
407,633
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top