I have a form with chkboxes for each field of the table to be queried as well as common operators (=, <, >, ect...) and then a txtbox where the user can enter what they want to query for. I can extract all of the info from the form but i am not sure how to go about making the query. I know that I can call a function as a parameter to the query. I have tried passing the whole query string to the function and have the query use that to no avail. For example in the criteria box of the query builder i put MyFunction(). The function returns for example...PartNumber > 1234567 or > 1234567. I can do either. This is not bringing up any records because as far as I can see, the query is trying to find PartNumber = > 1234567. How can i fix this? I would like the user to be able to select how the query is run with out teaching them to use the query builder in access. Any help is greatly appreciated. Thanks
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Please help with query problem! 2
- Thread starter pingman
- Start date
-
2
- #2
JerryDennison
IS-IT--Management
Well, you certainly have bitten off a good sized chunk to chew on. ;-)
I'm going to assume that you'd rather present a report to your user rather than just the raw query. If so then you're better off building a filter for the report's underlying recordset. This gives you far greater control over the records returned in the report and let's you give your user almost complete control over the criteria they'd like to use.
Now the fun part, how to accomplish this. I usually create an unbound form from which the user can choose the report they wish to run and any criteria choices I think they will want or need. These choices come in many different styles. One that is popular is a multiselect listbox. The listbox might have for example all the Cities available. The user can then select one or more of these cities to be queried on. Other choices might be ranges for number fields. Your choices are only limited by your imagination. And the fun part is how relatively easy it is to build the filter.
A filter is the WHERE clause of a SQL statement without the word where. An example would be "[City] = 'Chattanooga'".
This would limit the underlying query to only those record's whose City field contained the word Chattanooga. To then limit the recordset to that or Dalton the clause would look like this: "[City] = 'Chattanooga' OR [City] = 'Dalton'". To build this in code and to open the report using this it would look something like this:
...
Dim strSQL As String
Dim strDoc As String
strDoc = "rptReportName"
strSQL = "[City] = 'Chattanooga' OR [City] = '[Dalton]'"
DoCmd.OpenReport strDoc, acViewPreview, , strSQL
...
That's all there is to it. Now to build the SQL using "live" data you need only substitute the appropriate controlname. For text type data you should always enclose the criteria in single quotes. For numeric data this isn't necessary.
strSQL = "[City] = " & Chr(39) & Me.cboCity & Chr(39)
The above limits the search to a single city based on the user's choice from a combobox. To allow the user to select more than one city, you'll need to use a multiselect listbox and then iterate through the choices select and build the SQL. The following code takes the choices made in a list box and concatenates them to the necessary where sql statement and assigns it to a hidden control which is passed to the report when it's opened:
...
Dim sWhere As String ' Creates the WhereCondition for the Report
Dim RemoveOR As Integer ' Counts LEN of sWhere String for Removal of last OR statement
Const QUOTE = """"
sWhere = "[City] = " & QUOTE
Me.txtBuildSQL = Null
' Print the list of selected items to the text
' box txtSelected.
Dim varItem As Variant
Dim strList As String
With lstCompanies
' varItem returns a row number.
For Each varItem In .ItemsSelected
sWhere = sWhere & .Column(1, varItem) & QUOTE & " OR [City] = " & QUOTE
strList = strList & .Column(1, varItem) & vbCrLf
Next varItem
RemoveOR = Len(sWhere)
sWhere = Left(sWhere, (RemoveOR - 19))
Me.txtSelected = strList
Me.txtBuildSQL = sWhere
End With
...
I'm going to assume that you'd rather present a report to your user rather than just the raw query. If so then you're better off building a filter for the report's underlying recordset. This gives you far greater control over the records returned in the report and let's you give your user almost complete control over the criteria they'd like to use.
Now the fun part, how to accomplish this. I usually create an unbound form from which the user can choose the report they wish to run and any criteria choices I think they will want or need. These choices come in many different styles. One that is popular is a multiselect listbox. The listbox might have for example all the Cities available. The user can then select one or more of these cities to be queried on. Other choices might be ranges for number fields. Your choices are only limited by your imagination. And the fun part is how relatively easy it is to build the filter.
A filter is the WHERE clause of a SQL statement without the word where. An example would be "[City] = 'Chattanooga'".
This would limit the underlying query to only those record's whose City field contained the word Chattanooga. To then limit the recordset to that or Dalton the clause would look like this: "[City] = 'Chattanooga' OR [City] = 'Dalton'". To build this in code and to open the report using this it would look something like this:
...
Dim strSQL As String
Dim strDoc As String
strDoc = "rptReportName"
strSQL = "[City] = 'Chattanooga' OR [City] = '[Dalton]'"
DoCmd.OpenReport strDoc, acViewPreview, , strSQL
...
That's all there is to it. Now to build the SQL using "live" data you need only substitute the appropriate controlname. For text type data you should always enclose the criteria in single quotes. For numeric data this isn't necessary.
strSQL = "[City] = " & Chr(39) & Me.cboCity & Chr(39)
The above limits the search to a single city based on the user's choice from a combobox. To allow the user to select more than one city, you'll need to use a multiselect listbox and then iterate through the choices select and build the SQL. The following code takes the choices made in a list box and concatenates them to the necessary where sql statement and assigns it to a hidden control which is passed to the report when it's opened:
...
Dim sWhere As String ' Creates the WhereCondition for the Report
Dim RemoveOR As Integer ' Counts LEN of sWhere String for Removal of last OR statement
Const QUOTE = """"
sWhere = "[City] = " & QUOTE
Me.txtBuildSQL = Null
' Print the list of selected items to the text
' box txtSelected.
Dim varItem As Variant
Dim strList As String
With lstCompanies
' varItem returns a row number.
For Each varItem In .ItemsSelected
sWhere = sWhere & .Column(1, varItem) & QUOTE & " OR [City] = " & QUOTE
strList = strList & .Column(1, varItem) & vbCrLf
Next varItem
RemoveOR = Len(sWhere)
sWhere = Left(sWhere, (RemoveOR - 19))
Me.txtSelected = strList
Me.txtBuildSQL = sWhere
End With
...
- Thread starter
- #3
JerryDennison -
I am trying to do something similar to the question posted in this thread, but with a twist.
I want to have a pop-up form that first allows you to select a value from a combo box. Then, on AfterUpdate, a listbox is filtered to only display records that are associated with the first value (in the Combobox). The user can either filter the report (thus, displaying a report that shows all records that are associated with the first value selected) or select record(s) that they want via a multiple select listbox.
Do you have any ideas on how I would combine these two elements into a functioning filter?
Thank you, in advance.
I am trying to do something similar to the question posted in this thread, but with a twist.
I want to have a pop-up form that first allows you to select a value from a combo box. Then, on AfterUpdate, a listbox is filtered to only display records that are associated with the first value (in the Combobox). The user can either filter the report (thus, displaying a report that shows all records that are associated with the first value selected) or select record(s) that they want via a multiple select listbox.
Do you have any ideas on how I would combine these two elements into a functioning filter?
Thank you, in advance.
- Status
Similar threads
- Question