Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2010
    Posts
    5

    Unanswered: Insert into - combine select and coded field

    I am trying to use an SQL insert into statement to update an Access table with both data from another table and a date that I have calculated in my code. I know how to use the insert into for select and values, but how do I add the coded date (DStart) in the insert statement? Below is part of my code:

    DStart = Me.StartDate
    DEnd = Me.EndDate

    DaysCount = DateDiff("d", DStartHold, DEndHold) + 1
    Do Until DaysCount = 0
    MyWeekDay = Weekday(DStart)
    HoldWeekDayName = WeekdayName(MyWeekDay)
    If HoldWeekDayName = "Saturday" Then
    DStart = DStart + 1
    DaysCount = DaysCount - 2
    Else
    If HoldWeekDayName = "Sunday" Then
    DStart = DStart + 1
    DaysCount = DaysCount - 1
    Else
    dbs.Execute " INSERT INTO tblEmployeesPTODetail ( [EmployeePTOID], [EmployeeID#], [StartDate], [EndDate], PTODate, PTOShift, PTOHours) " _
    & " SELECT [EmployeePTOID], [EmployeeID#], [StartDate], [EndDate], 2 as PTOShift, 8 as PTOHours FROM tblEmployeesPTO and DStart as PTODate " _
    & " WHERE (((tblEmployeesPTO.EmployeePTOID)= " & Forms!frmEmployeesPTO!EmployeePTOID & " ) AND ((tblEmployeesPTO.PTOShift)=2));"

    dbs.Close
    DaysCount = DaysCount - 1
    End If
    End If

    DStart = DStart + 1
    MyWeekDay = Weekday(DStart)
    HoldWeekDayName = WeekdayName(MyWeekDay)
    Loop

    I want the PTODate to = DStart each time it loops. The code works until I try to add DStart. Help please!

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    make it part of your select statement, DStart that is. Also, it has to be in the same order as the columns you described in the INSERT part of the statement
    Dave

  3. #3
    Join Date
    Jul 2010
    Posts
    5

    Tried but did not work

    I tried that with this SQL statement, but got an error message stating "Too few parameters. Expected 1."

    dbs.Execute " INSERT INTO tblEmployeesPTODetail ( [EmployeePTOID], [EmployeeID#], [StartDate], [EndDate], PTODate, PTOShift, PTOHours) " _
    & " SELECT [EmployeePTOID], [EmployeeID#], [StartDate], [EndDate], DStart as PTODate, 2 as PTOShift, 8 as PTOHours FROM tblEmployeesPTO " _
    & " WHERE (((tblEmployeesPTO.EmployeePTOID)= " & Forms!frmEmployeesPTO!EmployeePTOID & " ) AND ((tblEmployeesPTO.PTOShift)=2));"

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    are you sure there was data for that employee id?

  5. #5
    Join Date
    Jul 2010
    Posts
    5
    Positive, since it worked before I added PTODate, since the other fields in the SELECT statement come from another table, but DStart is generated in the code before the INSERT statement. DStart is not located in a table, but rather calculated. See the code.

  6. #6
    Join Date
    Jul 2010
    Posts
    5
    Sorry, replied with last post before I was done writing...

    Positive, since it worked before I added PTODate, since the other fields in the SELECT statement come from another table, but DStart is generated in the code before the INSERT statement. DStart is not located in a table, but rather calculated. See the code below. This code works, but does not contain DStart which is the field I really need.

    dbs.Execute " INSERT INTO tblEmployeesPTODetail ( [EmployeePTOID], [EmployeeID#], [StartDate], [EndDate], PTOShift, PTOHours) " _
    & " SELECT [EmployeePTOID], [EmployeeID#], [StartDate], [EndDate], 2 as PTOShift, 8 as PTOHours FROM tblEmployeesPTO" _
    & " WHERE (((tblEmployeesPTO.EmployeePTOID)= " & Forms!frmEmployeesPTO!EmployeePTOID & " ) AND ((tblEmployeesPTO.PTOShift)=2));"

Posting Permissions

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