How to make a query that will choose all the record in that month

[justinp1]

Hi,

Is there a way to have a query that will only display all the record in that current month? And this month will be user determined through a form.

Thanks!

 

[jwindon]

Yes. You will have to have the date of the record stored somewhere in your tables. There are several ways of returning those records based on user input. The most useful being a parameter query where the user enters the beginning and ending date of records they would like returned. For example: The form is unbound, with two unbound controls: One for beginning and One for end.

Your query is based off of the selections the user makes on this form.

Query
Criteria: DateOfRecord
>=[Forms]![UnboundFormName]![BeginningControlName] and <=[Forms]![UnboundFormName]![EndingControlName]

As I said, there are several ways to go about retrieving data based on a date. That is one. Hope that helps.

 

[Dave Sherer]

another way is go to the next open column an type in on the field line

MONTH([the table the date is stored in].[The Field name of the date])

Then in the Criteria line put

[Forms]![Form Name]![Field of user input]

Example:

[color red]FIELD[/color]Month([Child.Birthday])
[color red]CRITERIA[/color][Forms]![BirthdayCards]![EnterMonth]

The bad thing about this is they need to type a number in the box.

But if you use a combobox with column(0) as the number of the month and column(1) as the name of the month then you could have your criteria as this

[color red]CRITERIA[/color][Forms]![BirthdayCards]![EnterMonth].Column(1)

HTH

 

[Dave Sherer]

Sorry Last Post I tried some HTML for effect and it did not work Plus I did make one mistake. It is all coreccted now.

another way is go to the next open column an type in on the field line
MONTH([the table the date is stored in].[The Field name of the date])

Then in the Criteria line put

[Forms]![Form Name]![Field of user input]

Example:

FIELD Month([Child.Birthday])
CRITERIA [Forms]![BirthdayCards]![EnterMonth]

The bad thing about this is they need to type a number in the box.

But if you use a combobox with column(0) as the number of the month and column(1) as the name of the month then you could have your criteria as this

CRITERIA [Forms]![BirthdayCards]![EnterMonth].Column(0)

HTH

 

[Rich]

What about the year?

 

[Dave Sherer]

Good point Rich

then the statement would look like this

CRITERIA ([Forms]![BirthdayCards]![EnterMonth].Column(0)) and (Year(Now())