Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2011
    Location
    Baltimore, MD
    Posts
    22

    Unanswered: Datediff in update query

    Hi,

    I'm trying to run a datediff code in an UPDATE query. When I run it in a SELECT query it works perfectly, but when I try and do it in an update query, the datediff function isn't working. Any suggestions?

    SELECT ([tblservice].[units])/(DateDiff("m",[tblService].[StartDate],[tblService].[EndDate])+1)*(DateDiff("m",[tblservice].[StartDate],[tblCases].[Discharge Date])+1) AS Units, [Forms]![frmcases].[Discharge Date] AS EndDate, ([tblservice].[cost])/([tblservice].[Units])*(DateDiff("m",[tblservice].[StartDate],[tblCases].[Discharge Date])+1) AS Cost
    FROM tblCases INNER JOIN tblService ON tblCases.ClientID = tblService.ClientID
    WHERE (((tblService.ClientID)=664) AND ((tblService.vendorid)=3) AND ((tblService.IsIncurred)=No));


    UPDATE tblService SET tblService.units = ([tblservice].[units])/(DateDiff("m",[tblService].[StartDate],[tblService].[EndDate])+1)*(DateDiff("m",[tblservice].[StartDate],[tblCases].[Discharge Date])+1), tblService.EndDate = [Forms]![frmcases].[Discharge Date], tblService.Cost = ([tblservice].[cost])/([tblservice].[Units])*(DateDiff("m",[tblservice].[StartDate],[tblCases].[Discharge Date])+1)
    WHERE (((tblService.ClientID)=664) AND ((tblService.vendorid)=3) AND ((tblService.IsIncurred)=No));

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    the datediff function isn't working
    This statement covers a multitude of sins. Does the application return an error message? Does it update the table with the wrong information? Does your PC turn into a flock of doves and fly around the room?

    I suspect that the problem occurs because you haven't joined tblCases to tblService in the UPDATE statement. This would give you a Cartesian product of a returned recordset, which means that Jet wouldn't know which values to use to update a given record.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Nov 2011
    Location
    Baltimore, MD
    Posts
    22
    Quote Originally Posted by weejas View Post
    I suspect that the problem occurs because you haven't joined tblCases to tblService in the UPDATE statement.
    I have tried that I it didn't do anything. I'm actually not getting any error message. The query is running but coming up with the wrong values.
    However, I have thought of a different way. I'm using my SELECT query and making it into a table query and then updating those values into tblService.
    But something if off with this code also. Could you help with this? Thanks!

    A = "UPDATE tblService SET tblService.Units = "
    B = " , ((tblService.Enddate) = #"
    C = "#) , tblservice.Cost = "
    D = " WHERE(((tblService.ClientID)="
    E = ") AND((tblService.VendorID)="
    F = ") AND((tblService.IsIncurred)="
    G = ")"

    strSQL = A & [tblUpdateCost].[units] & B & [tblUpdateCost].[Discharge Date] & C & [tblUpdateCost].[Cost] & D & [tblUpdateCost].[ClientID] & E & [tblUpdateCost].[VendorID] & F & [tblUpdateCost].[IsIncurred] & G

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how does the the update query know anythign about the tblcases.[DischargeDate]?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    At a glance, I can count seven opening parentheses in your WHERE clause, and only four closing parentheses. That will break the Jet engine. Strip them out - they're not really needed in this case.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Nov 2011
    Location
    Baltimore, MD
    Posts
    22
    Quote Originally Posted by healdem View Post
    how does the the update query know anythign about the tblcases.[DischargeDate]?
    table.cases is open when I run this query

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    That won't tell Jet which record in tblCases relates to which record in tblService, though.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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