Results 1 to 6 of 6
  1. #1
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2013
    Posts
    62

    VBA: Form date to match EOM date - what am I missing?

    I'm trying to setup a function in my module to run code based on the date in a user form matching the last day of the previous month, so it might look something like:

    Code:
    If [Forms]![main_form]![date] = DateSerial(Year(Date), Month(Date), 0) Then
    'do stuff
    Else
    'do other stuff
    End If
    Here's the funny thing - I can't get the form date to match the DateSerial. Down in the immediate window I double checked this:

    Print DateSerial(Year(Date), Month(Date), 0)


    7/31/2017 (correct)

    Print DateSerial(Year(Date), Month(Date), 0) = "7/31/2017"
    True (correct)

    Print [Forms]![main_form]![date]
    7/31/2017 (correct)

    Print [Forms]![main_form]![atb_date] = "7/31/2017"
    True (correct)

    Print [Forms]![main_form]![date] = DateSerial(Year(Date), Month(Date), 0)
    False (what????)

    What am I missing to make the DateSerial equal to the form?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In a brief test it works fine:

    ?forms!incoming.datereceived
    7/31/2017
    ?forms!incoming.datereceived= DateSerial(Year(Date), Month(Date), 0)
    True

    Maybe check the textbox and make sure it has a date format, or use CDate() around the form reference.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    date is a field in table? date is a reserved word and should not use reserved words as names for anything.

    If you want to test whether the two values are equal, use ? in front of the line in the Immediate Window.

    ?[Forms]![main_form]![date] = DateSerial(Year(Date), Month(Date), 0)

    should return True or False
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Is the Date field on your Form text or date datatype?
    Where is it being populated from?

  5. #5
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2013
    Posts
    62
    In a brief test it works fine:

    ?forms!incoming.datereceived
    7/31/2017
    ?forms!incoming.datereceived= DateSerial(Year(Date), Month(Date), 0)
    True

    Maybe check the textbox and make sure it has a date format, or use CDate() around the form reference.
    Quote Originally Posted by June7 View Post
    date is a field in table? date is a reserved word and should not use reserved words as names for anything.

    If you want to test whether the two values are equal, use ? in front of the line in the Immediate Window.

    ?[Forms]![main_form]![date] = DateSerial(Year(Date), Month(Date), 0)

    should return True or False
    This advice worked! I formatted the form field as general date, and used the ? to check if they were equal, and it returned true. That's what I was missing - so simple! Thanks!

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 1
    Last Post: 07-22-2017, 09:45 AM
  2. Find Match by Date Range
    By soldat452002 in forum Queries
    Replies: 2
    Last Post: 07-31-2016, 03:54 PM
  3. Identifying Missing Date Ranges
    By androo235 in forum Queries
    Replies: 1
    Last Post: 03-15-2016, 07:36 AM
  4. Replies: 3
    Last Post: 02-26-2016, 01:03 PM
  5. Populate Values if Match to Date
    By bs0d in forum Queries
    Replies: 2
    Last Post: 10-10-2014, 12:59 PM

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