remove characters from field in query expression (1 Viewer)

Cowboy_BeBa

Registered User.
Local time
Today, 23:50
Joined
Nov 30, 2010
Messages
188
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

Ignore List Poster Boy
Local time
Today, 11:50
Joined
Apr 27, 2015
Messages
6,286
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!
 
Last edited:

Cowboy_BeBa

Registered User.
Local time
Today, 23:50
Joined
Nov 30, 2010
Messages
188
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

bit cruncher
Local time
Today, 08:50
Joined
Mar 17, 2004
Messages
8,178
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...
Code:
oldID: Mid(stoOldDBRef, 3)
...which is somewhat simpler.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 11:50
Joined
Apr 27, 2015
Messages
6,286
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...
Code:
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

bit cruncher
Local time
Today, 08:50
Joined
Mar 17, 2004
Messages
8,178
Nope, the third param of the Mid() function is optional. Congrats on your extended Italy gig by the way! :)
 

Users who are viewing this thread

Top Bottom