Fill ListBox with dates in range between two dates on form's record

[mib1019]

I believe this will do what you've asked for:

Private Sub lstChooseDates_AfterUpdate()
    Dim i As Integer
    Dim dates As String
    
    dates = ""
    
    For i = 0 To Me.lstChooseDates.ListCount - 1
        If Me.lstChooseDates.Selected(i) Then
            dates = dates & Format(Me.lstChooseDates.ItemData(i), "MMM, mm/dd") & "; "
        End If
    Next i
    
    If dates <> "" Then
        'remove the last semicolon and space
        dates = Left(dates, Len(dates) - 2)
    End If
    
    Me.txtResult = dates
End Sub

BUT without knowing the details of your project it might be better to have a child table for these dates rather than to denormalize them to save it in a short text field. Good luck with your project.

KD2017, I'm back on this topic again....

I've got the popup form for populating dates between txtStartDate and txtEndDate working perfectly. For simplicity, I want to keep it that way. It generates the necessary piece for the document my user needs. I can see where I might want to be able to actually use the dates in reporting later.

So my question is, how could I populate a child table that is related to the one which holds this text string?



My child table fields would be ID, Detail_ID (the primary key of the table that holds the text field with all the dates), Air_Date (which is derived from the text field, split into the x number of dates that are in that text box).

So I would like to see, when the record is saved with the txtAirDates field, it writes new records to the Child Table: Dates of 7/13, 7/15, 7/17 go to three records in the child table, all with the same Detail_ID and a generated ID in the primary key.

Is this possible?

Thanks!
MIB1019

[kd2017]

Sure!

The primary key should be an Autonumber datatype, access automatically handles creating a unique ID for autonumbers. You would no longer want to have the AirDates text field in the parent table, rather you would derive the string based on a query that looks up AirDates when ever you would need it. You can write a simple vba function to accomplish this lookup and "compile" your string.

[kd2017]

See attached for an example database I've created. In the vba module 'HelperFunctions' you'll find a function I wrote that will build a string as I suggested above. This function can be called from forms, reports, and even queries. I've provided a usage example in both a query and on the 'Parent' form.

You will need to tweak the code, field and object names, to match those in your database.

*Note: a vba function like this works just fine in queries with a relatively small number of records, however it's not very efficient and can be quite slow if you try to run it in a query with a lot of parent records. You shouldn't have any problems running the function in forms or reports. If you need to run the function in a query I'd suggest filtering the records to as few as possible before running the vba function.

[mib1019]

kd2017,

Correct, the child table primary key would be autonumber.

I will play with this and see how it works out. Thanks so much for our help!

MIB1019

[mib1019]

KD2017, I have looked at your sample. Thanks for your input there.

The current table, tblIODetails, which is a child of table called tblIOs. The child table currenlty has the has the text box of dates. What I am wanting to do is extract the dates into separate records for the purpose of reporting. Would like to do this dynamically and to a temporary table perhaps, since the user may go in and edit the airdates textbox and so I would only want current values.

So if I have a record in the tblIODetails table that's got these dates in it, 6/17, 6/19, 6/23, 6/25, I'd like to retrieve those dates as separate records.

Thanks for your help. You are very patient.
MIB1019

[kd2017]

... since the user may go in and edit the airdates textbox and so I would only want current values...

This is why I suggest that you get rid of the txtAirDates field all together and only "calculate" the string when needed for presentation.

You can use the split function to break up your date string into an array, then use CDate to convert to a date datatype.
For example:

    Dim dateArray() As String
    Dim i As Integer
    
    dateArray = Split(AirDates, ",")
    
    For i = 0 To UBound(dateArray)
        'This loop will print each date individually
        'From here you can insert records into your child table
        'or do what ever it is you need to do.
        Debug.Print CDate(dateArray(i))
    Next i

Where AirDates = a string like "6/17, 6/19, 6/23, 6/25". Note that the example string you've provided doesn't have a year so CDate will assume the current year. You can use the DateSerial function to specify the year if it's not available within the string.

[mib1019]

Thanks so much kd2017. You're very helpful.