Results 1 to 7 of 7
  1. #1
    sarcher is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    9

    Date parts as numbers

    So I have been trying to get the parts of a date and then adding up the numbers of the date parts

    Dim d1 As Integer
    Dim d2 As Integer
    Dim d3 As Integer
    Dim d4 As Integer



    If Not IsNull(Me.BDAY) Then
    d1 = DatePart(m, Me.BDAY)
    d2 = DatePart(d, Me.BDAY)
    d3 = DatePart(yyyy, Me.BDAY)
    d4 = d1 + d2 + d3


    Me.LPATH = d4

    I get an improper procedure call error.

    Can someone help me with converting d1,d2, and d3 to integers so I can add them together.

    TIA
    Stephen

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    clng or cint would do it

    i.e. d1 = clng(datepart("m", me.bday))

    however I haven't run into an instance where a datepart executed on a date field didn't return a numeric value. Are you sure me.bday isn't being treated as a text field?

    also.... what do you intend to do with this wouldn't 2/1/2019 and 1/2/2019 be evaluated as being the same number?

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I think your error is because the first argument is a string, which rpeare corrected in their reply. You'll get the invalid procedure error as you have it. I suspect all you need to do is fix that.

    d1 = DatePart("m", Me.BDAY)

    The math doesn't make sense to me either.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Do you want the result as integer in format "mdyyy"?

    Let's assume the date is 1/21/2019. You get integer 1212019. There will no way to decide, was the original date 1/21/2019 or 12/1/2019.

    Only way to have original date determined will be to have the result sa string in format "mmddyyyy", i.e. with my example date it will be "01212019". But in this format it will be practically un-usuable as you never can order such strings properly. The only reasonable format format will be "yyyymmdd", i.e. with my example date "20190121". Easiest way to calculate it will be
    Code:
    Me.LPATH = FORMAT(Me.BDAY,"yyyymmdd")
    As year number will not have leading zeros in foreseeable future, using this format you can have result as integer too
    Code:
    Me.LPATH = CInt(FORMAT(Me.BDAY,"yyyymmdd"))

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I guess it needs to be clarified if the goal is 2+2=4 or 2+2=22. For the second you'd use & rather than +.

    Quote Originally Posted by ArviLaanemets View Post
    Me.LPATH = FORMAT(Me.BDAY,"yyyymmdd")
    This is also what I use when I need a string. As ArviLaanemets mentioned it sorts better than other formats.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    sarcher is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    9
    yes same number

    Thank that worked perfectly - Stephen

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    mark it solved!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with date parts
    By JimO in forum Access
    Replies: 3
    Last Post: 08-11-2019, 04:10 PM
  2. Parts List on Parts Requisition Report
    By billgyrotech1 in forum Access
    Replies: 16
    Last Post: 06-03-2019, 01:17 PM
  3. Replies: 1
    Last Post: 11-27-2017, 02:02 PM
  4. Replies: 2
    Last Post: 11-02-2016, 07:28 AM
  5. Replies: 3
    Last Post: 08-02-2012, 11:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums