how to break apart a large table field into smaller ones in SQL

[mg]

using access2007
i have a memo field in a table
i need to break the memo up into smaller pieces and use those pieces as
fields in my query, not sure how to do this.

eg//
memo field = (red NO, green YES, blue YES, purple NO) all
in fixed positions
i need to break apart the colors into seperate fields, so my access report
can group on them

i thought i could use something like mid$ in the SQL
any ideas?

thank you

 

[John Spencer MVP]

If your data elements are always separated by a comma you can use a VBA
function and the split function to return the parts. You would need to know
the maximum number of pieces.

You can try the one below in your query.
For instance
getSection(MemoField,",",1)
should return
red NO

You could then parse that down to two field with Red and No if you needed to
using standard VBA string functions (Mid, InStr, Trim, Left)

Parse string into sections and get a specific section / Item / token from the
string

Public Function getSection(strIn, _
Optional strDelimiter As String = ";", _
Optional intSectionNumber As Integer = 1)
'==============================================================
' Procedure : getSection
' Author : John Spencer
' Purpose : Return section of string
' Access 2000 and later
'==============================================================

Dim strArray As Variant

If Len(strIn & vbNullString) = 0 Then
getSection = strIn
Else
strArray = Split(strIn, strDelimiter, -1, vbTextCompare)

If UBound(strArray) >= intSectionNumber - 1 Then
getSection = strArray(intSectionNumber - 1)
Else
getSection = Null
End If

End If

End Function


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[mg]

wow, thanks for the coding.
unfortunately, the database is an external odbc, and i cant use anything but
plain-jane sql.
i did get this method to work - but only if i created a linked table (which
slows everything down).
select mid$(table.[memofield],1,15) as memostuff from table
i will try your coding on the linked table.

i could have done it very easily inside VBA into arrays, but i have no idea
how to build a report on the fly without the use of access reports (you
know, like the old days---print "hello world" and it goes to the
printer....)

My first recommendation would be to restructure your database. It is
obvious
that when you have a 1-to-many relation like this, you need another table
to
store the colors and status. However, in the short term, you could create
a
function and pass the memo field and color, and get the colors "value"
back.
Something like:

Public Function MemoColor(ByVal SomeText as Variant,
ByVal SomeColor as String) as
Variant

Dim intCharPos as integer
Dim strColorValue as string

SomeText = NZ(SomeText, "")

intCharPos = instr(SomeText, SomeColor)
if intCharPos = 0 then
MemoColor = Null
Exit Function
endif

strColorValue = mid(SomeText, intCharPos + Len(SomeColor))
'if there is a comma or ) after the color,
'thenstrip it and everything to the right
intCharPos = instr(strColorValue, ",")
if intCharPos = 0 then intCharPos = instr(strColorValue, ")")
if intCharPos > 0 then
strColorValue = left(strColorValue, intCharPos - 1)
endif
MemoColor = Trim(strColorValue)

End Function

Then, in your query you could do something like:

SELECT ID, MemoColor([MemoField], "red") as Red,
MemoColor([MemoField], "green") as Green,
MemoColor([MemoField], "blue")
FROM yourTable

HTH
Dale

using access2007
i have a memo field in a table
i need to break the memo up into smaller pieces and use those pieces as
fields in my query, not sure how to do this.

eg//
memo field = (red NO, green YES, blue YES, purple NO)
all
in fixed positions
i need to break apart the colors into seperate fields, so my access report
can group on them

i thought i could use something like mid$ in the SQL
any ideas?

thank you

 

[mg]

again, thanks for the coding, i really appreciate the effort. i will try
these methods.