Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Still working on the same problem as my earlier post today. Trying to avoid calling the query optimizer so many times in a VBA loop by using a parameter query. Tried two versions of a stored query: one with hard-wired criteria and one with parameters. Double-clicking this query from the database window generates a message that 699 rows will be updated. I cancel the actual updating, leaving the records unchanged.


    UPDATE TimeLine INNER JOIN qryGPSPosSelectedWithoutATIIDfk ON TimeLine.MachineID = qryGPSPosSelectedWithoutATIIDfk.MachineID
    SET qryGPSPosSelectedWithoutATIIDfk.ATIIDfk = timeline.Recnum
    WHERE (((qryGPSPosSelectedWithoutATIIDfk.TimeTag)>=[starttime] And (qryGPSPosSelectedWithoutATIIDfk.TimeTag)<[stoptime])
    AND ((qryGPSPosSelectedWithoutATIIDfk.MachineID)=6)
    AND ((TimeLine.MachineID)=6)
    AND ((TimeLine.ZoneID)=70)
    AND ((Int([TimeLine].[Starttime]))=40247));


    Running this query using the same criteria expressed as parameters generates a message that zero rows will be updated.

    UPDATE TimeLine INNER JOIN qryGPSPosSelectedWithoutATIIDfk ON TimeLine.MachineID = qryGPSPosSelectedWithoutATIIDfk.MachineID
    SET qryGPSPosSelectedWithoutATIIDfk.ATIIDfk = timeline.Recnum
    WHERE (((qryGPSPosSelectedWithoutATIIDfk.TimeTag)>=[starttime] And (qryGPSPosSelectedWithoutATIIDfk.TimeTag)<[stoptime])
    AND ((qryGPSPosSelectedWithoutATIIDfk.MachineID)=[GPSMachineID])
    AND ((TimeLine.MachineID)=[ATIMachineID])
    AND ((TimeLine.ZoneID)=[CurrentZoneID])
    AND ((Int([TimeLine].[Starttime]))=[DayNo]));



    Running this VBA snippet against the same parameter query and with the correct values in the different variables also updates zero records


    Set qdf = db.QueryDefs("qupdGPSPosWithATIIDdynamic") ' this is the same parameter query
    qdf.Parameters("GPSMachineID") = (!MachineID)
    qdf.Parameters("ATIMachineID") = (!MachineID)
    qdf.Parameters("CurrentZoneID") = (!ZoneID)
    qdf.Parameters("DayNo") = (!DayNo)
    qdf.Execute
    debug qdf.recordsaffected


    Can anyone tell me where I am making the mistake, 'cuz I can't see it! Thanks.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Details... The Int(StartTime) function was the culprit. Worked OK with the hardwired version, but failed with the parameter version. Changing it to CLng made it work.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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