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));"
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.
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