Page 1 of 3 123 LastLast
Results 1 to 15 of 39

Thread: DMax

  1. #1
    Join Date
    Apr 2008
    Posts
    24

    Unanswered: DMax

    I would like to use DMax with a Date field. What I would like to accomplish is to start out the date field with: Example: 01-Jun-08. then on entering in the next record have the DMax add 7 to 01-Jun-08. This would give me 08-Jun-08 as the beginning of the next week period.

    Is this possible? And if so how could this be written?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Look up DateAdd in the helpfiles
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2008
    Posts
    24
    My question is will DateAdd do the Same as DMax?

    With DMax I can have the date increasing by 7 in each new record.

    Will DateAdd increase increase new record by 7.

    The information is needed to schedule weekly events this is why I would like to have the ability to create a schedule for several months.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by sandrao
    My question is will DateAdd do the Same as DMax?

    With DMax I can have the date increasing by 7 in each new record.

    Will DateAdd increase increase new record by 7.

    The information is needed to schedule weekly events this is why I would like to have the ability to create a schedule for several months.
    when you looked up dateadd in the helpfiles, as GeorgeV suggested, what did you understand the function could do?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You could use DateAdd() but you'd have to use DMax() in conjunction with it, so why not just use DMax()?

    Code:
    Private Sub Form_Current()
    If Me.NewRecord Then
     If RecordsetClone.RecordCount = 0 Then
      Me.YourDate = Date - Weekday(Date, vbSunday) + 1
     Else
      Me.YourDate = DMax("[YourDate]", "YourTableName") + 7
      End If
    End If
    End Sub
    If you week starts on Monday, instead of

    Me.YourDate = Date - Weekday(Date, vbSunday) + 1

    use

    Me.YourDate = Date - Weekday(Date, vbMonday) + 1
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Because adding an integer to a date like that isn't considered best practice; in fact it could cause a few headaches down the line.

    I pointed the OP to the DateDiff function to be used in conjunction with DMax, to provide the result.

    Personally, if this is just for creating records then I'd much rather do it through SQL anyway.
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2008
    Posts
    24
    How would you go about creating the same results through SQL?
    And what did you mean by using DateDiff?

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I've been hearing for years that it "won't work down the road" and "Microsoft will stop supporting it!" But you know what? For years it's been working without a single problem!

    And you pointed him to DateAdd() not DateDiff().
    Last edited by Missinglinq; 05-27-08 at 19:13.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Apologies, I meant DateAdd().

    I recently discovered an implementation like the above on a MS platform, which had been silently working for a couple of years. However, when we went to add a new column to the view, it fell to pieces and we had to recreate it.

    Why risk that method when there is a standard, appreciated method for doing integer addition to a date?

    With regards to the SQL method; you'd still have to implement some sort of VBA loop to add multiple records but the general SQL would look like
    Code:
    INSERT INTO someDates (dateField)
    SELECT DateAdd("dd", 7, Max(dateField)) FROM someDates
    The great thing is that you can just execute the same query to create each new record!

    It works by grabbing the Max() dateField value in your table, and using DateAdd(), increases this by 7 days.
    George
    Home | Blog

  10. #10
    Join Date
    Apr 2008
    Posts
    24
    o.k. Thanks.
    I am not to familiar with using SQL

    I know how to apply VB but where would I place the SQL statement?

    and you mentioned still needing to create a VB code.

    Could you give me an example.

    I would appreciate it.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    pseudo-ish code from memory
    Code:
    Dim i As Integer
    Dim sql As String
    
    sql = "INSERT INTO someDates (dateField)" & _
           " SELECT DateAdd("dd", 7, Max(dateField)) FROM someDates"
    
    For i = 0 to 10
        CurrentDb.Execute sql
        i = i + 1
    Loop
    George
    Home | Blog

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ROFL!! That is not pseudocode... not even close!!

    If you didn't screw up the for loop, you could copy and paste it into Access ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Pfff - why loop?
    Code:
    INSERT INTO someDates (dateField)
    SELECT DateAdd("dd", 7 * tallytable.number, Max(dateField)) 
    FROM someDates, tallytable
    WHERE tallytable.number BETWEEN 1 and 10
    All you need is a tally table. What's a tally table? It is a table with one coumn and just includes integers from 0 to A Reasonably High Number.

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'd find looping easier tbh
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah yes - I find verbose code written in two languages easier too

Posting Permissions

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