Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Aug 2001
    Thanked 0 Times in 0 Posts

    Function to Return a Date (Access 97/SR-2)


    We would like to write a function that returns a date.
    The date is one that is supplied by the user in a custom form. This form details
    specific information about the date that is being requested, e.g., "Enter the Start
    Date for the reporting period:", or "Enter the date of purchase:".

    Is it possible to do this in Access? If so, can anyone show us a working example?
    Our problem seems to be that Access does not wait for a form to be processed by the
    user before proceeding to the next line of code so once the form is opened the
    next line of code is immediately processed and the function terminates (way before
    we get a chance to find out what the user entered!). I'm guessing that we are missing
    something really obvious but we just can't figure it out!!

    TIA, Jennifer

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Richland, Washington, USA
    Thanked 0 Times in 0 Posts

    Re: Function to Return a Date (Access 97/SR-2)

    The easiest way to do this is with the InputBox function (don't use a custom form at all). Something like:

    Public Function dteGetDate(strPrompt As String) As Date

    dteGetDate = CDate(InputBox(strPrompt))

    End Function

    Then your calling routine would have something like:

    dtePurchaseDate = dteGetDate("Enter the date of purchase:")

    But it sounds like you may want something a little fancier, using an actual form. This is a little trickier since the data in the form is available only when the form is open (unless the data is bound to a table). I'd suggest using an unbound form and assign the value of the date field (also unbound) to a global date variable in the control's AfterUpdate event. Now, to "pause your code" while the form is open, you need to open it with the WindowMode set to acDialog. For example, something like:

    DoCmd.OpenForm "DateForm", , , , , acDialog

    Code following this statement won't execute until you close the form. When the form is closed, you can reference the entered date via the global date variable.

    Hope this helps. If you need more specific details, holler. I'm sure someone will come to your rescue.

Posting Permissions

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