Confused about whether to use a macro or a module

[lostris]

I need to have a report prompt the user for the dates before it runs.. the query that the report is running off of is a union query, so I gather i have to use either a macro or a module.. only hitch is I don't know which one to use or how to use them.. I've never messed with them before and am completely lost. Can anyone help?

 

[smedvid]

I would recommend using a Module over a Macro for ths simple reason that there is better ability to handle/trap error conditions.

However, in your case, I would consider creating a data entry form that has the (2) date fields (Start/End). Then have a button(s) that will Preview and/or Print the report. the Command to use is as follows...
Dim stDocName As String
Dim lcWhere As String
If Me.cbo_UserGroup.Value = "ALL" Then
lcWhere = ""
Else
lcWhere = "USERGROUP ='" & Me.cbo_UserGroup.Value & "'"
End If

stDocName = "rpt_User_List"
DoCmd.OpenReport stDocName, acPreview, , lcWhere

In the form you can build in some checks to insure that Start Date is before or equal to End date. Also check to see if both fields are filled in. Also verify that they are valid date format using Isdate(). After you have the dates entered, you have a couple of options:

1. Pass in the Where condition with the date ranges and let the report filter the date accordingly. My preferred approach. As displayed above where I use the lcWhere clause.

2. Another approach is the include the variables from the form within the actual Query in a hard-coded Where condition. Where [forms]![FormName].Variable...

htwh Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...