Line number or label or statement or end of statement ошибка

I want to use Excel VBA to set up Task Reminders in Outlook, so I found this code from here:
http://www.jpsoftwaretech.com/using-excel-vba-to-set-up-task-reminders-in-outlook/

Dim bWeStartedOutlook As Boolean

Function AddToTasks(strDate As String, strText As String, DaysOut As Integer) As Boolean
=AddToTasks(B2, M2 Time, 120)



' Adds a task reminder to Outlook Tasks a specific number of days before the date specified
' Returns TRUE if successful
' Will not trigger OMG because no protected properties are accessed
' by Jimmy Pena, http://www.jpsoftwaretech.com, 10/30/2008
'
' Usage:
' =AddToTasks("12/31/2008", "Something to remember", 30)
' or:
' =AddToTasks(A1, A2, A3)
' where A1 contains valid date, A2 contains task information, A3 contains number of days before A1 date to trigger task reminder
'
' can also be used in VBA :
'If AddToTasks("12/31/2008", "Christmas shopping", 30) Then
'  MsgBox "ok!"
'End If
 
Dim intDaysBack As Integer
Dim dteDate As Date
Dim olApp As Object ' Outlook.Application
Dim objTask As Object ' Outlook.TaskItem
 
' make sure all fields were filled in
If (Not IsDate(strDate)) Or (strText = "") Or (DaysOut <= 0) Then
  AddToTasks = False
  GoTo ExitProc
End If
 
' We want the task reminder a certain number of days BEFORE the due date
' ex: if DaysOut = 120, then we want the due date to be -120 before the date specified
' we need to pass -120 to the NextBusinessDay function, so to go from 120 to -120,
' we subtract double the number (240) from the number provided (120).
' 120 - (120 * 2); 120 - 240 = -120
 
intDaysBack = DaysOut - (DaysOut * 2)
 
dteDate = NextBusinessDay(CDate(strDate), intDaysBack)
 
On Error Resume Next
  Set olApp = GetOutlookApp
On Error GoTo 0
 
If Not olApp Is Nothing Then
  Set objTask = olApp.CreateItem(3)  ' task item
 
  With objTask
    .StartDate = dteDate
    .Subject = strText & ", due on: " & strDate
    .ReminderSet = True
    .Save
  End With
 
Else
  AddToTasks = False
  GoTo ExitProc
End If
 
' if we got this far, it must have worked
AddToTasks = True
 
ExitProc:
If bWeStartedOutlook Then
  olApp.Quit
End If
Set olApp = Nothing
Set objTask = Nothing
End Function
 
Function GetOutlookApp() As Object
 
On Error Resume Next
  Set GetOutlookApp = GetObject(, "Outlook.Application")
  If Err.Number <> 0 Then
    Set GetOutlookApp = CreateObject("Outlook.Application")
    bWeStartedOutlook = True
  End If
On Error GoTo 0
 
End Function
Sub Test()

My question is, if I have all the data in the spreadsheet, and I initiallize AddToTasks like so:
=AddToTasks(A1, A2, 120)

Why does it come up with that error?

1. Compile Error: Expected: line number or label or…
   

   Good afternoon all, I’m running into this error and can’t for the life of me figure out how to fix it.

   Compile Error: Expected: line number or label or statement or end of statement

   This is the code I am using (its in an if statement if that helps).

   Code:

   "Update tblLogTemp set LogOffDate(Sql1) = LOD(sql2), LogOffTime = LOT(sql2)" &_
   "where (SELECT top 1 dbo_Logoffs.Operdate as LOD, dbo_Logoffs.opertime as LOT" &_
   "FROM dbo_Logoffs INNER JOIN tblLogTemp ON dbo_Logoffs.FullName = tblLogTemp.User" &_
   "Where dbo_Logoffs.OperDate >= tblLogTemp.LogOnDate and dbo_LogOffs.OperTime >= tblLogTemp.LogOffTime);"

   Any ideas?

   ---

2. immediately I see 2 things:

   • replace «&_» with «& _»
   • at the end of each line, add a space before you close the statement portion with a double quote:

   that’s the cause of the error

   ---

3. I did both of those things (i tried the & _ as opposed to the &_ previously but forgot to change it back), but the space before the » I didn’t realize mattered.

   But I am getting the same error still.

   ---

4. what’s the entire code? and what line errors out? by default, it should highlight one in yellow. If it doesn’t, go into the options and push the radio button that says «break on all errors».

   looking at the statement itself, I’ve never seen anything like it. what are you doing exactly? It looks like you’re trying to update a function WITH a function??

   And why is there no comparison value on the right side of your first WHERE clause?

   I’m having a heck of a time reading and interpreting this sql. I might not be the best person to help you here…

   ---

5. I don’t use VBA often (in all honesty I haven’t written anything serious in the access VBA in 6 years so my VBA knowledge, at best, can be considered rusty. At worst it can be considered beginner level) so I am kind of grasping at straws with what i’m trying to do..

   Quick breakdown of what I am trying to do.

   I need to pull Logon Dates/TImes from Table1, and Logoff dates/times from Table2. There is no direct corellation between the 2 so basically what i’m doing in order to create a report listing them both is using a temp table to house all 4 dates/times and create a report based on it.

   So Sql1 fills the temp table with user, logon date, logontime. And the Sql i’m attempting to write is (theoretically) going to update that table with the logoff date, and logofftime.

   So in theory

   if recordset.eof = false
   then Update TempTable set Logoffdate = Table2.logoffdate, logofftime = table2.logofftime where temptable.user = table2.user, logoffdate >= date and <= date

   Since there is no direct link between the records basically what I am having to do is select the first matching record (Select Top 1 assuming it exists) where the dates kind of match up.

   The date field houses the date, but the time field is formatted incorrectly and houses the date and time both (unfortunately the date it shows though is the default date which is 1/1/1900. Realistically the date/time should just be formatted into 1 field, but whoever designed it set it up in 2 different fields with the wrong formats so its causing me a terrible terrible headache.)

   Sorry about this wall of text. Maybe I’m just overcomplicating it?

   *edit* the comparison value after the first ‘where’ clause should be covered with the select statement inside the parethesis unless i am mistaken.

   ---

6. I think you are overcomplicating, actually.

   I might have a better suggestion. can you post screenshots of both tables’ structures?

   you said there’s no correlation between the two? WHY are you doing it? preparing data for periodic paychecks for staff? I would assume with something like that, the consolidation of the data would have to be correlated somehow, like one record per punch in, punch out?

   regardless, post those tables and let us have a look. post ss’s of both the originals, as well as what you’re new consolidated one is supposed to look like.

   ---

7. I’m doing it because the owner of the company I work for wants a report to show the hours of the salary employees are logged in as a ‘check’ to see if they’re coming in or not. The report will be unreliable, inaccurate, and the dates will be assumed at best, but he doesn’t care he wants to see it.

   The reason I say there is no correlation is because the user can login multiple times without having logged out once. The record input is based on login and logoff scripts. If neither is run then it won’t record a new record.

   Here is the layout of the tables. Both tables have the same layout.

   ID as Autonumber (PK)
   OperDate as Date/Time
   Opertime as Date/Time
   FullName as Text
   Host as Text
   IP as Text
   Asset as Text
   Oper as Text

   Now there is no direct relation between the 2 tables, but you can ‘assume’ that the host and ip will be the same for login/logoff’s but you will run into cases where the dates don’t match up (for instance on one example there were 24 logins, and 16 logoffs).

   Which is why i’m running the query to pull what I would consider an ‘assumed’ match. To pull the match it’s a simple select query that just goes ‘select top 1 date, time from logoffs where logoff.date >= logondate and user = user). The top 1 is just to it pulls the first possible match as opposed to 4 possible matches and updates itself repeatedly.

   ---

8. well first of all sir, you didn’t post any screenshots, which is what I asked about.

   is that not possible? simply copy the tables, delete all the data and print the screen. that would help, but the explanation works fine I suppose. But what about the 3rd table? I personally still can’t my head around it.

   how about posting the script you’re using to do this with? You said it was in the middle of an »if» statement. post it please.

   If you’re wanting to execute UPDATE statements everytime you go through a loop, I would be more inclined to tell you to open 2 recordsets and write values from one to the other, simply because it probably uses less memory stacking and processor resource. If too much resource is requested from a machine too fast, strange things can come out of it, and sometimes it’s a computer crash. As a matter of fact, I’ve had this happen to me with vba scripts and I’ve gotten back garbage that didn’t make any sense and wasn’t even related to the output I requested via the script.

   ---

9. Hi Rabozza,

   maybe try to start a total different approach: why not make a union query combining the logins and logouts in 1 dataset. Something like:

   Code:

   SELECT tblLogin.Host, tblLogin.IP, tblLogin.FullName, tblLogin.OperDate, tblLogin.OperTime, "Login" as flfType
   FROM tblLogin
   UNION
   SELECT tblLogout.Host, tblLogout.IP, tblLogout.FullName, tblLogout.OperDate, tblLogout.OperTime,  "Logout" as flfType
   FROM tblLogout
   ORDER BY  tblLogin.OperDate, tblLogin.OperTime, tblLogin.Host, tblLogin.IP, tblLogin.FullName;

   The sorting can be different, according to your needs of course. Then you can use this dataset as a base for a report (maybe use a crosstab?)

   succes
   NG

   ---