Results 1 to 11 of 11

Importing all dates between 2 date fields

  1. #1
    UT227 is offline Competent Performer
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    418

    Importing all dates between 2 date fields


    I need to find a way to import all dates between 2 dates into a table. I have a form with a beginning date and ending date. I want to bring in all of the dates including the beginning date and ending date into a table. I'm not sure how to go about getting started on this.

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,304
    Sorry, your question is not very clear.
    Quote Originally Posted by UT227 View Post
    I need to find a way to import all dates between 2 dates into a table.
    Where are the dates? In an Access dB? SQL dB? A text/csv file? Excel Workbook?

    Or do you want to generate a list of dates that are between two dates?

    Assuming an external file:
    One way would be to import the data into a tmp table (the data is temporary and gets deleted, not the table), then use an append query to insert the dates into another table.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,691
    If you just want to add records to table with a date value in each, easiest method to explain is VBA with looping code that writes records to table.
    Code:
    Sub SaveDates(dteStart As Date, dteEnd As Date)
    Do While dteStart <= dteEnd
       CurrentDb.Execute "INSERT INTO tablename(fieldname) VALUES(#" & dteStart & "#)"
       dteStart = dteStart + 1
    Loop
    End Sub
    If you want to import data from an external dataset, that is entirely different.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    UT227 is offline Competent Performer
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    418
    The dates do not exists right now. I have a form [frmDatePicker] There are 2 fields [BeginDate] and [EndDate]. I want the user to add a beginning date and and end date. I want Access to generate all the dates to include the beginning and end dates and put those dates into a table [tblSchDates].

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,691
    Did you try the code?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    UT227 is offline Competent Performer
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    418
    I'm trying to get it to work. I'm not exactly sure how to use it. I have a command button [btnImportDates]. I changed the table and field names in the code:
    Sub SaveDates(dteStart As Date, dteEnd As Date)
    Do While dteStart <= dteEnd
    CurrentDb.Execute "INSERT INTO tblSchDates(SchDate) VALUES(#" & dteStart & "#)"
    dteStart = dteStart + 1
    Loop
    End Sub

    That's pretty much where I'm at. Do I put it in the sub for my command button?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,691
    Yes, you can put code into button procedure. Something like:
    Code:
    Sub btnImportDates_Click()
    Dim dteStart As Date, dteEnd As Date
    dteStart = Me.tbxStart
    dteEnd = Me.tbxEnd
    
    Do While dteStart <= dteEnd
    CurrentDb.Execute "INSERT INTO tablename(fieldname) VALUES(#" & dteStart & "#)" dteStart = dteStart + 1
    Loop
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    UT227 is offline Competent Performer
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    418
    I put that into my button code. This is the error I got.
    Click image for larger version. 

Name:	Error.jpg 
Views:	5 
Size:	61.8 KB 
ID:	39426

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,691
    It's just example code. Use whatever names you assign to textboxes.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    UT227 is offline Competent Performer
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    418
    OK. Thanks. It's been a long day. It works great.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,691
    I modifed code to show Dim statement.

    All modules should have Option Explicit in header. Can set this to occur automatically for new modules in the VBE > Tools > Options > Editor tab > check Require Variable Declaration

    Will have to manually add to existing modules.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 07-19-2019, 02:17 PM
  2. Replies: 6
    Last Post: 08-24-2018, 04:52 PM
  3. Replies: 7
    Last Post: 09-10-2015, 04:52 PM
  4. Replies: 1
    Last Post: 02-16-2013, 09:11 AM
  5. Query input dates for range of dates in two fields
    By kagoodwin13 in forum Queries
    Replies: 3
    Last Post: 02-13-2012, 04:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums