Solved

text from table

Posted on 2014-04-28
13
193 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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
 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

777 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