DoCmd.OpenReport - WhereCondition

[ACase]

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

 

[fredg]

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

 

[ACase]

Perfect - Thank you.

One additional question though, is how do I manage the Null values. If the
user does not select any values in the drop down.

Thanks Again
AC

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

 

[fredg]

Perfect - Thank you.

One additional question though, is how do I manage the Null values. If the
user does not select any values in the drop down.

Thanks Again
AC

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

It depends.
What do you want to happen if nothing has been entered?
Print all records? Print nothing?
Show a message requesting an entry?

You would first decide what you want to happen, then you can check for
no combo entry by using:
If IsNull(Me![cmb_Country]) Or IsNull(Me![cmb_Region]) Then
Do this
Else
strWhere = "Etc."
End If

 

[ACase]

Sorry you lost me on this one.

If the user does not enter anything into the combo boxes, then I would like
the where query to Print all records (*).

I have three combo boxes so there are 8 possible where clauses. Is there an
easier way to code this then writing an 8 tiered IF statement. Can I just
assign * to the field if it is nul?

Here is my current statement - assuming the user has entered a criteria in
each, it works.
strWhere = "[Country] = '" & Me!cmb_Country & "' and [Region] = '" &
Me!cmb_Region & "' and [ClientSector] = '" & Me!cmb_Sector & "'"

Thanks Again
AC

Perfect - Thank you.

One additional question though, is how do I manage the Null values. If the
user does not select any values in the drop down.

Thanks Again
AC

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

It depends.
What do you want to happen if nothing has been entered?
Print all records? Print nothing?
Show a message requesting an entry?

You would first decide what you want to happen, then you can check for
no combo entry by using:
If IsNull(Me![cmb_Country]) Or IsNull(Me![cmb_Region]) Then
Do this
Else
strWhere = "Etc."
End If

 

[John Spencer (MVP)]

You can change the = operator to the Like operator and use the wildcard "*" when
the combobox is Null. This will work, BUT if any field contains a null value
that will not be a match and your query will not pick up those records.


A better Way (UNTESTED SAMPLE AIRCODE)
Dim strWhere as String

If IsNull(Me.Cmb_Country) = False Then
StrWhere= strWhere & " AND [Country] = '" & Me!cmb_Country & "'"
End If

If IsNull(Me.cmbRegion) =False then
StrWhere= strWhere & " AND [Region] = '" & Me.cmb_Region & "'"
End If

If isNull(Me.cmbSector) = False then
StrWhere= strWhere & " AND [ClientSector] = '" & Me!cmb_Sector & "'"
End if

If Len(StrWhere)> 0 then strWhere = Mid(StrWhere,5)
DoCmd.OpenReport "rpt_New_Bus_Wins_by_Sector", PrintMode, strWhere
Else
DoCmd.OpenReport "rpt_New_Bus_Wins_by_Sector", PrintMode
End If

Sorry you lost me on this one.

If the user does not enter anything into the combo boxes, then I would like
the where query to Print all records (*).

I have three combo boxes so there are 8 possible where clauses. Is there an
easier way to code this then writing an 8 tiered IF statement. Can I just
assign * to the field if it is nul?

Here is my current statement - assuming the user has entered a criteria in
each, it works.
strWhere = "[Country] = '" & Me!cmb_Country & "' and [Region] = '" &
Me!cmb_Region & "' and [ClientSector] = '" & Me!cmb_Sector & "'"

Thanks Again
AC

Perfect - Thank you.

One additional question though, is how do I manage the Null values. If the
user does not select any values in the drop down.

Thanks Again
AC

:

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

It depends.
What do you want to happen if nothing has been entered?
Print all records? Print nothing?
Show a message requesting an entry?

You would first decide what you want to happen, then you can check for
no combo entry by using:
If IsNull(Me![cmb_Country]) Or IsNull(Me![cmb_Region]) Then
Do this
Else
strWhere = "Etc."
End If

 

[Amour]

I keep getting Syntax error(missing operator) in wuery expression '(And
[Skil1] = 'name')'
on strWhereCountry = strWhereCountry & " AND [Country] = '" & Me!cmb_Country
& "'"
only a little different
Dim qrystring As String
Dim fieldresult As String
Dim totalnumber As Integer
Dim reportname As String
reportname = Me!reptname
printit = True
If IsNull(Forms!frmExperienceFilter!frmPersExperience.Form!Combo2) Then
MsgBox "You need to pick at least one Skills Set!"
Forms!frmExperienceFilter!frmPersExperience.Form!Combo2.SetFocus
printit = False
Else
filtexper1 =
Forms!frmExperienceFilter!frmPersExperience.Form!Combo2
qrystring = qrystring & "' AND [Skil1] = '" & filtexper1 & "'"
If Not
IsNull(Forms!frmExperienceFilter!frmPersExperience.Form!Combo3) Then
filtexper2 =
Forms!frmExperienceFilter!frmPersExperience.Form!Combo3
qrystring = "[Skil2] = '" & filtexper1 & "' And [Skil2] = '" &
filtexper2 & "'"
End If
End If
If printit Then
DoCmd.Close
DoCmd.OpenReport reportname, A_PREVIEW, , qrystring
End If

Skil1 is a field on a report
basicily I have 8 Combo boxes where a user picks one in each Combo box and
clicks on the print button which sends that field(s) to a report for
printing. the report has 8 seperate fields inwhich I want the values in the
Combo box to display. I also am having problems with the fields in the
report a parameter box keeps coming up how do I stop this.
Please can someone help me!!
Thank You for any help!!

 

[Eric_G]

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'"

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

 

[Ken Snell \(MVP\)]

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>

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'"

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

 

[Eric_G]

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.

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>

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'"

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

 

[George Nicholson]

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,

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.

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>

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'"



:

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