Data By Month with a variable as parameter?? 1

[lunarmono]

Hi, my skills are extremely limited so any help resolving the following issue will be invaluable. I am making a database of patient medication orders which has a table with fields "last name", "first name", "chart #" and "Order Date" among others. What I have now is a series of queries that eventually sum up the number of different patients ordering medications in a particular month. THey go like this:

1) Select Query of all fields with [order date] field parameter prompting for the "month as a number followed by a /" (((Parameter: Like [Enter Month as number followed by a /] & "*&quot))

2) Query of this query's results showing only [first name], [last name], and [chart #] and requiring unique values.

3) Query of this query's results counting the [last name] column.

This works, but it has problems. First of all, entering in a month as a number with a / is not pretty. Secondly, only the stats for a single month can be viewed at a time. Ideally, I could bring up a form which would look as follows:

JAN FEB MAR

# of Unique Pts Ordering: 342 254 896


I can visualize the solution involving setting a variable as the search parameter and then leaving it up to the form or a macro or whatever to set that variable for each text box in the form.
Anyhoo, thank you very kindly for reading about my problem and in advance for any advice you may have.
Sincerely,
Andy

 

[LarryDeLaruelle]

Andy:

In the field cell for the date enter something like this:
Criteria: DatePart("M", [DateField])

In the criteria cell enter your prompt:
[Enter Month Number]

The problem is that you will get everything for the selected month regardless of year. You would probably want something like this.

In the field cell:
Criteria: DatePart ("M", [DateField]) & "/" & DatePart("YYYY", [DateField])

Prompt
[Enter Month/Year as MM/YYYY]

This assumes your users will understand what you mean by "as MM/YYYY". Never underestimate the ability of users to find a way to do it wrong.

A better solution would be to use a query by form method. If you are not familiar with it, I can help you out.

HTH

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[isampurna]

Hi Larry,
I would like to know the method for month-year parameter using form. Is it possible for us to make a lookup (combo box) displaying the month-year and user just select that? And how to parse the month-year value back to date again for the query?

Thanks
Irwan

 

[LarryDeLaruelle]

isampurna:

Query by form is a pretty straight forward process.

1. Create an unbound form, name it something like frmMonthSet.

2. Add two text boxes to the form, name then txtStart and txtEnd (this is where the user will enter the start and end dates for the report period).

3. Add a command button to open the report or query.

4. Open the query where you want to use the two date entries.

5. In the criteria cell of the date field type this:

>=[Forms]![frmMonthSet]![txtStart] And <=[Forms]![frmMonthSet]![txtEnd]

or you can use the Expression Builder by right clicking on the criteria cell and selecting Build to create the above string. Check Help for Expression Builder for specifics.

I usually take this one step further since I don't trust users to enter dates correctly and display two list boxes for the user to select the month and year for the report range. The following (from one of my class handouts) explains how to set that up on the frmMonthSet.

Access Code Sample

Code to use a list boxes to set a month date range for report generation

Problem: Allowing users to enter the beginning and ending dates opens the possibility of invalid date values being entered which will produce a vague error message. Trapping for such date errors is difficult.

Solution: Allow users to only select for a month/year combination and set the beginning and ending dates via code.

This code sample uses the following controls on an unbound form to set a monthly date range for the production of reports:

lstMonth List Box Control to select report month
lstYear List Box Control to select report year
txtStartDate Beginning date of the selected report month/year
txtEndDate Ending date of the selected report month/year
txtReportMonth Formatted header for inclusion in the report output

lstMonth.RowSource = tblListMonth

SELECT DISTINCTROW [tblListMonth].[MonthNum], [tblListMonth].[MonthName] FROM [tblListMonth];

lstYear.RowSourceType = ListYears() [Function – See Below]

Form Code:
'frmSetDates
'Example of setting report date values via code
'On Open -- Sets the date range of the report to the previous month's first and last date.
'After Update(lstMonth and lstYear) -- Sets the date range to the selected Month/Year combination; provides
‘warnings if the user selects the current or later month.
'Date values are passed to the underlying query via reference or can be used internally in the creation
‘of queries via sql code.
'Properties (Optional):
'Control Width Height ColumnWidths RowSourceType RowSource
'lstYear 0.40 0.60 0”,.4” ListYears()
'lstMonth 0.75 1.80 0”,.75” tblListMonth
'txtStartDate 0.75 0.1667
'txtEndDate 0.75 0.1667
'txtReportMonth 2.60 0.1667
'
Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)

Call SetMonth ‘Sets the initial values for lstYear and lstMonth
Call SetDates ‘Sets the initial values for the text controls

End Sub

Private Sub lstMonth_AfterUpdate()

Call SetDates

End Sub

Private Sub lstYear_AfterUpdate()

Call SetDates

End Sub


Private Sub SetMonth()

If DatePart(&quot;M&quot;, Date) = 1 Then
'If the current month is January then we want to set the report
'month to December and the report year to the previous year.
lstMonth = 12
lstYear = DatePart(&quot;YYYY&quot;, Date) - 1
Else
'If the current month is after January then just set the
'report month to the previous month.
lstMonth = DatePart(&quot;M&quot;, Date) - 1
lstYear = DatePart(&quot;YYYY&quot;, Date)
End If

End Sub

Private Sub SetDates()

Dim datStart As Date
Dim datEnd As Date
Dim intMonth As Integer
Dim intYear As Integer

intMonth = CInt(lstMonth) 'Makes the value of list month an Integer for comparison purposes
intYear = CInt(lstYear) 'Makes the value of list year an Integer for comparison purposes

If intMonth = DatePart(&quot;M&quot;, Date) And intYear = DatePart(&quot;YYYY&quot;, Date) Then
'The selected month is the current month and the user is shown a prompt to cancel/continue.
'If the user selects cancel, the list boxes are reset and the sub is exited.
If MsgBox(&quot;This is the current month!&quot; & vbCrLf & vbCrLf & _
&quot;Do you wish to continue?&quot;, vbOKCancel + vbQuestion, &quot;TCC Reports&quot = vbCancel Then
Call SetMonth
Exit Sub
End If
Else
'The selected month/year is later than the current date; no records will be available.
'The user's only option is to exit the sub and select another month/year.
If intMonth > DatePart(&quot;M&quot;, Date) And intYear >= DatePart(&quot;YYYY&quot;, Date) Then
MsgBox &quot;The month selected is after the current month!&quot; & vbCrLf & vbCrLf & _
&quot;Please Cancel by clicking on OK.&quot;, vbOKOnly + vbCritical, &quot;TCC Reports&quot;
Call SetMonth
Exit Sub
End If
End If

'If the user selects OK on the first condition test or neither condition is true, then the date range
'and report header text controls are set to the selected month/year combination.
datStart = lstMonth & &quot;/01/&quot; & lstYear 'Example: 1 + /01/ + 2003 = 01/01/2003
datEnd = DateAdd(&quot;M&quot;, 1, datStart) 'Example: 01/01/2003 + 1 Month = 02/01/2003
datEnd = DateAdd(&quot;D&quot;, -1, datEnd) 'Example: 02/01/2003 - 1 day = 01/31/2003
txtStartDate = datStart
txtEndDate = datEnd.
txtReportMonth = Format(datStart, &quot;MMMM D&quot; & &quot;, &quot; & &quot;YYYY&quot & &quot; To &quot; & Format(datEnd, &quot;MMMM D&quot; & &quot;, &quot; & &quot;YYYY&quot
'Example: January 1, 2003 To January 31, 2003

End Sub

This function should be placed in a separate module or added to an existing module. It can be called by lstYears.RowSourceType from any form which uses this technique to establish report date ranges.

Public Function ListYears(fld As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant

Dim intOffset As Integer
Dim intYear As Integer

intYear = DatePart(&quot;YYYY&quot;, Date) – 3 ‘Sets the beginning year to 3 years in the past; list box will display the current
‘year and the last three years

Select Case code
Case acLBInitialize
ListYears = True
Case acLBOpen
ListYears = Timer
Case acLBGetRowCount
ListYears = 4
Case acLBGetColumnWidth
ListYears = 0.75
Case acLBGetValue
intOffset = 1
ListYears = intYear + intOffset * row
End Select

End Function

See List Boxes/Source Data in Access Help for an explanation of this code.

HTH

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[isampurna]

Hi Larry,
It works perfectly in my program. Thank you so much. One thing, we don't need the parantheses when call the RowSourceType (at least on my Access )

Thanks again
Irwan

 

[LarryDeLaruelle]

Irwin:

Thanks for pointing that out and thanks for the star. I'll remove that from the handout (no sense confusing people needlessly).

Glad I was able to help.

If you need to do reports that span mutiliple months, this can be easily modified to diplay two month/year list box sets to allow the user to select different starting and ending months. It can also be modified to use quarter, semi and annual date setups.

Most reports I'm asked to set up use either a month or multi-month set of dates which makes it pretty straight forward. On a few occasions, I have had users who want to start and/or end report periods sometime other than the first and last dates. For those, I use a custom calendar form that allows them to pick a specific day within the month.

That's what makes this so much fun, there's always another challenge just around the corner.



Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[isampurna]

Yes, I get the idea for &quot;month from&quot; and &quot;month to&quot; criterias using two month/year listboxes.
Anyway, the calendar is sound very interesting. How you create that calendar? Just curious, you don't need to give the code if you mind.

Regards
Irwan

 

[LarryDeLaruelle]

Irwan:

I'll try to get it to you tomorrow. It's really rather simple just a bunch of text boxes with a number of loops to populate.

I've tried using the ActiveX control but it drove me crazy (short drive) and decided to make my own.



Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[LarryDeLaruelle]

Irwan:

It might be easier if I just sent you a sample database with the calendar form in it. It's not difficult to do, but it is hard to explain without drawing pictures.

If you'll give me your e-mail address and what version of Access you are using, I'll zip it up and send it to you.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org