Solved

text from table

Posted on 2014-04-28
13
184 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
  • 7
  • 6
13 Comments
 
LVL 47

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 47

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
 

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 47

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 47

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 47

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 47

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 47

Expert Comment

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

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now