De-concatenate a query’s field (1 Viewer)

slharman1

Member
Local time
Today, 06:34
Joined
Mar 8, 2021
Messages
467
I have a field in a union query the has this format, I believe it is a string data type because the last two se room come from short text fields.
#####-ttt-ttt, the t,s are the text fields although they are usually numbers from a short text data field both the text fields vary in length and for some records the last part of the field is non-existent. Will it be easy to separate because they are from field from different tables?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:34
Joined
Feb 19, 2013
Messages
16,553
it will be a string, number fields can only contain the characters 0-9 plus a - sign.

as far as easy is concerned you can use the val and mid functions or pass to a UDF to use the split function. In a query for the one example you provided you might have

v1:val(mystring)
v2:mid(mystring,7,3)
v3:mid(mystring,11)

but you have only provided one example, would need to see a range of values to see what else needs to be catered for. Other string functions you might need to use include left, right, instr and instrrev.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:34
Joined
May 7, 2009
Messages
19,175
create a function using Split()
Code:
public function splitText(byval t as variant, byval ordinal as integer, optional byval sep as string=" ") as variant
dim v as variant
t= t & ""
if len(t) < 1 or ordinal < 1 then
    exit function
end if
v=split(t, sep)
if ubound(v) <= ordinal - 1 then
    splitText = Trim$(v(ordinal - 1))
end if
end function

t1: splitText([textfield], 1, "-")
t2: splitText([textfield], 2, "-")
t3: splitText([textfield], 3, "-")
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:34
Joined
Feb 28, 2001
Messages
27,001
Agree with CJ, the answer to your question depends on the regularity of the format.

However, arnelgp's proposed function would definitely work for most cases. (I'd say "all cases" but I'm conservative on predictions. No offense intended, arnelgp) If you are going to use that function, it must be declared in a general module because it cannot be used in a query if it is defined in a class module (form or report module) because those aren't visible to queries.
 

Users who are viewing this thread

Top Bottom