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

Topic

New Reply

Hello,

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

Reply | Quote

Replies

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.

Reply | Quote