By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,948 Members | 1,559 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,948 IT Pros & Developers. It's quick & easy.

Concatenate function in a query - struggling with character limitation

P: 48
Hi,

First of all I would like to express my gratitude to all users in this forum. It has really helped me a lot for a long time now, even without being a registered user!

But now I am stuck with a challenge, and I have tried to figure it out by having a look on several similar posts without any luck.

I have used Allen Browne's conc function. http://allenbrowne.com/func-concat.html

I use the function in a query called "Ingredients lengthwise" that puts together multiple rows from another query called "ingredients downward".

My challenge is when the concatenation gives a result longer than 255 characters. What can I do to make sure that the result is not truncated?

The field in the query where the function is called, is named "ingredient list".

I am currently using Access 2007.

Any help is very much appreciated. Thanks in advance!

Martin
Jul 5 '10 #1

✓ answered by nico5038

Hmmm, has something to do with the "downlist" query, but can't get where it goes wrong :-(

So I've changed the function to do all, including the IIF construction from the query, try this function:

Expand|Select|Wrap|Line Numbers
  1. Function fncFillConcat()
  2.  
  3. Dim rsI As DAO.Recordset
  4. Dim rsI2 As DAO.Recordset
  5. Dim rsO As DAO.Recordset
  6.  
  7. ' init table
  8. CurrentDb.Execute ("Delete * from tblIngredientlist")
  9.  
  10. ' Select the unique IDArtikkel
  11. Set rsI = CurrentDb.OpenRecordset("Select Distinct IDArtikkel from Oppskrifter")
  12. Set rsO = CurrentDb.OpenRecordset("tblIngredientlist")
  13.  
  14. ' Loop to add one row for each IDArtikkel
  15. While Not rsI.EOF
  16.     rsO.AddNew
  17.     rsO!IDArticle = rsI!idartikkel
  18.     Set rsI2 = CurrentDb.OpenRecordset("Select [råvaredata], [navn i produktdek], [råvarenavn] FROM råvare INNER JOIN Oppskrifter ON råvare.Artikkelnr=Oppskrifter.IDRåvare WHERE IDArtikkel = '" & rsI!idartikkel & "'")
  19.     While Not rsI2.EOF
  20.        If IsNull(rsI2![råvaredata]) = True Then
  21.           If IsNull(rsI2![navn i produktdek]) = True Then
  22.               rsO!Ingredientlist = rsO!Ingredientlist & ", " & rsI2![råvarenavn]
  23.           Else
  24.               rsO!Ingredientlist = rsO!Ingredientlist & ", " & rsI2![navn i produktdek]
  25.           End If
  26.        Else
  27.           If IsNull(rsI2![navn i produktdek]) = True Then
  28.               rsO!Ingredientlist = rsO!Ingredientlist & ", " & rsI2![råvarenavn]
  29.           Else
  30.               rsO!Ingredientlist = rsO!Ingredientlist & ", " & rsI2![navn i produktdek] & " (" & rsI2![råvaredata] & ")"
  31.           End If
  32.        End If
  33.        rsI2.MoveNext
  34.     Wend
  35.     ' remove leading ", "
  36.     rsO!Ingredientlist = Mid(rsO!Ingredientlist, 3)
  37.     rsO.Update
  38.     rsI.MoveNext
  39. Wend
  40.  
  41.  
  42. End Function
  43.  
This does the trick for me :-)

Nic;o)

Share this Question
Share on Google+
31 Replies


NeoPa
Expert Mod 15k+
P: 31,769
Hi Martin. Welcome to Bytes!

I'm not sure exactly where this is occurring. Would it be possible to post the details of your current situation including the SQL where the function is called and the VBA of the function itself.
Jul 5 '10 #2

P: 48
Hi NeoPa,

Thank you very much.

Credits go to Allen Browne for the function:
Expand|Select|Wrap|Line Numbers
  1. Public Function ConcatRelated(strField As String, _
  2.     strTable As String, _
  3.     Optional strWhere As String, _
  4.     Optional strOrderBy As String, _
  5.     Optional strSeparator = ", ") As Variant
  6. On Error GoTo Err_Handler
  7.     'Purpose:   Generate a concatenated string of related records.
  8.     'Return:    String variant, or Null if no matches.
  9.     'Arguments: strField = name of field to get results from and concatenate.
  10.     '           strTable = name of a table or query.
  11.     '           strWhere = WHERE clause to choose the right values.
  12.     '           strOrderBy = ORDER BY clause, for sorting the values.
  13.     '           strSeparator = characters to use between the concatenated values.
  14.     'Notes:     1. Use square brackets around field/table names with spaces or odd characters.
  15.     '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
  16.     '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
  17.     '           4. Returning more than 255 characters to a recordset triggers this Access bug:
  18.     '               http://allenbrowne.com/bug-16.html
  19.     Dim rs As DAO.Recordset         'Related records
  20.     Dim rsMV As DAO.Recordset       'Multi-valued field recordset
  21.     Dim strSql As String            'SQL statement
  22.     Dim strOut As String            'Output string to concatenate to.
  23.     Dim lngLen As Long              'Length of string.
  24.     Dim bIsMultiValue As Boolean    'Flag if strField is a multi-valued field.
  25.  
  26.     'Initialize to Null
  27.     ConcatRelated = Null
  28.  
  29.     'Build SQL string, and get the records.
  30.     strSql = "SELECT " & strField & " FROM " & strTable
  31.     If strWhere <> vbNullString Then
  32.         strSql = strSql & " WHERE " & strWhere
  33.     End If
  34.     If strOrderBy <> vbNullString Then
  35.         strSql = strSql & " ORDER BY " & strOrderBy
  36.     End If
  37.     Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
  38.     'Determine if the requested field is multi-valued (Type is above 100.)
  39.     bIsMultiValue = (rs(0).Type > 100)
  40.  
  41.     'Loop through the matching records
  42.     Do While Not rs.EOF
  43.         If bIsMultiValue Then
  44.             'For multi-valued field, loop through the values
  45.             Set rsMV = rs(0).Value
  46.             Do While Not rsMV.EOF
  47.                 If Not IsNull(rsMV(0)) Then
  48.                     strOut = strOut & rsMV(0) & strSeparator
  49.                 End If
  50.                 rsMV.MoveNext
  51.             Loop
  52.             Set rsMV = Nothing
  53.         ElseIf Not IsNull(rs(0)) Then
  54.             strOut = strOut & rs(0) & strSeparator
  55.         End If
  56.         rs.MoveNext
  57.     Loop
  58.     rs.Close
  59.  
  60.     'Return the string without the trailing separator.
  61.     lngLen = Len(strOut) - Len(strSeparator)
  62.     If lngLen > 0 Then
  63.         ConcatRelated = Left(strOut, lngLen)
  64.     End If
  65.  
  66. Exit_Handler:
  67.     'Clean up
  68.     Set rsMV = Nothing
  69.     Set rs = Nothing
  70.     Exit Function
  71.  
  72. Err_Handler:
  73.     MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
  74.     Resume Exit_Handler
  75. End Function
  76.  
The SQL for the query "ingredients lenghtwise" contains some entries in Norwegian, but I hope you will be able to read the coding.

Expand|Select|Wrap|Line Numbers
  1. SELECT Ingrediensliste.IDArtikkel, Ingrediensliste.Name, Concatrelated("råvaretekst","ingrediensliste","[IDArtikkel] = """ & [IDArtikkel] & """") AS Ingrediensliste
  2. FROM Ingrediensliste
  3. GROUP BY Ingrediensliste.IDArtikkel, Ingrediensliste.Name;
  4.  
The memofield in the original table "ingredientdetails" is called in the query "ingredients downwards", without being truncated. So the problem should lie either in the function or in the SQL for the query "ingredients lengthwise.

Martin
Jul 5 '10 #3

nico5038
Expert 2.5K+
P: 3,072
Hi Martin,

Access has a nasty habit of using a 255 character intermediate result when manipulating memo data in a query.
The only solution will be to store the result in the table and refer "directly" to that memo result field.

Nic;o)
Jul 5 '10 #4

NeoPa
Expert Mod 15k+
P: 31,769
I think Nico's right here Martin. It's the SQL that imposes the limit as it seems to determine the field ([Ingrediensliste]) is text.

I don't know a way of telling SQL the resultant field of a calculation should be treated as Memo.
Jul 5 '10 #5

P: 48
Thanks both of you. I think I have figured it out now. Have developed a macro that uses a table as Nico suggested. It took the day as handling tables in VBA is a new area to me, so I havent had tthe ime to test it on the situation I described above, but if Nico's right I shouldn't encounter any problems. Let me come back to you if it's not working as expected.

Thank you once again!
Jul 6 '10 #6

NeoPa
Expert Mod 15k+
P: 31,769
Perfectly fine :)

Thanks for the update.
Jul 6 '10 #7

P: 48
Hi again,

Sorry... probably it is just me :) but it didn't work.

This is an extract of the macro I have now
Expand|Select|Wrap|Line Numbers
  1.         If DCount("[IDArtikkel]", "[Ingrediens bortover]", "IDartikkel = """ & Nåværende & """") = 0 Then
  2.             ingredienstekst = ConcatRelated("råvaretekst", "[ingrediensliste nedover]", "IDartikkel = """ & MyRSIN.Fields("IDartikkel") & """")
  3.             MyRSOUT.AddNew
  4.             MyRSOUT![IDArtikkel] = MyRSIN.Fields("IDartikkel")
  5.             MyRSOUT![Ingrediensliste] = ingredienstekst
  6.             MyRSOUT.Update
  7.         Else
  8.         Betingelse = MyRSIN.Fields("IDartikkel")
  9.         MyRSOUT.MoveFirst
  10.         Do Until MyRSOUT.EOF
  11.             If MyRSOUT!IDArtikkel = Betingelse Then
  12.             ingredienstekst = ConcatRelated("råvaretekst", "[ingrediensliste nedover]", "IDartikkel = """ & MyRSIN.Fields("IDartikkel") & """")
  13.               MyRSOUT.Edit
  14.               MyRSOUT!Ingrediensliste = ingredienstekst
  15.               MyRSOUT.Update
  16.             End If
  17.             MyRSOUT.MoveNext
After I have run it, it still gives a truncated result entered into the table.

Let me try to put my challenge into an English format.
I have a query named Ingredients with the following fields
IDarticle, Ingredient, Amount
1, Flour, 0.75
1, Sugar, 1
1, Butter, 0.5
2, Egg, 0.2
2, Flour, 0.3

and so on....

I would like that query to be transformed to either a query or a table called Ingredientlist
IDArticle, Ingredientlist
1, Sugar, Flour, Butter
2, Flour, Egg

(notice descending order by amount)

I have managed this by using the function concatrelated() in a new query callled Ingredientlist.

My challenge is that when concatenated, the result may be larger than 255 characters, and when that happens the result is truncated. So, as I understand Nico the solution is to create a macro that writes the result from the function into a table which field is formated as a memo field. However, the macro above doesn't seem to help.

The number of entries may vary in the query Ingredients, and additions of new articles may also happen, so the macro needs to handle that issue as well. (as you can see I have tried to do in the macro above)

So... any ideas?

I really appreciate your help!

Best,

Martin
Jul 6 '10 #8

P: 48
I thought it worked, but now.... last post is still valid. Sorry.
Jul 6 '10 #9

nico5038
Expert 2.5K+
P: 3,072
Assuming that the function returns a memofield, try to use the function dirctly in your code like:
Expand|Select|Wrap|Line Numbers
  1. MyRSOUT!Ingrediensliste = ConcatRelated("råvaretekst", "[ingrediensliste nedover]", "IDartikkel = """ & MyRSIN.Fields
  2.  
When this doesn't do the trick, then attach (part of) your mdb as an attachment and I'll have a look.

Nic;o)
Jul 6 '10 #10

ADezii
Expert 5K+
P: 8,705
You should have delved deeper into the Article:
You can use a UNION query with a Memo field to coerce Access into treating the concatenated field as a Memo.
This yields read-only results, so is not always useful.

The idea is to create a table with a similar structure, but a Memo field where you need the concatenated field.
The table has no records. Access looks as the first table in a UNION to determine the data types, so even
though this table has no records, it does coerce Access into treating the field as a Memo, and so there
is enough memory to handle all characters.

In the example below, the table named StructureOnly has a memo field named MuchText. This query then averts the bug
that is causing your described behavior

Expand|Select|Wrap|Line Numbers
  1. SELECT ID, MuchText FROM StructureOnly
  2. Union all
  3. SELECT ID, Field1 & Field2 AS MuchText FROM LotsaText;
Jul 6 '10 #11

P: 48
Hi again,

Thanks Nico, will do :)

Adezil: I did, but the "how-to" in the article is different from my query, as the UNION example deals with two fields, without the function I am trying to use.

Definetly not an expert on the area. Maybe you know about a way to bypass the bug and still using the function in a query? Here is the query I then would need to be looking like UNION example
Expand|Select|Wrap|Line Numbers
  1. SELECT [Ingrediensliste nedover].IDArtikkel, [Ingrediensliste nedover].Name, Concatrelated("råvaretekst","ingrediensliste","[IDArtikkel] = """ & [IDArtikkel] & """") AS Ingrediensliste
  2. FROM [Ingrediensliste nedover]
  3. GROUP BY [Ingrediensliste nedover].IDArtikkel, [Ingrediensliste nedover].Name;
  4.  
Best,

Martin
Jul 6 '10 #12

ADezii
Expert 5K+
P: 8,705
My Norwegian is a little rusty these days, so here is an example in English on how to avoid the 255 character restriction limit by using a MEMO Field in a UNION Query involving a Function. tblFullName is an Empty Table, and the [Full] Field is a MEMO Data Type Field. The result of the fConcatNames() Function will be dumped into this MEMO Field thus avoiding the Bug and the 255 Character restriction.
Expand|Select|Wrap|Line Numbers
  1. SELECT [FName], [LName], [Full] FROM tblFullName;
  2. UNION
  3. SELECT [FirstName], [LastName], fConcatNames([FirstName],[LastName]) FROM Employees
  4. GROUP BY [FirstName], [LastName];
Jul 6 '10 #13

P: 48
Hehe, sorry for not putting all of it in English... bear with me :)

I tried what you suggested, and it looks something like
Expand|Select|Wrap|Line Numbers
  1. SELECT [IDArtikkel], [Navn], [Full] from [Dummy Ingrediensliste nedover]
  2. UNION SELECT [Ingrediensliste nedover].IDArtikkel, [Ingrediensliste nedover].Name, Concatrelated("råvaretekst","[ingrediensliste nedover]","[IDArtikkel] = """ & [IDArtikkel] & """") 
  3. FROM [Ingrediensliste nedover]
  4. GROUP BY [Ingrediensliste nedover].IDArtikkel, [Ingrediensliste nedover].Name;
  5.  
I made a "dummy" table with empy fields, and formatted the Full field as memo... and the result is still truncated.

Here is the sql as it would be in English
Expand|Select|Wrap|Line Numbers
  1. SELECT IDArticle, ArticleName, Concatrelated("ingredientdescription","ingredients","[IDArticle] = """ & [IDArticle] & """") AS Ingredientlist
  2. FROM [ingredients]
  3. GROUP BY IDArticle, ArticleName;
  4.  
A short explanation to the entries
It calls the fields IDarticle and Articlenam from another query named @ingredients@.

The concatrelated() is a usergenerated function, where the first field inside brackets ("ingredientdescription") is the field in the query "ingredients" that I would like to concatenate. (Multiple rows, but the same column)
The second field is the name on the query, and the last field is the where criteria that the function uses to put together fields on multiple rows from the same product.
Jul 6 '10 #14

P: 48
Hi Nico,

I have attached a part of my database. I have named the things you would need in English.

Your startingpoint would be the query "Ingredients downwards". In it, you will find the fields IDArticle and Ingredients.

The next to look into is the query "ingredients lengthwise". Here, I have used the function to concatenate multiple rows from the query "ingredients downwards". BTW, this is how I want it to be concatenated in the end.

As you can see, the result is truncated.

Then, you may have a look in the query "ingredients lengthwise union". Here, I have used the solution proposed by Adezii. Here as well, you will see the result is being truncated.

You will find the function in module 1.

Thanks to all of you for your kind interest in my challenge :)

Best,

Martin
Attached Files
File Type: zip Råvaretest 060710 1700 - Kopi.zip (2.87 MB, 180 views)
Jul 6 '10 #15

ADezii
Expert 5K+
P: 8,705
@Martin Lang
  1. Sorry, need the DB in Access 2003 Version. Missed the part about Access 2007. Can you convert to 2003?
  2. Are you sure that the Function is not doing the Truncating?
Jul 6 '10 #16

nico5038
Expert 2.5K+
P: 3,072
I created a dummy table tblIngredientlist with a text field for the IDArtikkel and a Memo field for the Ingredientlist.

Next I created a function to fill the table:


Expand|Select|Wrap|Line Numbers
  1. Function fncFillConcat()
  2.  
  3. Dim rsI As DAO.Recordset
  4. Dim rsO As DAO.Recordset
  5.  
  6. ' init table
  7. CurrentDb.Execute ("Delete * from tblIngredientlist")
  8.  
  9. ' Select the unique IDArtikkel
  10. Set rsI = CurrentDb.OpenRecordset("Select Distinct IDArtikkel from Oppskrifter")
  11. Set rsO = CurrentDb.OpenRecordset("tblIngredientlist")
  12.  
  13. ' Loop to add one row for each IDArtikkel
  14. While Not rsI.EOF
  15.     rsO.AddNew
  16.     rsO!IDarticle = rsI!IDArtikkel
  17.     rsO!Ingredientlist = Concatrelated("ingredients", "[ingredientlist downwards]", "IDArticle = '" & rsI!IDArtikkel & "'")
  18.     rsO.Update
  19.     rsI.MoveNext
  20. Wend
  21.  
  22.  
  23. End Function
  24.  
You'll notice that the function doesn't truncate the field.
The function will recreate a new table each time you run it, so before using the final query with the concatenated field you'll need to run the function to make sure all data is accurate.

Nic;o)
Jul 6 '10 #17

P: 48
Thanks Nico,

I like the code you created. So clean and neat. WOW! I tried what you did when you mentioned your solution.... but a lot more coding and without any luck :)

I copied it into both the database I attached here. Unfortunately, when I make a report on article 1050, the result is truncated as you can see below.

Hvetemel, Vann, Baguettemel, creme de levain, Potetgranulat, Salt, Gjærkontroll (sukker, emulgator E472e, soyamel, vegetabilsk olje,sukker, emulgator E472e, soyamel, vegetabilsk olje,sukker, emulgator E472e, soyamel, vegetabilsk olje,sukker, emulgator E472e, soyamel, vegetabilsk olje,sukker, emulgator E472e, soyamel, veg/抚஗/

Also, the same thing happen if I go straight to the table, and copy the field into word.

Maybe it is a setup or a configuration on my PC that creates the problem if the problem doesnt occure on your computer? What do you think?

Adezii: Sorry, here you have it in a 2003 version. You might be right that the function is the one that truncates it...but I don't know how to test that. According to Nico, he has been able to use the function without having the result truncated. In the database, you will find two articles. Article 1050 is the one that potentially will be truncated.

I really appreciate your help. Without you guys, I would have probably been struggling with it for ages.
Attached Files
File Type: zip Råvaretest 060710 1700 - Kopi.zip (182.5 KB, 168 views)
Jul 7 '10 #18

nico5038
Expert 2.5K+
P: 3,072
Hmmm, has something to do with the "downlist" query, but can't get where it goes wrong :-(

So I've changed the function to do all, including the IIF construction from the query, try this function:

Expand|Select|Wrap|Line Numbers
  1. Function fncFillConcat()
  2.  
  3. Dim rsI As DAO.Recordset
  4. Dim rsI2 As DAO.Recordset
  5. Dim rsO As DAO.Recordset
  6.  
  7. ' init table
  8. CurrentDb.Execute ("Delete * from tblIngredientlist")
  9.  
  10. ' Select the unique IDArtikkel
  11. Set rsI = CurrentDb.OpenRecordset("Select Distinct IDArtikkel from Oppskrifter")
  12. Set rsO = CurrentDb.OpenRecordset("tblIngredientlist")
  13.  
  14. ' Loop to add one row for each IDArtikkel
  15. While Not rsI.EOF
  16.     rsO.AddNew
  17.     rsO!IDArticle = rsI!idartikkel
  18.     Set rsI2 = CurrentDb.OpenRecordset("Select [råvaredata], [navn i produktdek], [råvarenavn] FROM råvare INNER JOIN Oppskrifter ON råvare.Artikkelnr=Oppskrifter.IDRåvare WHERE IDArtikkel = '" & rsI!idartikkel & "'")
  19.     While Not rsI2.EOF
  20.        If IsNull(rsI2![råvaredata]) = True Then
  21.           If IsNull(rsI2![navn i produktdek]) = True Then
  22.               rsO!Ingredientlist = rsO!Ingredientlist & ", " & rsI2![råvarenavn]
  23.           Else
  24.               rsO!Ingredientlist = rsO!Ingredientlist & ", " & rsI2![navn i produktdek]
  25.           End If
  26.        Else
  27.           If IsNull(rsI2![navn i produktdek]) = True Then
  28.               rsO!Ingredientlist = rsO!Ingredientlist & ", " & rsI2![råvarenavn]
  29.           Else
  30.               rsO!Ingredientlist = rsO!Ingredientlist & ", " & rsI2![navn i produktdek] & " (" & rsI2![råvaredata] & ")"
  31.           End If
  32.        End If
  33.        rsI2.MoveNext
  34.     Wend
  35.     ' remove leading ", "
  36.     rsO!Ingredientlist = Mid(rsO!Ingredientlist, 3)
  37.     rsO.Update
  38.     rsI.MoveNext
  39. Wend
  40.  
  41.  
  42. End Function
  43.  
This does the trick for me :-)

Nic;o)
Jul 7 '10 #19

P: 48
Extremely well done! Thank you so much! The trunc issue is definitely gone.

I have added a few more things in the downlist query, but I will see how you did it and I will probably manage to include those pieces in your master macro :)

But there is one thing I do not know how to do. I need the concatenation to follow the descending order on [amount]. Is there an easy fix to the code you propose here? If you have a look on the downlist query, or the truncated lengthwise query, you will get an idea of what I mean.
Jul 7 '10 #20

NeoPa
Expert Mod 15k+
P: 31,769
I'm no expert on this code, but I suspect if that is required, then simply including the relevant ORDER BY clause for the SQL of the OpenRecordset of one, or both, of your incoming recordsets would do the trick. Good luck :)
Jul 7 '10 #21

P: 48
Agree, I have set myself to selfstudy on sql select statements. I can recommend this link to anyone who is eager to learn. Thanks to everyone ! :)

http://dev.mysql.com/doc/refman/5.1/...ting-rows.html

Best,

Martin
Jul 7 '10 #22

NeoPa
Expert Mod 15k+
P: 31,769
Just be aware Martin that each package has a different flavour of SQL implemented. The link is fine for MySQL work, and also for anything that is common to all, which is most of it to be fair, but the Access Help system is probably a better resource for reference on Jet SQL (as used by Access).
Jul 7 '10 #23

P: 48
I can only believe :) Well, I sorted it out now, so... I am so happy :) Nico has a very nice way of coding. I am studying his solution now to learn the trick he shared here :)
Jul 7 '10 #24

NeoPa
Expert Mod 15k+
P: 31,769
I agree, Nico is one of our cleverest Access experts, and I heartily recommend studying his code for what it can teach you :).

I'm also very pleased you've got a solution to your problem.
Jul 7 '10 #25

nico5038
Expert 2.5K+
P: 3,072
NeoPa is correct about adding the sort, the line should read:

Expand|Select|Wrap|Line Numbers
  1. Set rsI2 = CurrentDb.OpenRecordset("Select [råvaredata], [navn i produktdek], [råvarenavn] FROM råvare INNER JOIN Oppskrifter ON råvare.Artikkelnr=Oppskrifter.IDRåvare WHERE IDArtikkel = '" & rsI!idartikkel & "' ORDER BY Oppskrifter.Mengde DESC;")
  2.  
Same additional advise:
1) Name tables like tblOppskrifter
2) Name queries like qryIngredientlist
Makes it easier in the code to know where to look.
Finally try to use no spaces in fieldnames. I use capitalization like: NavnIProduktdek or underscores like: Navn_i_produktdek.

Glad I could help and it was a pleasure to see a Norwegian database. Success with your application and let me know when stuck on the other needed code.

Nic;o)
Jul 7 '10 #26

P: 48
Thank you Nico,

I will take your additional advices as well. Thank you for your generous help. Or as we would say in Norwegian. Takk skal du ha :)

Just helping out my parent's small bakery company for product declaration, calculation on product cost etc... Selfskilled IT programmer without any formal education on the area. Quite inspiring to get in touch with you guys!

Sooner or later I will probably get in touch with you again... hehe, so thanks for the offer :)

Martin
Jul 7 '10 #27

ADezii
Expert 5K+
P: 8,705
Really nice fix, Nico.
Jul 7 '10 #28

nico5038
Expert 2.5K+
P: 3,072
Thanks ADezii, I thought your UNION solution quite ingenious, but I couldn't get it working, not even with a filled dummy row.
For me the IIF with the "&" looks the most likely suspect for the truncation and the Chinese characters are a funny feature :-)

Last year I've visited several Norwegian harbors on a trip to the Nordkap, thus seeing some Norwegian database did recall some fine "Fjord views" :-)

Nic;o)
Jul 7 '10 #29

P: 1
Dear Martin Lang and nico5038,

Thanks for the question and solution. It was critical to me and after hours of online research here laid the solution.

I actually signed up here on bytes specifically to acknowledge you guys and let you know how fortunate and grateful I am.

Have a good one,

Paulo
Jun 7 '16 #30

PhilOfWalton
Expert 100+
P: 1,430
Sorry, have only just spotted this thread.
Just thought you might be interested, but many years ago, I wrote a database for a flavour making firm. Basically, as you do, flavours are made from Ingredients, but in their case some of those ingredients could be other flavours.
So if for example you wanted the recipe for lemon meringue pie, we would have 1 recipe for pie (that could be used for all sorts of other pies), 1 recipe for lemon curd and 1 recipe for meringue.

Attached are some pictures







The first image shows the recipe for flavour a2
Double clicking on the F5768 SWEET & SOUR (KOSHER) line Brings up the second image which is the recipe for F5768 SWEET & SOUR (KOSHER).
Double Click on the F6307 ONION OIL 1-10 DC line and you get the third image of the recipe for onion oil.

Just thought it might be of interest.
Jun 7 '16 #31

zmbd
Expert Mod 5K+
P: 5,397
This is an old thread dealing with a c concatenation issue; thus, if anyone is interested in PhilOfWalton's database, please start a new thread, a link to Phil's post/this thread can be inserted to provide proper context
Jun 7 '16 #32

Post your reply

Sign in to post your reply or Sign up for a free account.