Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Aurora, Ontario

    Question Unanswered: Resetting to Zero

    Good Day All,

    I have an Overtime issuance Log which tracks all employees and the total amount of overtime they have received for a specific period of time.

    I would like to reset the numerical values to zero on a specific date.

    I have tried to no avail to teach myself, but I can't figure it out.

    Any information regarding this issue would be greatly appreciated.

  2. #2
    Join Date
    Jan 2004
    Aberdeen, Scotland
    the simpilist way i can think of is to use an autofilter then manually overwrite the data

    or you could use a simple macro to search for a specific date then overwrite the data
    if your interested in the macro option then we could work through this


  3. #3
    Join Date
    Jul 2004
    If there are standard time durations for the forms i.e. 1 month, 6 months etc. just have a created date that's automatically filled in every time you log someones overtime. While doing that, you can set the duration, lets use 1 month for example.

    You can easily calculate the date that the entry needs to be reset, so you could just do something like:

    If Date(this would be the system date) = ResetDate(this is the derived date based on the entry date + duration) Then selected fields = ""
    End If

    Im using an access frame of mind for this problem, but if it was to work that way, you can easily adapt it to suit your needs.

  4. #4
    Join Date
    Aug 2004
    Aurora, Ontario
    Sorry it took so long to pick up your replies.

    I will try both the auto filter and specific duration methods to see if it will do what I need.


    I am able to reset the form manually with the following code but for some reason some of the personnel's phone number get reset to zero, any thoughts.

    Once again thanks for the input.

    Sub resettest1()
    For Each n In Range("F2:G99")
    If Date = (#2/27/2005#) Then

    If n.Value <> 0 Then
    n.Value = 0

    End If
    End If
    Next n

    End Sub

  5. #5
    Join Date
    Mar 2004
    Minnesota, USA, Earth
    You could try the IsDate function.

    I'm not quite sure how to apply it to your current code, but I would guess something like...

    (after the For each n statement)
    If IsDate(n)=True THEN

    and, obviously, an "End if" before the "Next n" statement.

    However, I'm not familiar with the format of your telephone numbers, so this may not work if VBA thinks they are dates anyway.

    A less robust way to fix this could be a different If statement where it checks the value of N to make sure that it isn't above or below a certain date range.


Posting Permissions

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