Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert individual dates into table (Access 2000)

    I have a campground reservations databases containing these tables:
    table1 is named permits: fields are: permit_number, camper_id, date_arrival, date_departure
    primary key is permit_number,camper_id. Permit_number and camper_id are text fields.
    Date_arrival and Date_departure are date fields with an input mask of: 99/99/0000;0;_

    table2 is named nights_stayed: fields are: permit_number,camper_id,night_of_stay
    primary key is permit_number,camper_id,night_of_stay
    Night_of_stay is a date field with an input mask of: 99/99/0000;0;_

    I have a form (named "permits", with a subform named "nights_stayed subform". I have an After Update procedure on the permits form to enter in each individual night_of_stay, based on the arrival date and date departure. I would like to automatically update the nights_stayed table so each individual date the person is staying is stored in this table. I would also like to view the subform with the dates once they are added, so I'd like to keep my subform.
    My syntax is not working. I've tried several things, and lately I'm getting a parameter box and it's showing the camper_id I've just entered.
    Any help would be much appreciated!!!

    Private Sub Form_AfterUpdate()
    Dim startdate as Date
    Dim enddate as Date
    enddate = Forms![permits]![date_departure]
    enddate = DateAdd("d", -1, enddate)
    startdate - Forms![permits]![date_arrival]

    Do Until startdate > enddate
    DoCmd.RunSQL "Insert into nights_stayed (permit_number, " _
    & "camper_id, night_of_stay) Values (" _
    & permit_number & ", " _
    & camper_id & ", " _
    & "'" & startdate & "' " & ")"
    Me.Requery
    startdate = DateAdd("d", 1, startdate)
    Loop
    DoCmd.Close
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert individual dates into table (Access 2000)

    Since camper_id and permit_number are text fields, you should put single quotes around them, and since startdate is a date variable, you should *not* put quotes around it.

    Also, you don't need to requery the form every pass through the loop, and I don't understand why you close the form at the end of the procedure. You don't need to specifiy Forms![permits] if the code is for permits itself.

    Here is a modified version:<pre>Private Sub Form_AfterUpdate()
    Dim StartDate As Date
    Dim EndDate As Date
    Dim d As Date

    ' Get start and end date from form
    StartDate = [date_arrival]
    EndDate = [date_departure] - 1
    ' Loop through dates
    For d = StartDate To EndDate
    ' Run update query
    DoCmd.RunSQL "Insert into nights_stayed " & _
    "(permit_number, camper_id, night_of_stay) " & _
    "Values ('" & [permit_number] & "', '" & _
    [camper_id] & "', " & d & ")"
    Next d

    Me.Requery
    End Sub</pre>

    Post back if you still have problems.

    Note: the code would be more efficient if you used DAO or ADO to insert records.

  3. #3
    New Lounger
    Join Date
    Apr 2002
    Location
    USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert individual dates into table (Access 2000)

    I tried what you suggested and it's inserting records, but instead of inserting a date, it's inserting a time.
    i.e. 12:01:48 AM
    12:03:36 AM
    Can you tell me how to fix this?
    Also..I thought I should be putting this into an ADO or DAO but I wasn't sure what the difference was between the two.
    Thank you for your help! This has been driving me crazy!

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Insert individual dates into table (Access 2000)

    Are you entering dates as just the month and date? If so that may be part of the issue, but it sounds as if your Start Date and Stop Date are being treated as decimal values that are both are zero for the integer part. What you are doing should be just fine, as you are running a query, which is usually just as efficient as doing things with DAO or ADO. And there is a substantial learning curve with either. Step through your code and inspect what the start, stop and d dates look like.
    Wendell

  5. #5
    New Lounger
    Join Date
    Apr 2002
    Location
    USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert individual dates into table (Access 2000)

    I'm really sorry. I feel like such a dufus. I'm still having problems figuring this out. I have all date fields set at both the form and table levels as a format of 'short date' and an input mask of 99/99/0000;0;_ The decimal places are set as Auto. Now I'm getting the infamous 12/30/1899 inserted. Enclosed is a scaled down version of the database. I've only kept 1 or 2 records in and deleted queries, reports, etc. If you could look at it and tell me what I'm doing wrong, I'd be really appreciative.
    Attached Files Attached Files

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Insert individual dates into table (Access 2000)

    Hi Donna,
    Not to worry - we all feel that way sometimes. It turns out to be a fairly simple fix - you really just needed to put "#" characters around the date in your Insert query. I changed the code to work with a string so I could see the SQL that was being run, but the pound or sharp change around the date was all that was really needed. So code now is:
    <font color=blue><font face="Georgia">
    Private Sub Form_AfterUpdate()
    Dim startdate As Date
    Dim enddate As Date
    Dim d As Date
    Dim TSQL As String
    startdate = [date_arrival]
    enddate = [date_departure] - 1
    For d = startdate To enddate
    TSQL = "Insert into nights_stayed " & _
    "(permit_number, camper_id, night_of_stay) " & _
    "Values ('" & [permit_number] & "', '" & _
    [camper_id] & "', #" & d & "#)"
    DoCmd.RunSQL TSQL
    Next d
    Me.Requery
    End Sub
    </font face=georgia></font color=blue>
    I'll take a bit longer look at your database to see if I see anything else that looks worrisome. Post back if this doesn't work correctly.
    Wendell

  7. #7
    New Lounger
    Join Date
    Apr 2002
    Location
    USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert individual dates into table (Access 2000)

    This works GREAT!!!!!!!!!!!!!! You can't imagine how much I appreciate this!!
    Thanks again!!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert individual dates into table (Access 2000)

    Hi Donna,

    Wendell already solved the problem for you. I would like to add that if your database is ever going to be used on a system with non-US date settings, you'll need to convert the dates to US format, because that's what SQL expects:<pre>... #" & Format(d, "mm/dd/yyyy") & "# ...</pre>

    I could make several other remarks about your form, but I'll limit myself to one: as it is now, the user can select an item from the Site combo box that is not allowed for the selected Lake. In the attached database, I have added a bit of code to the AfterUpdate event of the Lake combo box to make the Site combo box display only the site numbers for the selected lake.
    Attached Files Attached Files

  9. #9
    New Lounger
    Join Date
    Apr 2002
    Location
    USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert individual dates into table (Access 2000)

    Thanks Hans! I wasn't sure how to write the code to call up only those sites associated with each lake, so this is good.
    I have in the past (on other databases) written a query and used that as a row source. I like this better.

Posting Permissions

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