Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    Amsterdam
    Posts
    3

    Unanswered: Manupilating tables with modules

    hi,
    i have a database,

    there is a field 'from date' and 'to date' i can calculate how many days is the difference but i also want to modify table or create new table with each date

    for example: from date: 4-february to date: 7 february i want to split this data and to be shown as:

    from date 4 feb to date 5 feb
    from date 5 feb to date 6 feb
    from date 6 feb to date 7 feb
    how can make access to calculate the difference of dates and split the date day by day and create new lines in the database with splitted data.

    Thank you,

  2. #2
    Join Date
    Feb 2004
    Posts
    137
    The following sub demonstrates how you can split a given date range and create a split recordset as you described; in this case, it works with Table1, which has two fields, "From" and To":

    Public Function dateSplit(dteFrom As Date, dteTo As Date)

    Dim dteIndex As Date

    For dteIndex = dteFrom to dteTo -1
    DBEngine(0)(0).Execute _
    "INSERT INTO Table1 ( From, To ) SELECT #" & dteIndex _
    & "#, #" & dteIndex+1 & "#;"
    Next dteIndex

    End Function



    Calling this function, for example, like this:

    Call dateSplit(#2/7/2004#,#2/12/2004#)

    ....will create the following records in Table1:

    From | To
    2/7/2004 | 2/8/2004
    2/8/2004 | 2/9/2004
    2/9/2004 | 2/10/2004
    2/10/2004 | 2/11/2004
    2/11/2004 | 2/12/2004

  3. #3
    Join Date
    Mar 2004
    Location
    Amsterdam
    Posts
    3
    Hi, Thank you very much for your reply,
    Since im just starting to access, I have another question.

    Should i put this code in a button in the form or put it in the query?

    Thanks in advence,

  4. #4
    Join Date
    Feb 2004
    Posts
    137
    I would attach this code to the OnClick event on a button on the form. Paste the function as shown and set the button's OnClick property to:
    =dateSplit([txtFrom], [txtTo])

    You would, of course, substitute the [txtFrom] and [txtTo] with the actual name of the text fields which hold the date information.

  5. #5
    Join Date
    Mar 2004
    Location
    Amsterdam
    Posts
    3

    Unhappy

    Hi i really think that im stupid.
    i couldnt make the code working

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •