Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2003
    Posts
    45

    Unanswered: DateDiff() Function

    how can I use DateDiff Function To Create A counter that Reset To 0 (Zero) in evry new year?

  2. #2
    Join Date
    Feb 2004
    Posts
    199
    rearange your question, what do you need actually, maybe datediff won't help you , write your task
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  3. #3
    Join Date
    Oct 2003
    Posts
    45

    DateDiff() Function

    I'd like to create a DataBase Table that contains IDNumber field that increment by one but in the next new year IDNumbe will reset to Zero?

  4. #4
    Join Date
    Mar 2002
    Posts
    32
    I don't think there is a way to reset an autonumber field. The only way I can see this being accomplished is by setting the field to a normal integer, and incrementing it through code every time the users presses the "New Record" button you put on said form. If you increment the number yourself through code then this is possible.

    Careful however that you are not using that ID as the Primary Key because you can not have a duplicate primary key.

    Let me know how you want to do this and I can post code.

  5. #5
    Join Date
    Oct 2003
    Posts
    45
    IDNumber is not used as Primary key, I used an SQL statment to increment the number by one but now the problem how to reset the number to zero evry new year? and I realy like to see ur code sugestions.

  6. #6
    Join Date
    Mar 2002
    Posts
    32
    Could you post exactly what happens when users add a record along with the SQL Statment?

    It would be best if you could still do things the way you area doing....I just need to know weather or not you have a date field.

    Also can you get the last record. With code you could do it using a recordset, but I'm not sure what you're doing now, and if you can make it work within what you've already got it would probably be good.

    So post what you got

    To give you and idea where I'm going however it would be

    If DateDiff(yyyy, LastRecDate, NewRecDate, vbMonday, vbFirstJan1) >= 1 Then
    ID = 0
    Else
    ID = ID + 1
    End If

    Or if you are going to do it in SQL you need to use the IIF function.

    IIF(DateDiff(yyyy, LastRecDate, NewRecDate, vbMonday, vbFirstJan1) >= 1,ID=0,ID=ID+1)

  7. #7
    Join Date
    Oct 2003
    Posts
    45
    the DataBase Contains the following:

    table1: autoNumTable - contain the field AutoNumNumber)
    table2:LPO - contain the fields: LPO_No (number)
    LPO_date (Date)
    LPR_No (number)
    LPR_Date (Date)

    LPO_No is the counter that will reset to 0 evry new year.

    I used the following SQL in the event of a command buttom:

    SQLStr = "UPDATE AutoNumTable SET AutoNumTable.AutoNum = [AutoNum]+1"
    DoCmd.RunSQL (SQLStr)
    Forms![LPOData]![LPO_Data_subform].SetFocus
    DoCmd.GoToRecord , , acNewRec
    Forms![LPOData]![LPO_Data_subform]![Lpo_no] = Forms![LPOData]![AutoNum]

    I hope that will make the idea more clear? thanks for you posts Bencustalow.

  8. #8
    Join Date
    Mar 2002
    Posts
    32
    This code should allow you to get rid of your autonumber table that you store the last number in as you dynamically look it up each time. It is not been debuged, so it may need some tweaking, if you have any trouble with it let me know. This code replaces your event code on the command button.

    '****Begin Code

    Dim strSQL as string
    Dim NewLPONum as integer

    Forms![LPOData]![LPO_Data_subform].SetFocus
    DoCmd.GoToRecord , , acNewRec

    strSQL = "SELECT * FROM LPO"
    Set LastRec = New ADODB.Recordset
    With LastRec
    .ActiveConnection = CurrentProject.Connection
    .CursorType = adOpenStatic
    .LockType = adLockReadOnly
    .Source = strSQL
    .Open
    End With

    LastRec.MoveLast

    If DateDiff(yyyy, LastRec.LPO_Date, Now(), vbMonday, vbFirstJan1) >= 1 Then
    NewLPONum = 0
    Else
    NewLPONum = LastRec.LPO_No + 1
    End If

    LastRec.Close

    Forms![LPOData]![LPO_Data_subform]![Lpo_no] = NewLPONum

  9. #9
    Join Date
    Oct 2003
    Posts
    45
    thanks,
    I Pasted the code on the event of the add button but it displays the following error:
    "Run Time error'438':
    Object dosen't support this property or method"
    and it hilights the If statment line?

  10. #10
    Join Date
    Oct 2003
    Posts
    45
    I think the problem is with type of LPO_Date.
    I did the folowing:

    Dim LPOD as Date

    lastrec.MoveLast
    LPOD= LPO_date
    msgBox LPOD

    The message display time not date (not the current system time)
    and when I move the mouse over LPO_date the tip displays " LPO_Date= empty " and over LPOD displays " LPOD= 12:00:00 "

  11. #11
    Join Date
    Mar 2002
    Posts
    32
    Try

    LPOD= lastrec.LPO_date
    And even try
    msgBox lastrec.LPO_No
    msgBox lastrec.LPO_date

    and let me know what you get.

  12. #12
    Join Date
    Oct 2003
    Posts
    45
    when I use LastRec with any LPO table Fields it displays the following Error Message:

    Run Time error ' 438':
    Object dosn't support this property or method

  13. #13
    Join Date
    Mar 2002
    Posts
    32
    hmmm...oops told ya I never tested or debugged the code.

    Up by your dim statements add

    dim lastrec as ADODB.recordset

  14. #14
    Join Date
    Oct 2003
    Posts
    45
    thanks alot, u were so kindly and helpful I did the folowing definitely with ur assist and it worked correctly:

    Dim strSQL As String
    Dim NewLPONum As Integer
    Dim lastrec As ADODB.Recordset
    Dim lpod As Date
    Dim LPON As Integer

    strSQL = "SELECT * FROM LPO"
    Set lastrec = New ADODB.Recordset
    With lastrec
    .ActiveConnection = CurrentProject.Connection
    .CursorType = adOpenStatic
    .LockType = adLockReadOnly
    .Source = strSQL
    .Open
    End With

    lastrec.MoveLast

    lpod = lastrec.Fields("LPO_Date")
    'lpod = lastrec.LPO_Date
    'MsgBox lpod

    If DateDiff("yyyy", lpod, date, vbMonday, vbFirstJan1) >= 1 Then
    NewLPONum = 1
    Else
    LPON = lastrec.Fields("Lpo_no")
    'MsgBox LPON
    NewLPONum = LPON + 1
    End If
    lastrec.Close
    Forms![LPODAta]![LPO_Data_subform].SetFocus
    DoCmd.GoToRecord , , acNewRec
    Forms![LPODAta]![LPO_Data_subform]![Lpo_no] = NewLPONum
    Forms![LPODAta]![LPO_Data_subform]![Lpr_no].SetFocus

    I used LastRec.fields("LPO_date) to Retrive Table LPO data not the form data Is that correct?

  15. #15
    Join Date
    Mar 2002
    Posts
    32
    Perfect

    Glad it worked for ya.

Posting Permissions

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