Results 1 to 3 of 3

Thread: RTE 2001 (A2K)

  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    RTE 2001 (A2K)

    Hi All,
    I am running this SQL string to determine the recordsource for my form. I am trying to use a couple of variables, "strloginid" and "start_date", for the filtering of the records. Using these variables I am getting a "RTE 2001, you canceled the previous operation". I cannot tell what is causing this. I can substitute another value that it prompts for when the form opens and it works fine. For instance, I sub in [login] instead of strloginid and [start date] instead of start_date. Any ideas?

    Me.RecordSource = "SELECT timesheet.TimeID, timesheet.Date, Format([date],""ddd"") " _
    & "AS [Day], timesheet.Wo_ID, timesheet.Tech_ID, timesheet.reg_hours, " _
    & "timesheet.OT_hours, timesheet.Premium_hours, timesheet.travel_hours, " _
    & "timesheet.office_hours, timesheet.Vacation_hours, timesheet.Holiday_hours, " _
    & "Resources.Tech_Name_Last, Resources.Tech_Name_First, Format([date],""ww"") " _
    & "AS WorkWeek, timesheet.on_call_hours, ([reg_hours]+[ot_hours]+[premium_hours]+ " _
    & "<travel_hours>+[office_hours]+[vacation_hours]+[holiday_hours]+[on_call_hours]+<travel_adjustment>) " _
    & "AS Daily_calc, timesheet.holiday, timesheet.travel_adjustment, timesheet.tech_id FROM timesheet " _
    & "INNER JOIN Resources ON timesheet.Tech_ID = Resources.Tech_id WHERE " _
    & "(((timesheet.Date) >= '" & start_date & "' And (timesheet.Date) " _
    & "< DateAdd(""y"", 7, '" & start_date & "')) And ((timesheet.tech_id) = " _
    & " '" & strloginid & "')) ORDER BY timesheet.Date;"


    Thanks,
    Mark

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RTE 2001 (A2K)

    Hi Mark,

    A couple of things that I spot...

    Two lines from the bottom there's - you'll probably want to surround the date with Pound signs (#) instead of Apostrophes
    (& "(((timesheet.Date) >=<font color=red>#</font color=red>'" & start_date & "<font color=red>#</font color=red> And (timesheet.Date) " _)

    Another thought - could you build most of the busy work of this string into a Query, then pass a few parameters to the query rather than having to write such a heavy SQL string?

    Hope this helps

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RTE 2001 (A2K)

    Thanks Mark,
    I will try out the # sign thing. One thing I noticed doing it the other way was that I couldn't get a good value in code. For instance, I would open and immediate window and "print start_date". it would return "12:00:00AM" even though I sent it 15-Sep-2002.

    Thanks,
    Mark

Posting Permissions

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