Report display

[Guest]

I have a form where the user selects the month and year, the user clicks on a
button and it prints the report. VB code's attached:

DoCmd.OpenReport stDocName, acPreview, , [rptMonth] = Me.[txtMonth], ,
[rptYear] = Me.txtYear

The user now wants to have the ability to filter results based on a Name
criteria. I understand the new code would be:

DoCmd.OpenReport stDocName, acPreview, , [Month] = Me.[Month], , [Year] =
Me.Year, ,[rptName]=Me.txtName

What I would like to know before implementing is that if the user leaves
name blank, would it still pull all of the names, as if no filters were
applied? Or would I have to modify the Name combo box so that it has an
include all wildcard?

 

[Nikos Yannacopoulos]

Keith,

What you need is something like:

strWhere = "[Month] = " & Me.Month & " And [Year] = " & Me.Year
If Me.txtName <> "" Then
strWhere = strWhere & " And [rptName] = '" & Me.txtName & "'"
End If

The above assumes Month and Year are both numeric. In case maonth is
text, the first line must be changed to:

strWhere = "[Month] = '" & Me.Month & "' And [Year] = " & Me.Year

Note: You are using Access reserved keywords Month and Year (Access and
VBA function names) as field and control names, which is not a good idea
at all; it may get you into problems eventually, with Access / VBA
getting confused as to whether you are referring to a field or control,
or calling a function. As a general rule, you should use different names
for your objects, like, for instance, fDate or InvDate instead of Date
etc. For a list of reserved keywords, look at:

http://support.microsoft.com/default.aspx?scid=kb;en-us;321266

HTH,
Nikos

 

[Guest]

I think it would not pull all of the names if the Name box is blank. One
approach may be to do something like:

If IsNull (Me.txtName) Then
' use original code
Else
' use new code
End If

There are probably other approaches, but this is the one that occurs to me.