Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2010

    Unanswered: Insert code from loop into SQL insert into statement

    I tried to get a response on this before, but never got a good answer. Need a really smart SQL programmer. Thanks!

    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
    If HoldWeekDayName = "Sunday" Then
    DStart = DStart + 1
    DaysCount = DaysCount - 1
    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));"

    DaysCount = DaysCount - 1
    End If
    End If

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

    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
    Nov 2004
    Provided Answers: 4
    FYI, this is the SQL Server forum, not the Access forum.

    Anyway, the dbs.Execute gets a SQL string. Write the SQL INSERT command as Access wants it to be, with the date of DStart spelled out, like in "2010-08-12". Then do whatever you need to convert DStart to that string format in the dbs.Execute string.

    You will get something like:
    dbs.Execute "INSERT INTO tblEmployeesPTODetail ......" _&
    " WHERE (((tblEmployeesPTO.EmployeePTOID)= " & Forms!frmEmployeesPTO!EmployeePTOID & " ) AND ((tblEmployeesPTO.PTOShift)=2)) AND PTODate = " & date2string(DStart) & " ;"

    I don't know Access that good, so I used the imaginary date2string() function to convert the value of DStart to a string format that Access can handle.

    Debug tip:
    Declare a string variable that you use to hold the INSERT SQL. Print it in a MsgBOx and fiddle with date2string(DStart) until the result looks right, only then submit it to dbs.Execute.
    With kind regards . . . . . SQL Server 2000/2005/2012

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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