querying a date based on month

[Gator]

how can i open and query a report displaying only the months of the month of
the date in the textbox of the current record on a form?

 

[Jeff Boyce]

Are you working in Microsoft Access?

MS Acces doesn't have a way to "open and query a report". You can open a
table. You can create a query based on a table. You can create a report
(based on either a table or a query).

If you are working in query design view, you can add a new field that
returns the month of a date in a date/time field, but this requires that the
underlying field in the table is actually a date/time value, not just text
(hint: use the Month() function).

And I'm quite confused how you got to "the current record on a form" from
the other information.

More specific description may lead to more specific suggestion...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Gator]

I have a form where only one record is shown at a time based on a table that
has several records. As I scroll through the records a different date will
appear in the form textbox. When I stop scrolling, a certain date will be
displayed in the textbox. From here, i click a button that will open a
report that only displays the dates, from the same table, for the month that
is part of the date in the current record in the form where the button was
clicked. What about using docmd.OpenRecord and applying the filter here?

 

[Jeff Boyce]

I may not be fully understanding the data you have or what you are doing
with it...

It seems to me you could create a query that uses the Month() to find the
correct dates, and uses the Month() function as part of your selection
criterion for that field, perhaps something like (untested):

Month(Forms!YourFormName!YourTextBoxName)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Graham Mandeno]

Hi Gator

A simple way to filter on month is to compare two dates formatted with year
and month only:

"Format([FieldInReport], 'yyyymm')=" & Format([FieldInForm], "\'yyyymm\'")

(note carefully all the single and double quotes!)

Another option is to compare Year and Month values:

"Year([FieldInReport])=" & Year([FieldInForm] _
& " and Month([FieldInReport])=" & Month([FieldInForm]

Both these will be quite slow if you have many records to filter because
they have to process every one of the date fields in your report's
recordsource.

If this is a problem, you can use the following tips to create a more
efficient filter string:

FirstDayOfMonth = DateSerial(Year(TheDate), Month(TheDate), 1)

LastDayOfMonth = DateSerial(Year(TheDate), Month(TheDate)+1, 0)

FirstDayOfNextMonth = DateSerial(Year(TheDate), Month(TheDate)+1, 1)

To check for a date being in a given month you can use either:

[DateField] Between FirstDayOfMonth and LastDayOfMonth

or

[DateField]>=FirstDayOfMonth and [DateField]<FirstDayOfNextMonth

The second one is more reliable if it's possible your date field might also
include a time value.

You could put all this together into a handy function:

Public Function SqlMonthFilter(strDateField As String, dt As Date) As String
Const cDateFormat = "\#yyyy-mm-dd\#"
SqlMonthFilter = "(" & strDateField & " >= " _
& Format(DateSerial(YEAR(dt), month(dt), 1), cDateFormat) _
& " and " & strDateField & " < " _
& Format(DateSerial(YEAR(dt), month(dt) + 1, 1), cDateFormat) _
& ")"
End Function

Then use the function directly to create the WHERE condition string for
OpenReport:

DoCmd.OpenReport "ReportName', acPreview, , _
SqlMonthFilter("[FieldInReport]", [FieldInForm])

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand