Access lookup on partial string
Posted on 2013-10-30
Using Access '07...long time since I've been in Access...I need to do the equivalent of the following from Excel:
use the left 4 characters in a field to go to a table that has 5 versions of those 4 characters, and bring back into the table from which I'm looking, the value in the second field of the lookup table.
Basically, I have a table with a long list of account information (chart of accounts from accounting department). The start of each account is "10-x" (with 'x' being anything from 4 to 8). Depending on the 'x', it is either a 'revenue'/'expense'/etc. account type. I am going to be taking the results of this out of Access and dumping to Excel, for pivoting. I need that 'account type' designation to come across in the results. In Excel, I'd simply do a vlookup(left(CELL,4),SHORT_TABLE_WITH_LOOKUP_VALUES,2,false), and I'd have my answer. Don't know how to replicate that in Access, and can't seem to find the right online searches for answers.