• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • Last Modified:

MS Access - Passing a date Value Input in a Form to an MS Access Report

I have a Form in which a user enters a date value into a text box in the format of mmddyyyy

In my VBA code, I then parse the information as follows:
StartDate = Format(CDate(Left([StartDate], 2) & "/" & Mid([StartDate], 3, 2) & "/" & Right([StartDate], 4)), "MM/DD/YYYY")

Now, I have the start date in a string in the format I want. I can MsgBox the date, I can use it as a filter in a Report, BUT I CANNOT GET THIS DATE TO APPEAR IN MY ACCESS REPORT (the capitalized words describe my problem/question)

I have a control box on my Report and I assign 'Start Date' as the Control Source, but the date field displays as #Error in my report no matter what I do - HELP!
1 Solution
Gustav BrockCIOCommented:
You can reference the textbox directly as the control source:

=CDate(Format(Forms("NameOfYourForm")!txtStartDate, "00/00/0000"))

In a standard module declare StartDate as a public variable then it will be available from anywhere.

Public StartDate as string

Then in the Load event of your report.

Me.YourReportControl = StartDate

When you open the report set the openargs to = Startdate.
Then on the report, your text box source =openargs.

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tom_wbiAuthor Commented:
Hi All,

Thank you for your suggestions. I will be able to test these this afternoon and post the results here.

I will probably evaluate all three suggestions to determine which is most applicable to my particular circumstance.

 Again, sorry for the delay.
Tom_wbiAuthor Commented:
This is what I was looking for!! Sorry about the delay!!!!

Gustav BrockCIOCommented:
You are welcome!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now