remove characters from field in query expression

[Cowboy_BeBa]

Hi

So ive rebuilt my work database
the original db had 3 tables for keeping track of Stock (tblFinishedGoods, tblProducts, tblIngredients), the new one just has one table for all types (tblStock). This new design simplified the crap out of all the queries i used to calculate stock costs/levels as well as added greater flexibility

I originally wrote up a few paragraphs explaining the different tables and their functions and why i made the decision to merge 3 tables into one (actually turned 6 tables into 2, but thats a long story)
but end of the day all that is irrelevant and i didnt overlaod you guys (or waste your time on) unnecessary info

basically i have a field (stoOldDBRef) that contains all my old ID's and the tables their from
these records have 2 letters followed by a number
I need to write an expression in a query that essentially strips the 2 letters and just leaves me with the number (the number varies between 1 and 3 characters), not quite sure how to do this, wondering if anyone can help me out here

Just wondering

 

[NauticalGent]

If the first characters are ALWAYS the two letters you want to replace, use this in your query grid:

YourField:Replace(Left([stoOldDBRef],2),"")

Again, this is based on the ASS-umption that your numbers are ALWAYS the first two characters. If not, this is a move convoluted way to do it by calling a function in your query - but we will save that for later if needed!

Try it, let me know how it works!

 

[Cowboy_BeBa]

Thanks NauticalGent

i did get an error saying my expression had the wrong number of arguments but it was an easy fix, the replace function was missing an argument so i just changed it to
oldID: Replace([stoOldDBRef],Left([stoOldDBRef],2),"")

Works perfectly, thanks for your help

 

[MarkK]

But that has the effect of just dropping the first two characters, whatever they are, right? And if that is your objective, you can do that more simply with just the Mid() function...

oldID: Mid(stoOldDBRef, 3)

...which is somewhat simpler.

 

[NauticalGent]

But that has the effect of just dropping the first two characters, whatever they are, right? And if that is your objective, you can do that more simply with just the Mid() function...

oldID: Mid(stoOldDBRef, 3)

...which is somewhat simpler.

Thanks MarkK, for some reason I had it in my head the Mid function required two arguments which meant the length of the string had to be known. Learn something new everyday...

 

[MarkK]

Nope, the third param of the Mid() function is optional. Congrats on your extended Italy gig by the way!