Solved

text from table

Posted on 2014-04-28
13
200 Views
Last Modified: 2014-04-30
How do I get the all the values from a named column in a query into a single text field on a form, subject to an upper limit of 200 characters.

Form: MainForm
Field: TextModelNumbers
Query: ModelsQuery
Column: ModelNumbers
0
Comment
Question by:DatabaseDek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40027879
Please provide an example; sample data would be better.

Are you talking about concatenating the values from multiple records in your [TextModelNumbers] column, into a single textbox on the form?
0
 

Author Comment

by:DatabaseDek
ID: 40029108
That is correct as in:

The values come from the query ModelsQuery

ModelNumbers
A1
B2
C3
D4

to go a field (TextModelNumbers) on the MainForm as
A1, B2, C3, D4

With a limit of 200 Characters
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40029149
Is this the only field in the query?  What does the query look like?  Generally, the best way to do this is to use some form of concatenation function.  I use something that looks like:
Public Function fnConcat(FieldName as String, TableName as string, _
                                Optional Criteria as Variant = NULL, _
                                Optional Separator as string = ", ", _
                                Optional WrapWith as string = "") as string

    Dim strSQL as String
    Dim rs as DAO.Recordsource

    strSQL = "SELECT [" & FieldName & "] as ConcatField " _
                & "FROM [" & TableName & "] " _
                & ("WHERE " + Criteria)  'this second line will eval to NULL if Criteria is NULL
    
    'The next line will remove extra [ and ] characters
    strSQL = Replace(Replace, strSQL, "[[", "["), "]]", "]"))

    set rs = currentdb.openrecordset strSQL, , dbfailonerror
    While not rs.eof

        fnConcat = fnConcat & Separator & WrapWith & rs!ConCatField & WrapWith

        rs.MoveNext

    Wend

    'Strip the leading separator from the return value
    fnConcat = Mid(fnConcat, Len(Separator) + 1)

    rs.close
    set rs = nothing

Exit Function

Open in new window

You would call this something like:

= fnConcat("ModelNumbers", "ModelsQuery")

HTH, Dale
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:DatabaseDek
ID: 40029258
Hi Dale

I get error (expected end of statement) with:

    set rs = currentdb.openrecordset strSQL, , dbfailonerror

And presumably I need:

If Len(funconcat)>199 then
       msgbox "Some text"
end if
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40029285
sorry, need to wrap that in ()

    set rs = currentdb.openrecordset(strSQL, , dbfailonerror)

Yes, I would change:
While not rs.eof

    fnConcat = fnConcat & Separator & WrapWith & rs!ConCatField & WrapWith

    rs.MoveNext

Wend

Open in new window

to:
Do while not rs.eof

        fnConcat = fnConcat & Separator & WrapWith & rs!ConCatField & WrapWith

        if Len(fnConcat) - Len(Separator) >200 then Exit Do

        rs.MoveNext

Loop

Open in new window

This could have the effect of returning > 200 characters if your ModelNumber field is long.  For example, if Len(fnConcat)-Len(Separator) = 199 then it will allow you to add another ModelNumber to the string and will then exit.  If you really want to keep it to under 200 characters, you would need to check the length after removing the leading separator character(s).  If it is still over 200, then you would need to search backwards for the last occurrence of the separator:

if Len(fnConcat) > 200 then
    fnConcat = Left(fnConcat, instrrev(fnConcat, separator) - 1)
endif
0
 

Author Comment

by:DatabaseDek
ID: 40030077
Wow!

Last record would be fine for about 200 characters.

Should Dim rs as DAO.Recordsource be DAO.Recordset as I get error

and I get augment ("Replace" highlighted on second placement) in statement not optional with strSQL = Replace(Replace, strSQL, "[[", "[", "]]", "]")
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40030365
yes, recordset, not recordsource.

The problems of typing on an iPad.

strSQL = Replace(Replace(strSQL, "[[", "[", "]]", "]")
0
 

Author Comment

by:DatabaseDek
ID: 40031402
I am still getting augment not optional on

strSQL = Replace(Replace(strSQL, "[[", "[", "]]", "]"))

Have I put the last bracket in the right place and should this statement say what to replace these brackets with?

Or am I missing some library references? I have DAO 3.6 and Activex Data Objects 2.5

Public Function fnConcat(DoorNumber As String, Sets As String, _
                                Optional Criteria As Variant = Null, _
                                Optional Separator As String = ", ", _
                                Optional WrapWith As String = "") As String

    Dim strSQL As String
    Dim rs As DAO.Recordset

    strSQL = "SELECT [" & DoorNumber & "] as ConcatField " & "FROM [" & Sets & "] " & ("WHERE  (((Sets.ItemNumber)=[Forms]![ProjectForm]![Details].[Form]![ItemNumber]))" + Criteria)  'this second line will eval to NULL if Criteria is NULL
    
    'The next line will remove extra [ and ] characters
    'strSQL = Replace(Replace(strSQL, "[[", "[", "]]", "]"))

    Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
Do While Not rs.EOF

        fnConcat = fnConcat & Separator & WrapWith & rs!ConCatField & WrapWith

        'If Len(fnConcat) - Len(Separator) > 200 Then Exit Do

        rs.MoveNext

Loop

    'Strip the leading separator from the return value
    fnConcat = Mid(fnConcat, Len(Separator) + 1)

    rs.Close
    Set rs = Nothing

End Function

Open in new window

If I disable the two lines above it works but I get all values from table sets so it seems to be ignoring the where clause. Is the where clause wrong or am I calling the function in the wrong way with:
Note = fnConcat("DoorNumber", "Sets")

Derek
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40031610
It can get confusing, and it looks like I dropped a right paren ).

The Replace syntax is:

Replace(ValueToSearch, Character(s) to replace, Replace with what), you need to add a ) between the "[" and the "]]"

strSQL = Replace(Replace(strSQL, "[[", "["), "]]", "]"))

You could also have done this as:

strSQL = Replace(strSQL, "[[", "[")
strSQL = Replace(strSQL, "]]", "]")
0
 

Author Comment

by:DatabaseDek
ID: 40031618
That's great.

I take it that you did not get the edited version of the question.
It works but I get all the values from the table instead of those with the where clause. Can you revisit the comment above when it has refreshed. It's just the records I am getting now.
0
 
LVL 48

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 40031628
That function was a generic function, used for all sorts of things.  Other than the problem with the Replace string, you really didn't need to change anything in the function, you just modify the way you call it.  So, lets go back to the original function, with the necessary modifications:
Public Function fnConcat(FieldName as String, TableName as string, _
                                Optional Criteria as Variant = NULL, _
                                Optional Separator as string = ", ", _
                                Optional WrapWith as string = "") as string

    Dim strSQL as String
    Dim rs as DAO.Recordset

    strSQL = "SELECT [" & FieldName & "] as ConcatField " _
           & "FROM [" & TableName & "] " _
           & ("WHERE " + Criteria)  
    
    'The next line will remove extra [ and ] characters
    strSQL = Replace(Replace, strSQL, "[[", "["), "]]", "]"))

    set rs = currentdb.openrecordset strSQL, , dbfailonerror

    Do while not rs.eof

        fnConcat = fnConcat & Separator & WrapWith & rs!ConCatField & WrapWith

        if Len(fnConcat) - Len(Separator) >200 then Exit Do

        rs.MoveNext

    Loop

    'Strip the leading separator from the return value
    fnConcat = Mid(fnConcat, Len(Separator) + 1)

    rs.close
    set rs = nothing

Exit Function

Open in new window

Then call it like:

Dim strCriteria as string
strCriteria = "[ItemNumber] = " & [Forms]![ProjectForm]![Details].[Form]![ItemNumber]
Note = fnConcat("DoorNumber", "Sets", strCriteria)

If you are calling this from a button or some other code that is on Details subform, you could modify the strCriteria definition to

strCriteria = "[ItemNumber] = " & me![ItemNumber]
0
 

Author Closing Comment

by:DatabaseDek
ID: 40032350
Fabulous!

With thanks Derek
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40032463
sorry it took so long to get that working.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question