How do I do Text To Columns in Access? 2

[sprite]

I have a table with a list of Business Locations
(BL) and for each BL, there may be multiple Account IDs (AI). For each unique AI, there is a row. It looks something like this:

BL AI
12345 135790
12345 124680
12345 567321
54321 975315
54321 456123

What I need is a table that looks like this:

BL AIs
12345 135790, 124680, 567321
54321 975315, 456123

How do I do this?
Thanks,
Sprite

 

[sprite]

HORRAY!!! Many many thanks to PHV and Leslie! It works. I could never have done it without you. This is awesome. I have been trying to do this for so long.
Hope our paths meet again.
Sprite

 

[PHV]

Sorry for the typo. Thanks to lespaul for the correction.

 

[duncandd]

I'm new to the list! I found this thread by accident and it looks exactly like what I need except that I'm using sQL server instead of Access. Could someone please convert this code to Transact SQL for me? Unfortunately, I'm also a newbie at writing code. TIA!

DD

 

[PHV]

Write a SP (Stored Procedure).
If you don't know how to do that, ask your DBA (DatraBase Administrator)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

Or post in Forum183 - SQL Server Programming

Leslie

 

[mpm32]

This is an old topic and I am trying to use this function GetAIList modifying it to my application.

Here is what I have;

Public Function GetUnitList(ID)
Dim db As Database
Dim rs As Recordset
Dim strUnit As String ' string to contUnitn list
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Unit FROM AP Business Units WHERE ID=" & ID)
strUnit = ""
Do Until rs.EOF
If strUnit = "" Then
strUnit = rs!Unit
Else
strUnit = strUnit & ", " & rs!Unit
End If
rs.MoveNext
Loop
'release the memory
Set rs = Nothing
Set db = Nothing
GetUnitList = strUnit
End Function


I have created a module with this code but when I try to call the function from within a query, it gives me a message of "Undefined Function GetUnitList in expression" when running the query.

Can anyone help with this?

Thanks in advance

 

[willir]

mpm32

We usually post new problems as a new thread.

You may have to be specific for DAO (and include it in the references). The important tweak in blue...

Public Function GetUnitList(ID [COLOR=blue]as Variant[/color]) [COLOR=blue]as String[/color]

Dim rs As [COLOR=blue]DAO.[/color]Recordset
Dim strUnit As String ' string to contUnitn list

Set rs = CurrentDB.OpenRecordset("SELECT Unit FROM " _
& "AP Business Units WHERE ID=" & ID)
strUnit = ""
rs.MoveFirst

Do Until rs.EOF
    strUnit = strUnit & ", " & rs!Unit
    rs.MoveNext
Loop

If Left(strUnit, 2) = ", " Then
    strUnit = Right(strUnit, Len(strUnit) - 2) 
End If

'release the memory
Set rs = Nothing

GetUnitList = strUnit
End Function

To check your references. ALT-F11 to open up the VBA coding window. From the menu, "Tools" -> "References".

Look for / add Microsoft DAO 3.6 Object Library

Richard

 

[dhookom]

There is a generic concatenate function with sample usage at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mpm32]

Thanks for your help.

I do have Microsoft DAO 3.6 Object Library checked off. And I tried you tweaks Richard.


Duane, I tried your concatenate function as well. I seem to be having problems calling any function I write. I still get the message of "Undefined Function (functionname) in expression" no matter the function. I think I have an internal issue with Access. I'm usually pretty good at copying code and making it work.

 

[dhookom]

mpm32,
What did you name the module that you pasted the concatenate function into?

What is the exact expression you use in the concatenate function?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[willir]

mpm32

Are you placing the module in the right location. Perhaps your scope is wrong.

From the database window, select "Modules". If none created, select "New". ... OR ... from the menu, select "Insert" -> "Module"

At the top of the module, you should see...

Option Compare Database
[COLOR=blue]Option Explicit[/color]

You may have to add the Option Explicit.

IF YOU add your code on a form, or report, then the code will only run when the form or report is open -- this is what is meant by being in scope. Creating a public function will from anywhere.

Richard

 

[dhookom]

Richard,
I suggest we end this thread since mpm32 has created a new thread in another forum with the exact same question. I hate when that happens ;-)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[willir]

Well that was my suggestion in the first place. Take care Duane