Can I use a function returning an array in an append query?

[cherold]

I'm trying to divide up a string in a table into several strings and use those to populate a different table. I wrote a function to chop the original string up, and right now I have it returning an array of strings, each of which has an unpredictable number of elements (although not, I think, more than 3). So the results of running the function on a three-record table would look something like:



("orange", "apple")
("apple")
("apple", "orange", "grape")


What I would like is to use these results to populate a table that looks like this (with no duplicates):

Table: Fruit
fruitID - autonumber
fruit - string

Can this be done with an append query? Or do the variable arrays mean I need to do a VBA script or something?

[ItsMe]

What does the data in the original table look like? Do you have multiple words that describe different fruit in a single field? Apple, Orange, Pear

[June7]

Need VBA because of variable arrays.

I am confused. If you ran function on 3-record table and the output is still 3 records, what did the original data look like? Just no quote marks?

[cherold]

Original data would be a string for each record like
"orange-apple"
"apple"
"apple/grape, orange"

I then chop up the strings via delimiters like "-" and "/" and "," and wind up with three arrays to replace the three strings.
("orange", "apple")
("apple")
("apple", "orange", "grape")

[June7]

I suggest you expand the code to accomplish saving the unique records.

Can use a DLookup() to check if item already in table and if not, append record.

[ItsMe]

I suggest you expand the code to accomplish saving the unique records.

Can use a DLookup() to check if item already in table and if not, append record.

It is pretty much as simple as that. You really are not dealing with arrays types at this point and it is simply a matter of cleaning up your data before storing it in a production table.

[orange]

I'm not clear on where you will store the results. I think you would be well served to review Normalization(starting here).

[cherold]

I seem to have been unclear, possibly because what I want to do is easier than I think and you're not realizing how little I know? So let me explain what I would want to happen.

I have a function called ParseFruit that returns an array of strings. So you call parseFruit("apple-orange, kiwi") and it returns the array ("apple", "orange", "kiwi").

I want each element of each array to wind up in the "fruit" field of a table that contains one fruit field and one id field.

Therefore, I would want a Query like:

Insert into Fruits (fruit)
SELECT DISTINCT parseFruit (Food.FruitList) FROM Food;

Only doing that gives me a Data Type Mismatch, I'm assuming because the query doesn't know what to do with a function that returns an array of values. So I don't know how to get the fruit values into the table.

[June7]

Something like:

Dim rs AS DAO.Recordset, x As Integer, aryFruit As Variant
Set rs = CurrentDb.OpenRecordset("SELECT fieldname FROM tablename;")
rs.MoveFirst
While Not rs.EOF
   aryFruit = Split(parsefruit(rs!fieldname), ",") 
   For x = 0 To UBound(aryFruit)
      If IsNull(DLookup("Fruit", "FruitList", "Fruit='" & ary(x) & "'")) Then   
         CurrentDb.Execute "INSERT INTO FruitList(Fruit) VALUES('" & aryFruit(x) & "')"  
     End If
   Next
Wend

Modify the parsefruit function to return a string without embedded spaces and quote marks - just comma separated values. Or eliminate that function:

aryFruit = Split(Replace(Replace(Replace(rs!fieldname, "-", ","), "/", ","), " ", ""), ",")

[ItsMe]

...it returns the array ("apple", "orange", "kiwi")....

If it is retuning, "apple", "orange", "kiwi", that is a literal string and can be written to your table; June describes how in previous post.

If your function is returning a variable of Type Array. You will need to incorporate the array variable in the loop and use the looping index to retrieve tha various elements within the array.

Is it possible to post the function that creates this Array? If you like, you can PM the function to me and I will post back here with an opinion how to incorporate an Array variable in DAO code like June posted.