Solved

text from table

Posted on 2014-04-28
13
196 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

766 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