Merging info in one field

capsula4 · Jan 15, 2008

capsula4 · Jan 15, 2008

Thanks Gizmo!

I tried to use the module you posted but I couldn't really make it work in my database.

I'm not sure what fields should I put in the query...

[TRANSACTION ID] | [PRODUCTS] | fConList([TRANSACTION ID],[PRODUCTS])

But doesn't work.. not sure how to make it work..

capsula4 · Jan 16, 2008

Thank you for the help, I have just realised that I was importing just the module and didn't make any modification to it! I just opened it now and realised that I actually have to change the module.

I'm pretty newbie to access, I thought that the module would work for any DB as long as the arguments were okay.

capsula4 · Jan 22, 2008

capsula4 · Jan 22, 2008

The sample I posted was representative, here is the exact query I'm using:

Code:

SELECT CONTENEDORES.N_OP, CONTENEDORES.ID_CONTENEDOR, CONTENEDORES.CONTENEDOR
FROM CONTENEDORES;

N_OP = OPERATION NUMBER (TRANSACTION ID)
ID_CONTENEDOR = ID CONTAINER (PRODUCT ID)
CONTENEDOR = CONTAINER (PRODUCT)

I connected them using the TRANSACTION ID, not the CONTAINER ID since one transaction may have more than one container (1-many relationship).

If you can gimme a lil help with this it would be wonderful!! And I guess that it might be useful for many people.

capsula4 · Jan 23, 2008

Code:

SELECT CONTENEDORES.N_OP, CONTENEDORES.ID_CONTENEDOR, CONTENEDORES.CONTENEDOR
FROM CONTENEDORES
WHERE (((CONTENEDORES.N_OP)=1));

Pretty similar to what you said, I guess it would have worked!

Anyway, is really necessary to make the criteria thing? Cause I would like to have the list of containers for all of the operations.

Cosmos75 · Jan 23, 2008

Here's another way - DConcat function - Concatenating field values for records matching a query's GROUP / SORT BY or WHERE clause . (old link). The code is a lot longer but it's meant to be a generic function.

capsula4 · Jan 23, 2008

Thanks a lot Gizmo! Gotta say that works pretty good now (N_OP is an integer field in my case). The only lil thing is that entries in the query are being duplicated, if the OP has 3 containers, then I have 3 registries of the same OP. Despite this, it works fine for what I want in a report by just including once the field.

And also thank Cosmos75 for your contribution! So far the code Gizmo gave me is okay, in case I gotta go further, I may take a look into it.

capsula4 · Jan 23, 2008

Actually when the field I'm trying to list horizontally is empty, it gives an error.

I tried to add an if condition but definitively I'm still too newbie with modules...
I guess the error is in the condition "RS!RECORDER = Null", how should it be so as to recognize RECORDER is empty?

Code:

    Do Until RS.EOF
            If RS!RECORDER = Null Then
            Else
                If strText = "" Then 'This If statment prevents a  single comma at the begining of the text","
                    strText = RS!RECORDER
                    Else
                    strText = strText & ", " & RS!RECORDER
                End If
            End If
        RS.MoveNext

capsula4 · Jan 23, 2008

Using the NULL if it gives this error:

"Error 94: not valide use of Null" (im translating froms spanish)

Going back to the first sample in which I gave you the SQL query:

It highlights: "strText = RS!CONTENEDOR"

....

To my surprise, I also tried taking out the If condition with the NULL thing and gives exactly the same error!

capsula4 · Jan 23, 2008

This is even more weird! I just found out that the error was given if the FIRST field was empty, so I changed:

Code:

strText = RS!CONTENEDOR

with this

Code:

strText = "" & RS!CONTENEDOR

Now it works!!!

I'm gonna try to make this module with 3 arguments, TABLE, CONCATENATED FIELD and GROUP BY, actually the only argument might the GROUP BY. If I can't make it I will ask for some help I guess.

I know that someone already posted a module like what I'm trying to do but I couldn't really make it work.

I opened a topic about this module at Modules/VBA forum category:
http://www.access-programmers.co.uk/forums/showthread.php?p=666122

capsula4 · Jan 23, 2008

Uncle Gizmo said:
Sorry, you have lost me, I do not know if you still have a problem or not. Please could you clarify the situation.

I had a problem but since it was module stuff I decided to open a new topic and someone already answered me. So I don't have any problem!

I modified a bit the module so as to make it generic, enter here in case you wanna see it (it's finished):

http://www.access-programmers.co.uk/forums/showthread.php?p=666122

Thank you for all your help Gizmo!! You are really patient!!

Cosmos75 · Jan 23, 2008

capsula4 said:
And also thank Cosmos75 for your contribution! So far the code Gizmo gave me is okay, in case I gotta go further, I may take a look into it.

I'm happy to share it. While I fully understand that it's much easier to create a custom code for your a particular situation, I like tinkering on generic functions that are reusable.

Uncle Gizmo said:
thats a nice piece of Code .... Thanks, will use it I'm sure

Thanks for the compliment! (especially coming from a guru such as you!)

Merging info in one field

capsula4

Registered User.

capsula4

Registered User.

capsula4

Registered User.

capsula4

Registered User.

capsula4

Registered User.

capsula4

Registered User.

Cosmos75

Registered User.

capsula4

Registered User.

capsula4

Registered User.

capsula4

Registered User.

capsula4

Registered User.

capsula4

Registered User.

Cosmos75

Registered User.

Similar threads

Users who are viewing this thread