INNER JOIN to a split(array) function?

[kman42]

Is it possible to perform a join operation to the result of a split function?



I have a simple table and one of the fields is an array of dates (e.g. 1/1/2010, 2/1/2010, 3/1/2010). I didn't bother putting this info in another table because it is pretty rare for there to be multiple dates, but it happens. For most of my database I just need the first date which I can pull out using a split() or str(). But for one particular query I need to have all the dates so I want to create a JOIN on the result of the split() function. Is this possible?

I tried:

SELECT tbl_Initiatives.Name, DateGroup.Date
FROM tbl_Initiatives INNER JOIN (SELECT tbl_Initiative.pk_InitiativeID, Split(tbl_Initiatives.Dates, ",")) AS DateGroup

And I got stuck. Any suggestions?

Thanks!

[ajetrumpet]

very doubtful. one thing you 'cannot' do is use the split() function in the actual query statement. if it's possible at all, you have to concatenate a function call into the actual statement. the called function is where you would execute the split and other thing. but it can only return a single value.

and I don't think it will work. you can use function calls and such on the RIGHT side of criteria statements, but I don't believe you can use their returns as actual parts of the select statement. (when writing it in the query builder)

[June7]

I agree that using the Split function in the Query is not possible. Split creates an array.

However, functions can be used in a SELECT statement to return values in fields constructed by expression. It's done all the time as with Date() or DMax() for examples. Calling a custom function would be no different than with these intrinsic functions. Like:

SELECT MyFunction(argument) As MyValue FROM tablename;

[ajetrumpet]

Calling a custom function would be no different than with these intrinsic functions. Like:

SELECT MyFunction(argument) As MyValue FROM tablename;

this just made me think of something else. Doing that above is not useful at all. Either that, or I haven't seen it done. Why would you need a function to figure out what field you are selecting? Yeah...that's a very good point. It's probably been done, and it relates directly to this thread's question.

Mr. OP, what you want, if it relates to above, can't be done because you're trying to use a function that returns a VALUE as part of the JOIN statement. You don't join tables on values or value names, you join them on field names.

So the easy answer for you is NO. And there ya have it. I'm out of this thread now. Good luck to ya, sir!

[June7]

The function is not to determine what field to select, it is manipulation of the input argument value in some manner and then returning a value as result of that manipulation. This is just another way of constructing a field that doesn't exist in the table. Another way to construct a 'new' field is by concatenation of several fields. Like:

SELECT LastName & ", " & FirstName As ClientName FROM tablename;

This query could be saved and then this field could be used in a join with another table/query that has this value.

However, as we said, don't think the Split function can be used this way.

[kman42]

Ok, I'm trying another approach. I created a table called ReceiptDates that will have all the dates associated with each record, but now I'm having problems figuring out how to update this table and display all the dates in a text field.

I created a text field that was bound to a function that would loop through all the RecieptDate records for the associated record and create a list of dates: "3/15/2011, 4/15/2011, 5/15/20011" This works fine.

I then created some code that I wanted to run when the text field was updated that would delete all the ReceiptDates and then loop through the array of dates in the field and run an insert query for each.

The problem is that I can't trigger the event for the latter function if the field is bound to the display function. I could put a button that would trigger the event and pop up a dialog asking for the dates, but I don't want it to be that complicated. I just want the ReceiptDate rows to be deleted and reinserted if the user changes the text field.

Whew. I hope that makes sense and that someone has a suggested solution.

thanks,
kman

[June7]

Not sure need to have a table of receipt dates that requires maintenance. Use a query. Queries can be treated like tables. The saved query would be like: SELECT DISTINCT datefield FROM table;
Then use this query whereever you would use the ReceiptDates table.

What do you mean by 'display all the dates in a text field'? Do you mean a combobox list?

[kman42]

Records in the main table (tbl_Initiatives) can have multiple ReceiptDates associated with them, which is why I created a ReceiptDates table. Each record in tbl_Initatives needs to display all of the ReceiptDates for that record. There may be 1-5 associated with each record. They need to be displayed as a list: "1/15/2011, 2/15/2011, 3/15/2011, 4/14/2011"

So I created a text field on the form for tbl_Initatives that lists all of the ReceiptDates. It generates the list by performing a query on the ReceiptDates table, looping through the resulting records and adding commas where necessary. This all works fine.

But I want the user to be able to update the list from the text field. I figured that I could delete all the associated records in the REceiptDates table and just INSERT new records corresponding to the new values in the text field.

I'm now thinking that instead of binding the text field to the display function, I should put the function in the On Current event.

kman

[June7]

Getting to be a long thread. I am losing track of what you really need to do.

Thread originally start with need to parse a multi-value field.

If you still need assistance and before I try to offer more advice, could you restate exactly what it is you want to accomplish because I am still not seeing requirement to create and maintain another table.