asked on

msaccess 2007 creating a "variable"

Hi.

I have a database with a lot of dates in it and I would like to create an input form that has a field or variable called date which I enter and then a query will display all the records in my table corresponding to that date. I know I could do this in a report but the query I will make will also update the date by 7 days. How do I get from the input form to the report to print out a simple query like this.?

Jeffrey Coachman

Not sure about the "update" part, ...but to have a form variable used as a query criteria you can:
1. Create a public Variable in a module:
Public dtmYourDate as date

2. Create a public function to retrieve this date:
Public Function GetYourDate() as date
GetYourDate=dtmYourDate
end function

Then create your query to look at this variable as the criteria:
SELECT....
FROM...
WHERE Yourdate=GetYourDate()

Then assign the form control value to the variable
Create a button to open the query
On the click event of the button, use code like this:
dtmYourDate =txtYourDate
Docmd.OpenQuery "YourQuery"

;-)

JeffCoachman

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PawloA

ASKER

How would I do this in Access. do I go to the visual basic. Sorry I am pretty new to msaccess and I have to take a crash course on this. Any help would be appreachiated.

PawloA

ASKER

Thanks. This is exactly what I am looking for.

Jeffrey Coachman

;-)

msaccess 2007 creating a &quot;variable&quot;

msaccess 2007 creating a "variable"