Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Update Field With Correct Date?? (2003)

    Hi,

    I'm working on some code to update a field to the previous days date of a morning, obviosly if its a Monday it has to be the Friday date. I thought it could go something like this. (in Freehand). I've a Table called "Daily Update File" and Column called "Amended"

    Message Box "Is it Monday" [yes] [no]
    If Yes
    Then Update to Date () minus 3 Days
    Else Update to Date() minus 1 Day

    Thats all fine and Dandy but how do I then take into account the months. I'm assuming that someones already got some code for this as I can see that it would require knowing the number of days in the month and then referencing the structure. If it automates knowing when the Weekends are, all the better. I could of Course do an input box which then updates whichever date the user types in, which could be easier and maybe more flexible.

    Anyway advice Gratefully received, as I'm just starting to work on this.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Field With Correct Date?? (2003)

    You don't need user intervention. You can use code like this:
    <code>
    Me.PreviousDate = Date() - 1 + 2 * (Weekday(Date()) = 2)
    </code>
    where PreviousDate is the field you want to update. Note: this will only work if the current day is Monday to Saturday. If you want to be able to run the code on Sunday and set the value to the previous Friday, you'd need to do a bit more.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Update Field With Correct Date?? (2003)

    Also if the Field is Date/Time I get: 30/12/1899 even if I set the format to Short Date??????

  4. #4
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Update Field With Correct Date?? (2003)

    Hi.

    I've been playing with this: I'm trying to force the date into a format and apply it to a SQL statement to a update multiple fields like:

    Dim Date1 as String

    Date1 = Format((Date - 1 + 2 * (Weekday(Date) = 2)), "dd/mm/yyy") ? The complier keeps takeing out the () of Date?

    plusdateSQL = "Update [Daily Update File] SET [Entered] = " & Date1 & " WHERE [Entered] Is Null"

    CurrentDb.Execute plusdateSQL, dbFailOnError

    But all I get is: 4.81647566849361E-03

    How do I force this to input the correct date, using this method???? The Field can Either be Date/Time or Text.

    Thanks.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Update Field With Correct Date?? (2003)

    Try this

    Dim Date1 as String
    Dim plusdateSQL as string

    Date1 = Format((Date - 1 + 2 * (Weekday(Date) = 2)), "dd/mm/yyy<font color=red>y</font color=red>")

    plusdateSQL = "Update [Daily Update File] SET [Entered] = <font color=red>#</font color=red>" & Date1 & "<font color=red># </font color=red> WHERE [Entered] Is Null"

    CurrentDb.Execute plusdateSQL, dbFailOnError


    Is the field [Entered] set up as Date/Time field or just a number?
    Regards
    John



  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Field With Correct Date?? (2003)

    You must always use US date format in SQL, so

    Date1 = Format((Date - 1 + 2 * (Weekday(Date) = 2)), "mm/dd/yyyy")

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Field With Correct Date?? (2003)

    John Hutchison already answered the main points (but see my reply to him!).

    > The complier keeps takeing out the () of Date?

    In expressions, you must use Date(), but in VBA code, the function is simply Date, without parentheses ().

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Update Field With Correct Date?? (2003)

    I forgot that today because it worked for me here in dd/mm/yyyy format.
    Presumably this is because 29/03/2007 can only March 29.
    Regards
    John



  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Field With Correct Date?? (2003)

    That is correct. 29/03/2007 is unambiguous, so it will be handled correctly. But 01/04/2007 would be interpreted as the 4th of January, not as April Fool's day.

  10. #10
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Update Field With Correct Date?? (2003)

    OK Johns. Code Corrections Work.

    So Hansv How do I then make sure the correct English Date is applied to the Field for every instance?????

    Thanks, It would take me ages without the input.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Field With Correct Date?? (2003)

    Sorry, I don't understand your question. Could you elaborate?

  12. #12
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Update Field With Correct Date?? (2003)

    OK. Ummm.....
    We've managed to get the code corrected by John (with date code snippit supplied by HansV) to update the [Entered] Field in the [Daily Update File] Table, but you said that there may be a problem with the Turn arround of the Date (dd-mm-yyyy to mm-dd-yyyy (IE English to US) if the day is 12 or lower. How do I prevent this from happening in the code above. I can change the Field to be either Date/Time or Text, as this is just an archive file, but as it has to be presentable and reliable I have to make sure it working properly before I apply it to the update system on the mdb file.

    Thank-You

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Field With Correct Date?? (2003)

    The Entered field should be a date/time field. The code

    Dim Date1 as String
    Date1 = Format((Date - 1 + 2 * (Weekday(Date) = 2)), "mm/dd/yyyy")
    plusdateSQL = "Update [Daily Update File] SET [Entered] = #" & Date1 & "# WHERE [Entered] Is Null"
    CurrentDb.Execute plusdateSQL, dbFailOnError

    should then work correctly, whether your PC uses English or US date format.

    In general, I recommend creating a copy of the database immediately when you have created a query or code that changes data, and testing on the copy first.

  14. #14
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Update Field With Correct Date?? (2003)

    Thanks. That seems have done it. I changed the system date a few times and then also exported the test file and it seems to hold in the right format. Excellent. Thats just about got that sorted.

    As I said the time you've saved me. Words can't express.

Posting Permissions

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