Page 1 of 2 12 LastLast
Results 1 to 40 of 44

Thread: [RESOLVED] trying to update a Date field based on ID dosnt work proper

  1. #1

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Resolved [RESOLVED] trying to update a Date field based on ID dosnt work proper

    i have a table that is called ZHistory
    now i am trying to check a field if its empty after midnight and update it according to the ID
    i know there is CDate and dateadd but dont know what better method to use for this
    i tried a code but some how it updates all the time the field
    need some help with this query
    this is my code
    Code:
        Dim DateToday As Date
        Dim StrID As String
        Dim StrDate As Date
        Dim S As String
        DateToday = CDate(Date & " 00:01")
    Dim Rs As New ADODB.Recordset
    Rs.Open "SELECT Top 1 * FROM ZHistory Where ZDateStart = #" & Format(DateToday) & "#  ORDER BY     ID Desc", CN
              If Not Rs.EOF Then
                StrID = Rs!ID
                StrDate = Rs!ZDateStart
            
                S = "Update ZHistory Set ZDateEnd = #" & StrDate & "# Where ID = " & StrID
                CN.Execute S
       
     Else
    
            End If
    any help will be appreciated
    salsa

  2. #2
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: trying to update a Date field based on ID dosnt work proper

    ?
    When are you running this (time of day)?
    Why are you using "Top 1 *" and "order by"? You do not LOOP through your RS, so no need of order by.
    VERY Confusing query.
    Are you saying, just guessing here, that EVERY DAY you run this code, and you want to know if there is no END DATE for any record that has a startdate of the day it is run?
    any help would be appreciated to understand your situation...and, please, don't simply repeat what you said, RE-SAY it in a better explanation, from start to finish.

  3. #3

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: trying to update a Date field based on ID dosnt work proper

    i am saying that every day i open the cash register lets say 10/02/2015 08:00 with number ID 1
    now , everyday i need to close the cashregister
    my question is if i forget to close the cash register how do i update automatic Before 00:00 the ZDateEnd According to the number ID?
    if the ZDateEnd is null for the same date until the hour 23:59 then i will update the same field with the date and hour

  4. #4
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: trying to update a Date field based on ID dosnt work proper

    at midnight, if ZEndDate = "" or ISNULL(ZEnddate) then update ZHistory set ZEndDate = Date + 1 where ZStartDate = Date

    This is pseudo code, so you'll have to format your query appropriately...this will update all rows that have a startdate of the day it is run, with tomorrow's date being placed in ZEndDate

    This means (that solution) that your program is going to have to be running and constantly checking the clock....probably not a good idea, and I doubt you have workers at 11:59. So, maybe a better option is to update the endDates at program start before the cashdrawer is open. ???

  5. #5

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: trying to update a Date field based on ID dosnt work proper

    at midnight, if ZEndDate = "" or ISNULL(ZEnddate) then update ZHistory set ZEndDate = Date + 1 where ZStartDate = Date
    yes amigo
    maybe a better option is to update the endDates at program start before the cashdrawer is open. ??
    yes that is a good idea also
    how do i accomplish this method sami?

  6. #6
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: trying to update a Date field based on ID dosnt work proper

    in form load call a routine to insert tomorrow's date in any record where ZEndDate does not exist.
    Pretty easy query.

    pseudo....update ZHistory set ZendDate = Date where ZEndDate is NULL or ZEndDate = ""

    That way, EVERY day the program is started, all cashdrawers will be closed.

    If the program is run more than once a day, however (as it may be if it crashes or power goes out or whatever), then create a boolean and put it into a database, a cfg file or an ini file and check it at program start.

  7. #7

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: trying to update a Date field based on ID dosnt work proper

    can you help me fix the query?

  8. #8
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: trying to update a Date field based on ID dosnt work proper

    Was thinking (dangerous, I know, but I was) that every time your program goes down (power outage, user action, etc) you probably want that cash drawer to close. For user action shut down, it is easy to do do so and then update the ZEndDate. Then, when the system is restarted, and the drawer is open (and a ZStartDate is re-established), then no problem (as long as you also have a new (blank) zEndDate).
    If power goes out, not sure how to handle that except one would have to manually close that cash drawer if it was open. Then, at project start, determine the status and if open, close it, otherwise just restart.
    NEVER used a cash drawer, so know absolutely nothing about how to code to open/close, but I suspect you already do. So, I guess, if I am assuming correctly, you have to know if the drawer is open or not, so why are you using dates instead of some boolean value in your database...true for closed, false for open, for example? Then instead of messing with dates at all, just close it if it is open at shutdown and/or check at startup?????
    Maybe I SHOULDN'T be thinking at all......

  9. #9
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: trying to update a Date field based on ID dosnt work proper

    Quote Originally Posted by salsa31 View Post
    can you help me fix the query?
    No. YOU do it....gotta be pretty simple. Or wait for DataMiser who may have the time to do so. Busy day.

  10. #10
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: trying to update a Date field based on ID dosnt work proper

    Quote Originally Posted by SamOscarBrown View Post
    ?
    Why are you using "Top 1 *" and "order by"?
    That's actually quite common (and clever... many of Salsas posts contain such a mix of
    "clever parts" - paired with a seeming "lack of understanding" - mostly happening in
    the discussion which evolves around his original question) - makes me think sometimes,
    whether Salsa is actually "two persons" or something...

    In his original posting, the construct ensures, that the one record with the highest ID
    (IDs are given in increasing order) of a given Day is returned from the Select.

    Which is corrrect to this point (e.g. when somebody forgot to fill in a "Case-Closed"-
    Field for a given day, then it's with high probability manifested in the last record of this
    day (which is the record with the highest Auto-ID on this day).

    Olaf
    Last edited by Schmidt; Feb 11th, 2015 at 12:58 PM.

  11. #11

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: trying to update a Date field based on ID dosnt work proper

    lol Olaf

    what is wrong with the query?
    what is missing?

  12. #12
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: trying to update a Date field based on ID dosnt work proper

    You say you are trying to check if the field is empty but the code you posted does not check to see if the field is empty. It is not that the code is not working properly, it isn't coded properly. If you want to select and/or update only the record that has a empty value then you need to add some code to check that the value is empty.

    You also do not need that much code, a single update statement with the proper where clause would do the trick.

  13. #13
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: trying to update a Date field based on ID dosnt work proper

    Quote Originally Posted by DataMiser View Post
    You say you are trying to check if the field is empty but the code you posted does not check to see if the field is empty. It is not that the code is not working properly, it isn't coded properly. If you want to select and/or update only the record that has a empty value then you need to add some code to check that the value is empty.

    You also do not need that much code, a single update statement with the proper where clause would do the trick.
    Yep - and IMO Sam already gave (although being marked by him as Pseudo-Code)
    a quite well-matching solution (in his post #6):

    Cnn.Execute " Update ZHistory Set ZEndDate = Date where ZEndDate Is NULL OR ZEndDate='' "

    This would "close" *all* the ZEndDate-Fields which are currently "Empty" in ZHistory -
    but when run daily (early morning or at "end of the work-day", then Sams construct
    would ensure proper "daily cleanup" I guess...

    Olaf

  14. #14

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: trying to update a Date field based on ID dosnt work proper

    hey sir
    i need all that code because i need to update the same ZDateEnd as DStartDate
    what is wrong /missing in the query?
    i know i wrote it right with out any errors
    explain and teach me so i will fix my mistake
    i just want to check if the ZDateEnd is null after 00:00 and then update the same field with todays date and time

  15. #15
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: trying to update a Date field based on ID dosnt work proper

    Quote Originally Posted by salsa31 View Post
    ...i just want to check if the ZDateEnd is null
    That's what Sams Update-Construct does in the first part of the Where-Clause
    (the OR combined second part of Sams Where-clause could also be omitted IMO,
    it's just there to "really make sure, in case the Field was defined as Text-Field or something")

    Quote Originally Posted by salsa31 View Post
    after 00:00 and then update the same field with todays date and time
    And with this part you really only describe, *when* you plan to run this Update-Query.

    If you plan to run it today (or better, tomorrow - shortly after midnight), e.g. at #2015-02-12 00:00:22#
    then the part I marked Blue in Sams query:

    Cnn.Execute " Update ZHistory Set ZDateEnd = Date Where ZDateEnd Is NULL OR ZDateEnd='' "

    Will ensure, that your (empty or Null) ZDateEnd-Field will be set to #2015-02-12#.

    If you want it, including the exact time as well (00:00:22) when you run it, then you could change it to:

    Cnn.Execute " Update ZHistory Set ZDateEnd= Now Where ZDateEnd Is NULL OR ZDateEnd='' "

    Olaf

    Edit: Replaced the (formerly) wrongly named ZEndDate with: ZDateEnd to avoid confusion on your end...
    Last edited by Schmidt; Feb 11th, 2015 at 01:36 PM.

  16. #16

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: trying to update a Date field based on ID dosnt work proper

    hey olaf
    the update isnt the problem
    the select is
    Code:
    DateToday = CDate(Date & " 00:01")
    Rs.Open "SELECT Top 1 * FROM ZHistory Where ZDateStart = #" & DateToday & "# order by ID desc", CN
    If Not Rs.EOF Then....
    i dont want to check right away if its null and then update
    when a user comes to work he needs to open the cash register by inserting the values of today
    e.x today is 10/02/2015 09:30
    now if the user forgets to close the cash register which is an update query i already did then
    the ZDateEnd will be updated with 10/02/2015 23:59
    i have to keep the format of the date same as the ZDateStart
    get it amigo?

  17. #17
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: trying to update a Date field based on ID dosnt work proper

    Quote Originally Posted by salsa31 View Post
    when a user comes to work he needs to open the cash register by inserting the values of today
    e.x today is 10/02/2015 09:30
    now if the user forgets to close the cash register which is an update query i already did then
    the ZDateEnd will be updated with 10/02/2015 23:59
    i have to keep the format of the date same as the ZDateStart
    get it amigo?
    No, I still don't understand fully, since you formulated in an ambiguous way...
    All I can do is *guess*, that what you're after:
    - is to update a potential Null-Value in ZDateEnd
    - at any given time you choose
    - with a Date-Value which equals the same *day* as ZDateStart in the same record
    - though appended with a time-part which equals 23:59:00

    And you can do this (still with a single line of SQL-code), but changed to:

    Cnn.Execute "Update ZHistory Set ZDateEnd = Int(ZDateStart)+0.9993 Where ZDateEnd Is NULL"

    Please ask, when you don't understand what the above code is doing in that Update-Query
    which is not using any String-Concatenations.

    Olaf

  18. #18

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: trying to update a Date field based on ID dosnt work proper

    No, I still don't understand fully, since you formulated in an ambiguous way...
    let me put it this way
    if ZdateStart is equal of the date today and the ZEndDate is still null after midnight then make the update for ZEndDate

  19. #19

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: trying to update a Date field based on ID dosnt work proper

    olaf like i said
    the update is not the problem
    the select is

  20. #20
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: trying to update a Date field based on ID dosnt work proper

    Salsa, Like I said and Olaf showed you a simple update is all that is needed.

    Your select did not have anything in the where clause to check that the field was empty and as such would just return 1 entry from the db no matter if it was empty or not and then your update would update that record whether it was supposed to or not.

    You do not need to select anything
    You need to do an update with the proper where clause and that where clause needs to include code to check and see if the field is empty. This would correct not only todays record but any record that was missing the close time. If you run it everyday then there would never be more than one that needed fixed but if there were it would fix all of them.

  21. #21

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: trying to update a Date field based on ID dosnt work proper

    i dont know how to do it sir sry i realy dont

    i dont understand how can i update something before i select something to check before i update it

  22. #22
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: trying to update a Date field based on ID dosnt work proper

    It is called a where clause, you've already been shown how to do it in this thread.

  23. #23
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: trying to update a Date field based on ID dosnt work proper

    You keep saying you want to check it after midnight....I say you do NOT. IF you REALLY want to go that way, you need your UPDATE query in a timer that checks for midnight....that would, to me, be 'dumb'.
    I still don't know if your program is on 24X7 (all the time) or that it runs sometime in the normal workday (which I would assume might run from say, 0700 to 1800). That update query I provided WILL update your enddate (all enddates) that are null (blank?) if the code is run on the startdate.

    It's really quite simple, run that code (refined by Olaf---"Update ZHistory Set ZDateEnd = Int(ZDateStart)+0.9993 Where ZDateEnd Is NULL") and see if it does not do what you want....No SELECT, no return of an RS, nothing...but an update query.

  24. #24

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: trying to update a Date field based on ID dosnt work proper

    i know that sit but it still doesn't suit my purpose
    Code:
        DateToday = CDate(Date & " 00:01")
        Dim Rs As New ADODB.Recordset
        Rs.Open "SELECT Top 1 * FROM ZHistory order by ID desc", CN
    
        StrID = Rs!ID
        StrDate = Format(Rs!ZDateStart, "MM/DD/YYYY")
    
        S = "Update ZHistory Set ZDateEnd = #" & Format(Rs!ZDateStart, "mm/dd/yyyy") & "# Where ID = " & StrID
        CN.Execute S

  25. #25

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: trying to update a Date field based on ID dosnt work proper

    i need the select to hold the number ID
    thats all

  26. #26
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: trying to update a Date field based on ID dosnt work proper

    Do you understand what this query will do?
    Code:
    Update ZHistory Set ZDateEnd = Int(ZDateStart)+0.9993 Where ZDateEnd Is NULL
    Do you understand what this is not doing
    Code:
    Rs.Open "SELECT Top 1 * FROM ZHistory order by ID desc", CN
    Either the update query shown will do what you want or you have told us incorrectly what you want.
    Your select will not do what you want because you did not add the proper where clause to it. I have no idea why you can't seem to understand that you have to use the proper criteria in a where clause to get the results you want. If you want only the record that had an empty entry then you have to put that in the where clause. Yours will simply return the record with the highest ID no matter what data it contains and that is not what you say you want.

  27. #27
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: trying to update a Date field based on ID dosnt work proper

    Quote Originally Posted by salsa31 View Post
    i need the select to hold the number ID
    thats all
    So are you doing something with this ID that you are not showing us or telling us about?

    For the task you describe and the code you show the select is not needed at all, the ID is not needed to be known.
    What needs to be known is if the field is empty and you are not testing that.

    So in other words you are testing the wrong thing, you are returning data that is not needed and you maybe updating records that may not need updating

  28. #28
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: trying to update a Date field based on ID dosnt work proper

    Quote Originally Posted by salsa31 View Post
    i dont know how to do it sir sry i realy dont

    i dont understand how can i update something before i select something to check before i update it

    What you call "a check" (e.g. for a certain record - or a certain amount of records) -
    is (in SQL) expressed over the Where-Clause (which sets the Filter-condition(s))...

    And such a Where-Clause is available:
    - when you perform a Select (usually combined with retrieving a Recordset) ...

    ...but the same (filtering) Where-Clause is *also* available when you perform
    something (in Write-Direction) against a DB-Table (then over a Cnn.Execute) in:
    - Update SQL-Statements ... as well as in
    - Delete SQL-Statements

    You perhaps have used these Update- or Delete-Statements so far only against
    "unmistakably identified Single-Records" (where you gave an AutoID=SomeNumber) in the Where-Clause...

    as in e.g.:
    Cnn.Execute "Delete From MyTable Where ID=12345" '<- deletes only the record with the ID-Field with Value 12345 from MyTable

    or:
    Cnn.Execute "Update MyTable Set SomeField=NewValue Where ID=12345" '<- Update the Field SomeField only on the Record which has ID = 12345 on MyTable

    But as said, the Where-Clause can be used with the two "Data-Changing Statements" above
    in the same way as you do it on "normal Selects" (in Read-Direction).

    So, you don't have to perform a "Read-Direction-Select" (with a filtering Where-Clause) first,
    to then retrieve from the returned Recordset the Value of its ID-Field - which in turn you
    then use to identify the single Record in your Update-Query...

    Instead you can do all of the above in "one go" - by using the same Where-Clause as in your
    initial "Recordset-Select" (directly in your Update-Statement).

    Olaf

  29. #29

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: trying to update a Date field based on ID dosnt work proper

    Do you understand what this query will do?
    Code:
    Update ZHistory Set ZDateEnd = Int(ZDateStart)+0.9993 Where ZDateEnd Is NULL
    no i dont.never used this ever
    Do you understand what this is not doing
    Code:
    Rs.Open "SELECT Top 1 * FROM ZHistory order by ID desc", CN
    no i dont
    i understand that this selects the highest ID which is exactly what i need

  30. #30
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: trying to update a Date field based on ID dosnt work proper

    i understand that this selects the highest ID which is exactly what i need
    No, you said you need to know if the enddate was blank.
    check a field if its empty
    Just please put the update query in place of all your other 'stuff' and run it. Then check your database. I'd bet that if you have any startdates as of today, your enddates will now be populated (non-blank), is what your ULTIMATE goal is...NOT to get some ID.

    Use the Nike motto.....

  31. #31
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: trying to update a Date field based on ID dosnt work proper

    Quote Originally Posted by salsa31 View Post
    Code:
    Update ZHistory Set ZDateEnd = Int(ZDateStart)+0.9993 Where ZDateEnd Is NULL
    ...never used this ever
    That I don't believe, since I've seen you using the SQL Update Statement quite often in your posted Code-Snippets.

    Let me take this apart for you ...
    (although you could google and study that statement on thousands of Sites which explain SQL for you)...

    So we have: Update ZHistory Set ZDateEnd = Int(ZDateStart)+0.9993 Where ZDateEnd Is NULL

    Update ZHistory
    Specifies the TableName we will perform our Update on...


    Set ZDateEnd
    Specifies the FieldName (contained in the above Table) we want to assign a new Value to...


    = Int(ZDateStart)+0.9993
    Specifies the new Value the above Field will get ... when certain conditions are met...


    Where ZDateEnd Is NULL
    And the above Where-Clause describes those (Record-Filter-)conditions!

    So the Update will only be performed on those records, where ZDateEnd Is NULL.

    Was this helpful?
    If not - where in the above explanation did you get lost...?

    Olaf

  32. #32
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: trying to update a Date field based on ID dosnt work proper

    Still so many newbie mistakes though.

    Code:
    DateToday = CDate(Date & " 00:01")
    This is not how you add 1 minute to the curent date. Use DateAdd. Playing with Strings is playing with fire.

    Code:
    Rs.Open "SELECT Top 1 * FROM ZHistory Where ZDateStart = #" & Format(DateToday) & "#  ORDER BY     ID Desc", CN
    Using Format(DateToday) can get you in all sorts of trouble. Jet expects date/time literals in Invariant Locale format, i.e. dates are MM/DD/YYYY, but the Format() function (and do youself a favor and use Format$() instead) is locale aware which means if the current PC session's local uses DD/MM/YYYY you have chaos.

    Even better, stop building dynamic SQL strings and start using parameter queries.


    I think we have been over all of this with you many, many times. These accumulated goofs are getting painful to see.

  33. #33

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: trying to update a Date field based on ID dosnt work proper

    Schmidt ,DM,Sam please listen
    you are all telling me to make an update before i check the record why?
    Code:
     Update ZHistory Set ZDateEnd = Int(ZDateStart)+0.9993 Where ZDateEnd Is NULL
    i understand that this query updates the ZDateEnd if its null but i mentioned that i need to check first of all if the ZDateStart exists.
    another scenario
    1 Employee punches the clock every day ok?
    today for the matter is 12/02/2014 09:00 Now...
    this Employee finished is shift but forgot to punch out the clock still on the same date 12/02/2014
    before the next day that will arrive with a diffrent date i need to check if the Employee did punch the clock or not for the same day
    if not then update the ZEndDate Before the next date comes which is 13/02/2015
    so it needs to be like this if the employee didnt punch out the clock
    Code:
    ZDateStart 12/02/2015 09:00 ZEndDate 12/02/2015 23:59
    i hope its much clear now

  34. #34
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: trying to update a Date field based on ID dosnt work proper

    The where part of the update statement checks the record, you don't need anything else

    Surely after over 3000 posts you should be able to understand the meaning of a simple Where clause by now. I have saw lots and lots of posts from you where a where clause is required and used.

    If you need to check it to see if the start date exists then of course that needs to be part of your where clause as well you can have 1 or 2 or 100 checks in that where clause. It is just simple logic.

  35. #35

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: trying to update a Date field based on ID dosnt work proper

    The where part of the update statement checks the record, you don't need anything else
    i definitely understand the where clause sir
    If you need to check it to see if the start date exists then of course that needs to be part of your where clause as well you can have 1 or 2 or 100 checks in that where clause
    yes sir exactly
    unfortunately i don't know how to accomplish this alone by my self
    that is why i asked you and all the members in this forum

  36. #36

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: trying to update a Date field based on ID dosnt work proper

    i think i found the solution
    Code:
        Dim DateToday As Date
        Dim StrID As String
        Dim StrDate As Date
        Dim S As String
        DateToday = CDate(Date & " 00:01")
        Dim Rs As New ADODB.Recordset
     Rs.Open "SELECT Top 1 * FROM ZHistory Where ZDateStart <= #" & DateToday & "# AND IsNull(ZDateEnd)   Order By ID Desc", CN
         If Not Rs.EOF Then
                StrID = Rs!ID
                StrDate = Format(Rs!ZDateStart, "MM/DD/YYYY")
    
    S = "Update ZHistory Set ZDateEnd = #" & Format(Rs!ZDateStart, "mm/dd/yyyy") & "# Where ID = " & StrID
     CN.Execute S
       
            End If
    tnk you all for your help and time
    Last edited by salsa31; Feb 12th, 2015 at 04:22 AM.

  37. #37

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: trying to update a Date field based on ID dosnt work proper

    Solved!!!!
    Code:
     S = "Update ZHistory Set ZDateEnd = #" & Format(StrDate, "mm/dd/yyyy 23:59") & "# Where ID = " & StrID
        CN.Execute S

  38. #38
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: trying to update a Date field based on ID dosnt work proper

    Quote Originally Posted by salsa31 View Post
    Solved!!!!
    Code:
     S = "Update ZHistory Set ZDateEnd = #" & Format(StrDate, "mm/dd/yyyy 23:59") & "# Where ID = " & StrID
        CN.Execute S
    Salsa, the above is only a more complicated way to express the same thing as the (simpler)
    Update-SQL-String which was already posted:

    Update ZHistory Set ZDateEnd = Int(ZDateStart)+0.9993 Where ZDateEnd Is NULL

    You don't really need the additional: ...Where ID = " & StrID
    you applied above - and neither the String-Concatenations of your re-formatted StartDate
    (since the Int(ZDateStart)+0.9993 construct ensures the same thing).

    Olaf

  39. #39
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: [RESOLVED] trying to update a Date field based on ID dosnt work proper

    Salsa, please read this tutorial about the ADO Command object, very useful when dealing with date/time fields:
    http://www.vbforums.com/showthread.p...Command-object

  40. #40

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: [RESOLVED] trying to update a Date field based on ID dosnt work proper

    hey olaf
    what is this code?
    wht it means?
    Code:
    Int(ZDateStart)+0.9993

Page 1 of 2 12 LastLast

Posting Permissions

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



Click Here to Expand Forum to Full Width