QBF Question

  • Thread starter Thread starter Alastair MacFarlane
  • Start date Start date
A

Alastair MacFarlane

Dear All

I have a form with 12 check boxes (chk1 - chk12)
respresenting the 12 months of the year, enabling the
users to select the months they wish to report on.

I would then like a QBF that looks at the check boxes that
are true and then filters a date field accordingly.

I have thought of only one way and this would involve code
and DatePart function, but is there an easier way to
undertake this?

Thanks

Alastair MacFarlane
 
If I were designing this functionality, I would use a multiselect listbox
showing the names of the months.
If would have a hidden column showing the number for that month.
You'll still need to use DatePart to build your criteria, but otherwise, it
should be fairly simple:

WARNING: AIR CODE
Dim Crit as string
Dim i as integer
For i=0 to DateList.ListCount-1
if DateList(i).Selected then
Crit=Crit & DateList.columns(1,i) & ","
end if
next
Crit="DatePart(DateField,'m') IN (" & Left(Crit, len(Crit)-1) & ")"

Of course you should build in a check for the case where no items are
selected.

HTH
- Turtle
 
Hi

You can use a 2-column combo box with list of months. The selection number
is equal to month number. I.e when you select 'March', the combo's value is
3.

Or, when you want to select a month for certain year, then create a table
with 1st of every month, started from some year and up to some date in far
future. Then cretae a single-column combo with dates from table as selection
(maybe it'll be wise to set in WHERE clause, that only months p.e. up to
current one are selected), and format it like p.e. "mmmm.yyyy". When you
select 'March', the combos value will be '01.03.2004'.

For both solutions, it'll be clever to set the default value for combo
(current month, or previous one, or whatever).

Create a report whith prints data for all months. When you start this report
from your form (using a button, or from Change event of combo), then sent
the WHERE clause to report. Something like this:
DoCmd.OpenReport "YourReport", acViewPreview, , "MONTH(DateField)=" &
Month(Me.YourCombo)
for solution wit 12 months to select, or
DoCmd.OpenReport "YourReport", acViewPreview, , "MONTH(DateField)=" &
Month(Me.YourCombo) & " AND YEAR(DateField)=" & YEAR(Me.YourCombo)
for solutions with month and year.
 
Turtle

Thanks for your help Turtle. There is always a more
obvious way than the way I would do it!

Alastair
 
Good thing you gave an aircode warning <g>

How about something like this (also aircode <g>):

Public strSQL As String
Public strList As String

SubListBoxName_Click()

Dim varItem As Variant

With Me.ListBoxName
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
End If
End With

End Sub

strSQL = "Select * From TableName WHERE DatePart('m',[DateField]) In (" &
strList & ");"

Then open the report or form with strSQL as a RecordSource. The code above
needs take strSQL out of the procedure and put it in the open form or report
code.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top