Using Code to build criteria

[FBxiii]

Hi.

I have got a form where the user can select various options for a report.

One option I want is for them to be able to select either All Customers or
just ones selected from a list box.

I have created some code to generate an In statement using the selected
customers. This works fine.

I am trying to apply the criteria of:

Field: Customer Criteria: [forms]![frmSAR_Statistics]![cmbCustomer] = "All
Customers" OR Get_Customer_List()

Get_Customer_List is the function I have created to return the list -
In("Customer1","Customer2")

If I run the query when All Customers is selected, it works fine.

When I select certain customers, the query comes back blank. I am
definitely selecting customers who appear on the All Customers report.

Can anyone help with this?

Cheers,
Steve.

 

[Jeanette Cunningham]

Hi,
this SQL returns records
SELECT tblContacts.ContactID, tblContacts.CompanyID, tblContacts.LName
FROM tblContacts
WHERE (((tblContacts.LName) In ("Brown")));

Would you be willing to paste the SQL for the query that returns all
customers and the SQL for the query that returns blank, then maybe we can
make suggestions.

Jeanette Cunningham

 

[FBxiii]

It is the same query. It is the reason why my criteria for the customer
field does not work that I need an answer for.

Thanks anyway,
Steve.

Hi,
this SQL returns records
SELECT tblContacts.ContactID, tblContacts.CompanyID, tblContacts.LName
FROM tblContacts
WHERE (((tblContacts.LName) In ("Brown")));

Would you be willing to paste the SQL for the query that returns all
customers and the SQL for the query that returns blank, then maybe we can
make suggestions.

Jeanette Cunningham

Hi.

I have got a form where the user can select various options for a report.

One option I want is for them to be able to select either All Customers or
just ones selected from a list box.

I have created some code to generate an In statement using the selected
customers. This works fine.

I am trying to apply the criteria of:

Field: Customer Criteria: [forms]![frmSAR_Statistics]![cmbCustomer] = "All
Customers" OR Get_Customer_List()

Get_Customer_List is the function I have created to return the list -
In("Customer1","Customer2")

If I run the query when All Customers is selected, it works fine.

When I select certain customers, the query comes back blank. I am
definitely selecting customers who appear on the All Customers report.

Can anyone help with this?

Cheers,
Steve.

 

[Jeanette Cunningham]

What does the SQL statement look like after the code has created the SQL
string.
In your code you will have a line like
strSQL = (your query string here) and following this line put
debug.print strSQL

Use your form to select from the combo each time.
You need to do this for both cases- the case where the user chooses all
customers and where they choose a couple of customers.
Access will form the strSQL
Look in the immediate window to see what Access got for strSQL, this will
give the answer to why in all customers it works and why for selected
customers it doesn't.

Jeanette Cunningham

It is the same query. It is the reason why my criteria for the customer
field does not work that I need an answer for.

Thanks anyway,
Steve.

Hi,
this SQL returns records
SELECT tblContacts.ContactID, tblContacts.CompanyID, tblContacts.LName
FROM tblContacts
WHERE (((tblContacts.LName) In ("Brown")));

Would you be willing to paste the SQL for the query that returns all
customers and the SQL for the query that returns blank, then maybe we can
make suggestions.

Jeanette Cunningham

Hi.

I have got a form where the user can select various options for a
report.

One option I want is for them to be able to select either All Customers
or
just ones selected from a list box.

I have created some code to generate an In statement using the selected
customers. This works fine.

I am trying to apply the criteria of:

Field: Customer Criteria: [forms]![frmSAR_Statistics]![cmbCustomer] =
"All
Customers" OR Get_Customer_List()

Get_Customer_List is the function I have created to return the list -
In("Customer1","Customer2")

If I run the query when All Customers is selected, it works fine.

When I select certain customers, the query comes back blank. I am
definitely selecting customers who appear on the All Customers report.

Can anyone help with this?

Cheers,
Steve.

 

[Ofer Cohen]

You can't send a criteria to query that includes few values, like
In (1,2,3)
In that case the condition will look for a string that has "In (1,2,3)" as a
full string and not just the values inside the brackets.

What you can do is, change the SQL that the report is based on with the new
criteia, and then run the report.

Look at this link on "Using a Microsoft Access Listbox to pass criteria to a
query"

http://www.databasedev.co.uk/query_using_listbox.html

In that example, instead of openning the query run the report

--
Good Luck
BS"D

It is the same query. It is the reason why my criteria for the customer
field does not work that I need an answer for.

Thanks anyway,
Steve.

Hi,
this SQL returns records
SELECT tblContacts.ContactID, tblContacts.CompanyID, tblContacts.LName
FROM tblContacts
WHERE (((tblContacts.LName) In ("Brown")));

Would you be willing to paste the SQL for the query that returns all
customers and the SQL for the query that returns blank, then maybe we can
make suggestions.

Jeanette Cunningham

Hi.

I have got a form where the user can select various options for a report.

One option I want is for them to be able to select either All Customers or
just ones selected from a list box.

I have created some code to generate an In statement using the selected
customers. This works fine.

I am trying to apply the criteria of:

Field: Customer Criteria: [forms]![frmSAR_Statistics]![cmbCustomer] = "All
Customers" OR Get_Customer_List()

Get_Customer_List is the function I have created to return the list -
In("Customer1","Customer2")

If I run the query when All Customers is selected, it works fine.

When I select certain customers, the query comes back blank. I am
definitely selecting customers who appear on the All Customers report.

Can anyone help with this?

Cheers,
Steve.

 

[FBxiii]

Thanks for the replies guys.

I will look at re-building the query instead (in the case of multiple
customers being selected).

Cheers,
Steve.

You can't send a criteria to query that includes few values, like
In (1,2,3)
In that case the condition will look for a string that has "In (1,2,3)" as a
full string and not just the values inside the brackets.

What you can do is, change the SQL that the report is based on with the new
criteia, and then run the report.

Look at this link on "Using a Microsoft Access Listbox to pass criteria to a
query"

http://www.databasedev.co.uk/query_using_listbox.html

In that example, instead of openning the query run the report

--
Good Luck
BS"D

It is the same query. It is the reason why my criteria for the customer
field does not work that I need an answer for.

Thanks anyway,
Steve.

Hi,
this SQL returns records
SELECT tblContacts.ContactID, tblContacts.CompanyID, tblContacts.LName
FROM tblContacts
WHERE (((tblContacts.LName) In ("Brown")));

Would you be willing to paste the SQL for the query that returns all
customers and the SQL for the query that returns blank, then maybe we can
make suggestions.

Jeanette Cunningham

Hi.

I have got a form where the user can select various options for a report.

One option I want is for them to be able to select either All Customers or
just ones selected from a list box.

I have created some code to generate an In statement using the selected
customers. This works fine.

I am trying to apply the criteria of:

Field: Customer Criteria: [forms]![frmSAR_Statistics]![cmbCustomer] = "All
Customers" OR Get_Customer_List()

Get_Customer_List is the function I have created to return the list -
In("Customer1","Customer2")

If I run the query when All Customers is selected, it works fine.

When I select certain customers, the query comes back blank. I am
definitely selecting customers who appear on the All Customers report.

Can anyone help with this?

Cheers,
Steve.

 

[Klatuu]

You should have only one query for the record source of a report. It should
have no filtering at all. Use the Where argument of the OpenReport method to
do the filtering. Here is a function designed to use a multi select list
box to filter a report:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

--
Dave Hargis, Microsoft Access MVP

Thanks for the replies guys.

I will look at re-building the query instead (in the case of multiple
customers being selected).

Cheers,
Steve.

You can't send a criteria to query that includes few values, like
In (1,2,3)
In that case the condition will look for a string that has "In (1,2,3)" as a
full string and not just the values inside the brackets.

What you can do is, change the SQL that the report is based on with the new
criteia, and then run the report.

Look at this link on "Using a Microsoft Access Listbox to pass criteria to a
query"

http://www.databasedev.co.uk/query_using_listbox.html

In that example, instead of openning the query run the report

--
Good Luck
BS"D

It is the same query. It is the reason why my criteria for the customer
field does not work that I need an answer for.

Thanks anyway,
Steve.




:

Hi,
this SQL returns records
SELECT tblContacts.ContactID, tblContacts.CompanyID, tblContacts.LName
FROM tblContacts
WHERE (((tblContacts.LName) In ("Brown")));

Would you be willing to paste the SQL for the query that returns all
customers and the SQL for the query that returns blank, then maybe we can
make suggestions.

Jeanette Cunningham

Hi.

I have got a form where the user can select various options for a report.

One option I want is for them to be able to select either All Customers or
just ones selected from a list box.

I have created some code to generate an In statement using the selected
customers. This works fine.

I am trying to apply the criteria of:

Field: Customer Criteria: [forms]![frmSAR_Statistics]![cmbCustomer] = "All
Customers" OR Get_Customer_List()

Get_Customer_List is the function I have created to return the list -
In("Customer1","Customer2")

If I run the query when All Customers is selected, it works fine.

When I select certain customers, the query comes back blank. I am
definitely selecting customers who appear on the All Customers report.

Can anyone help with this?

Cheers,
Steve.

 

[FBxiii]

Thats a bit more similar to my solution!

I have placed the function into my form and it creates the In statement
fine. The report however, still shows all Customers.

How do I apply the filter to the report?

You should have only one query for the record source of a report. It should
have no filtering at all. Use the Where argument of the OpenReport method to
do the filtering. Here is a function designed to use a multi select list
box to filter a report:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

--
Dave Hargis, Microsoft Access MVP

Thanks for the replies guys.

I will look at re-building the query instead (in the case of multiple
customers being selected).

Cheers,
Steve.

You can't send a criteria to query that includes few values, like
In (1,2,3)
In that case the condition will look for a string that has "In (1,2,3)" as a
full string and not just the values inside the brackets.

What you can do is, change the SQL that the report is based on with the new
criteia, and then run the report.

Look at this link on "Using a Microsoft Access Listbox to pass criteria to a
query"

http://www.databasedev.co.uk/query_using_listbox.html

In that example, instead of openning the query run the report

--
Good Luck
BS"D


:

It is the same query. It is the reason why my criteria for the customer
field does not work that I need an answer for.

Thanks anyway,
Steve.




:

Hi,
this SQL returns records
SELECT tblContacts.ContactID, tblContacts.CompanyID, tblContacts.LName
FROM tblContacts
WHERE (((tblContacts.LName) In ("Brown")));

Would you be willing to paste the SQL for the query that returns all
customers and the SQL for the query that returns blank, then maybe we can
make suggestions.

Jeanette Cunningham

Hi.

I have got a form where the user can select various options for a report.

One option I want is for them to be able to select either All Customers or
just ones selected from a list box.

I have created some code to generate an In statement using the selected
customers. This works fine.

I am trying to apply the criteria of:

Field: Customer Criteria: [forms]![frmSAR_Statistics]![cmbCustomer] = "All
Customers" OR Get_Customer_List()

Get_Customer_List is the function I have created to return the list -
In("Customer1","Customer2")

If I run the query when All Customers is selected, it works fine.

When I select certain customers, the query comes back blank. I am
definitely selecting customers who appear on the All Customers report.

Can anyone help with this?

Cheers,
Steve.

 

[Klatuu]

Use the Where argument of the OpenReport method:

Dim strWhere as String

strWhere = BuildWhereCondition(Me.MyListBox)
If Len(strWhere) > 0 Then
strWhere = "[CustID] " & strWhere
DoCmd.OpenReport "MyReport", , , strWhere
--
Dave Hargis, Microsoft Access MVP

Thats a bit more similar to my solution!

I have placed the function into my form and it creates the In statement
fine. The report however, still shows all Customers.

How do I apply the filter to the report?

You should have only one query for the record source of a report. It should
have no filtering at all. Use the Where argument of the OpenReport method to
do the filtering. Here is a function designed to use a multi select list
box to filter a report:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

--
Dave Hargis, Microsoft Access MVP

Thanks for the replies guys.

I will look at re-building the query instead (in the case of multiple
customers being selected).

Cheers,
Steve.


:

You can't send a criteria to query that includes few values, like
In (1,2,3)
In that case the condition will look for a string that has "In (1,2,3)" as a
full string and not just the values inside the brackets.

What you can do is, change the SQL that the report is based on with the new
criteia, and then run the report.

Look at this link on "Using a Microsoft Access Listbox to pass criteria to a
query"

http://www.databasedev.co.uk/query_using_listbox.html

In that example, instead of openning the query run the report

--
Good Luck
BS"D


:

It is the same query. It is the reason why my criteria for the customer
field does not work that I need an answer for.

Thanks anyway,
Steve.




:

Hi,
this SQL returns records
SELECT tblContacts.ContactID, tblContacts.CompanyID, tblContacts.LName
FROM tblContacts
WHERE (((tblContacts.LName) In ("Brown")));

Would you be willing to paste the SQL for the query that returns all
customers and the SQL for the query that returns blank, then maybe we can
make suggestions.

Jeanette Cunningham

Hi.

I have got a form where the user can select various options for a report.

One option I want is for them to be able to select either All Customers or
just ones selected from a list box.

I have created some code to generate an In statement using the selected
customers. This works fine.

I am trying to apply the criteria of:

Field: Customer Criteria: [forms]![frmSAR_Statistics]![cmbCustomer] = "All
Customers" OR Get_Customer_List()

Get_Customer_List is the function I have created to return the list -
In("Customer1","Customer2")

If I run the query when All Customers is selected, it works fine.

When I select certain customers, the query comes back blank. I am
definitely selecting customers who appear on the All Customers report.

Can anyone help with this?

Cheers,
Steve.

 

[FBxiii]

Cheers Klatuu, that works now!

The Build function was not storing strWhere to pass back to the form which
is why it wasnt working I think! I added BuildWhereFunction = strWhere after
the SELECT CASE as well as the additional code and it works perfectly.

Thanks to all for your advice.
Steve.

Use the Where argument of the OpenReport method:

Dim strWhere as String

strWhere = BuildWhereCondition(Me.MyListBox)
If Len(strWhere) > 0 Then
strWhere = "[CustID] " & strWhere
DoCmd.OpenReport "MyReport", , , strWhere
--
Dave Hargis, Microsoft Access MVP

Thats a bit more similar to my solution!

I have placed the function into my form and it creates the In statement
fine. The report however, still shows all Customers.

How do I apply the filter to the report?

You should have only one query for the record source of a report. It should
have no filtering at all. Use the Where argument of the OpenReport method to
do the filtering. Here is a function designed to use a multi select list
box to filter a report:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

--
Dave Hargis, Microsoft Access MVP


:

Thanks for the replies guys.

I will look at re-building the query instead (in the case of multiple
customers being selected).

Cheers,
Steve.


:

You can't send a criteria to query that includes few values, like
In (1,2,3)
In that case the condition will look for a string that has "In (1,2,3)" as a
full string and not just the values inside the brackets.

What you can do is, change the SQL that the report is based on with the new
criteia, and then run the report.

Look at this link on "Using a Microsoft Access Listbox to pass criteria to a
query"

http://www.databasedev.co.uk/query_using_listbox.html

In that example, instead of openning the query run the report

--
Good Luck
BS"D


:

It is the same query. It is the reason why my criteria for the customer
field does not work that I need an answer for.

Thanks anyway,
Steve.




:

Hi,
this SQL returns records
SELECT tblContacts.ContactID, tblContacts.CompanyID, tblContacts.LName
FROM tblContacts
WHERE (((tblContacts.LName) In ("Brown")));

Would you be willing to paste the SQL for the query that returns all
customers and the SQL for the query that returns blank, then maybe we can
make suggestions.

Jeanette Cunningham

Hi.

I have got a form where the user can select various options for a report.

One option I want is for them to be able to select either All Customers or
just ones selected from a list box.

I have created some code to generate an In statement using the selected
customers. This works fine.

I am trying to apply the criteria of:

Field: Customer Criteria: [forms]![frmSAR_Statistics]![cmbCustomer] = "All
Customers" OR Get_Customer_List()

Get_Customer_List is the function I have created to return the list -
In("Customer1","Customer2")

If I run the query when All Customers is selected, it works fine.

When I select certain customers, the query comes back blank. I am
definitely selecting customers who appear on the All Customers report.

Can anyone help with this?

Cheers,
Steve.