Passing Variables to a query

[Guest]

Sorry I'm new to using VBA in Access.
I have a form the user enter data in, then my codes calculates variables
that I want to pass to a query. I.e. the user enters a date, say 4/15/07, and
I would like to tell a query to pull all date values between 4/1/07 and
4/30/07. Can someone help me with the syntax to use these variables as
criteria in a query?

Thanks so much!

Paul

 

[John Spencer]

Criteria like the following might work.

Between DateSerial(Year([Forms]![YourFormName]![ControlName]),
Month([Forms]![YourFormName]![ControlName]),1) And
DateSerial(Year([Forms]![YourFormName]![ControlName]),
Month([Forms]![YourFormName]![ControlName])+1,0)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[John W. Vinson]

Sorry I'm new to using VBA in Access.
I have a form the user enter data in, then my codes calculates variables
that I want to pass to a query. I.e. the user enters a date, say 4/15/07, and
I would like to tell a query to pull all date values between 4/1/07 and
4/30/07. Can someone help me with the syntax to use these variables as
criteria in a query?

Thanks so much!

Paul

Use a criterion of

= DateSerial(Year([Enter date:]), Month([Enter date:], 1) AND < DateSerial(Year([Enter date:], Month([Enter date:] + 1, 1)

where [Enter date:] is whatever parameter you're using to prompt for the date.

John W. Vinson [MVP]