DoCmd.OpenReport - WhereCondition

1) If you want to use TextBox_Change to set strPrintLang, then it has to be
a module-level variable, not procedure-level.
As a procedure-level variable, it goes out of scope (ceases to exist) once
the procedure ends. That would explain why it has no value when you call
OpenReport.

2) Wildcard matching requires using Like, not =, so you need to accomodate
that requirement as well.

A slightly different approach, that puts off creating the variable until you
are about to use it (aircode):

Private sub cmdPrintReport_Click()

Dim strPrintLang as string
Dim strPrintDist as string
Dim strSQL as string

If TextBox1.Value = "ALL" Then
strPrintLang = "[AdvisorLang] Like '*' AND "
Else
strPrintLang = "[AdvisorLang]='" & TextBox1.Value & "' AND "
End If

If TextBox2.Value = "ALL" Then
strPrintDist = "[MailCode] Like '*'"
Else
strPrintDist = "[MailCode]='" & TextBox2.Value & "'"
End If

strSQL = strPrintLang & strPrintDist

'Put a breakpoint on the following line and type ?strSQL in the Immediate
window to eyeball the value of your string
DoCmd.OpenReport "F/U: Complete Report -- Finaltest", acViewPreview, ,
strSQL

End Sub

HTH,


"Eric_G" wrote in message
...
thanks, Ken, but for whatever reason, I'm receiving the error message:
"OpenAction report was cancelled".

Here is a sample of the code I am using to assign the values to the field
"PrintLang":

Public Sub TextBox1_Change()
Dim PrintLang As String

PrintLang = TextBox1.Value
If PrintLang = "ALL" Then
PrintLang = "*"
Else
PrintLang = "*"
End If

I then proceed to use similar code to assign values to other variables,
but
I believe the variables aren't picking up the values.

"Ken Snell (MVP)" wrote:

Put the AND operator inside the quotes, and delimit the text string
PrintLang with ' characters:

DoCmd.OpenReport "F/U: Complete Report -- Finaltest", acViewPreview, ,
WhereCondition:="[AdvisorLang]='PrintLang' And [MailCode]='PrintDist'"


--

Ken Snell
MS ACCESS MVP



"Eric_G" wrote in message
...
I am attempting to do the same thing but with conditions based on
different
fields:

This is my existing code but I keep receiving an error; I believe it
pertains to incorrect syntax:

DoCmd.OpenReport "F/U: Complete Report -- Finaltest", acViewPreview, ,
WhereCondition:="[AdvisorLang]=PrintLang" And "[MailCode]='PrintDist'"



"fredg" wrote:

On Wed, 10 Aug 2005 13:34:04 -0700, ACase wrote:

How do I employ multiple Where conditions.

Example

strWhereCountry = "Country = Forms![frmRptMenu]!cmb_Country"
strWhereRegion = "Region = Forms![frmRptMenu]!cmb_Region"

DoCmd.OpenReport "rpt_New_Bus_Wins_by_Sector", PrintMode,
strWhereCountry,
and strWhereRegion

This is not working - is there a better way?

Any help would be much appreciated.
AC

Why do you need 2 separate string variables?

Is the bound column of cmb_Country and cmb_Region a Text or Number
datatype? Is that the same datatype as the datatype of [Country] and
[Region]?

If the combo bound columns and [Country] and [Region] fields are
Number datatypes:

Dim strWhere as String

strWhere = "[Country] = " & Me!cmb_Country & " and [Region] = " &
Me!cmb_Region

If the combo bound columns and [Country] and [Region] fields are Text
datatypes?

strWhere = "[Country] = '" & Me!cmb_Country & "' and [Region] = '" &
Me!cmb_Region & "'"

DoCmd.OpenReport "rpt_New_Bus_Wins_by_Sector", acViewPreview, ,
strWhere

Note. You need to watch your comma placement.
You had them incorrect in your OpenReport syntax.
The strWhere clause goes in the Where clause argument, not in the
filter argument position.
DoCmd.OpenReport "Name", view, filter, where clause
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.