Query prompts for multiple/variable values

[nsajeff]

Hi,

I'm trying to prompt my user to enter ID's for a company. Some companies have 1 ID, some have several and we have no way of distinguishing between them.

I want my user to be prompted like such:

[Enter Exchange ID]
They enter an ID and hit enter. What can I do to get multiple values?

My SQL statement looks like this:

SELECT dbo_Exchanges.Province, dbo_Exchanges.[Name edited], mad_MadEntity.MadName, dbo_Exchanges.ExchangeID, mad_MadEntity.MadNumber INTO ExchangeIDQueryResults1
FROM dbo_Exchanges INNER JOIN mad_MadEntity ON dbo_Exchanges.[ILEC MAD] = mad_MadEntity.MadNumber
WHERE (((dbo_Exchanges.ExchangeID)=[Enter Exchange ID]));


Thanks for your help!

[orange]

What is the purpose of the ID field? How is it assigned?
What is the database/project you are working on?

[nsajeff]

What is the purpose of the ID field? How is it assigned?
What is the database/project you are working on?

The purpose of the ID field is to query several tables that I have linked. I don't understand your question of how is it assigned. It is already in the table. I just want the user to be able to enter more than one ID when prompted by my criteria in my query.

The database/project is a report generator for something confidential. I need to generate a report that pulls from several tables/queries and this is one aspect of it. Previous aspects of the project were met/done by entering one value. This part requires my user to be able to enter a variable number of values(ID's). I am unclear how to do this.

Any help is greatly appreciated. Thank you.

[nsajeff]

Note:

As of right now, my 'ExchangeIDQuery' has the following criteria, "[Enter Exchange ID]", is there a way to allow the user to enter a variable number of ids by simply separating them with a ',' or ';'? Thats essentially all I need it to do.

[nsajeff]

I think I've simplified my problem.

Would it be easier if I created a form field(textExchangeID), output/parsed the comma separated entries to a table and used an In statement in my criteria to generate my 'ExchangeIDQueryresults1' table?

If so, my next question is, how do I output the data in a textbox to a table? Thanks for your help and patience!

[nsajeff]

Hi again, not sure if I'm over-posting or going to get in trouble for multi-posting to my own thread. Just trying to preserve my train of thought for anyone with this issue.

I've gone back to my SQL reference material and refound the In statement. I've modified my criteria to
In ([Enter Exchange IDs]) and if I enter one value, it generates my table correctly. However, the moment I enter multiple tables it simple generates a blank table. Any ideas? Thanks.

[OBP]

nsajeff, it would be much better if you build this using VBA based SQL and overwrite the Query using the QueryDef function.
Do you want to do the filtering by any one of the selected Exchange IDs or by all of them at once?
As that will decide whether to use the "AND" or the "OR" in the SQL.

You could provide the user with a Multi select List box to select the multiple Exchange IDs,

[nsajeff]

nsajeff, it would be much better if you build this using VBA based SQL and overwrite the Query using the QueryDef function.
Do you want to do the filtering by any one of the selected Exchange IDs or by all of them at once?
As that will decide whether to use the "AND" or the "OR" in the SQL.

You could provide the user with a Multi select List box to select the multiple Exchange IDs,

Thanks for your feedback OBP. I've made some progress on this. I've gone about it differently.

I created a form:


Private Sub Command15_Click()
On Error GoTo Err_Command15_Click

Me.SetFocus
Text1.SetFocus
DoCmd.OpenReport "Exchanges Subreport", acViewNormal, , "ExchangeID in (" & Text1.Text & ")"


Exit_Command15_Click:
Exit Sub

Err_Command15_Click:
MsgBox Err.Description
Resume Exit_Command15_Click

End Sub

Private Sub Form_Load()

End Sub

As you can see, it is outputting to a subreport. However, I figured out that calling this subreport in my primary report does not reflect the query. What I need to do(I think) is run that form and have it output the results to a temporary table and then have my subreport call those results to populate itself. Any suggestions?

Thanks!

[OBP]

I think you would be much better off using the QueryDef technique, because it will handle as many "Inputs" as you like.
Here is an Example of using it with Multiple Multi-Selection List Boxes and Combos, unfortunately there is a lot of it as it is doing a lot of work.

Dim rs As Object, strsql As String, qdfNew As Object, var As Variant, tempquery As String
Dim sSELECT As String, sFROM As String, swhere As String, itemcount As Integer, dbs As Object
On Error GoTo errorcatch
swhere = " WHERE not isnull([Candidates ID])"
If Not IsNull(Me.Combo3) Then
swhere = swhere & " AND [Nationality] = " & Me.Combo3
End If
If Not IsNull(Me.Combo5) Then
swhere = swhere & " AND [MotherTongue] = " & Me.Combo5
End If
If Not IsNull(Me.Combo19) Then
swhere = swhere & " AND [AcademicLevel] = " & Me.Combo19
End If
If Not IsNull(Me.cboMilitaryAreaInvolved) Then
swhere = swhere & " AND [WhatMilitaryareaInvolvedin] = " & Me.cboMilitaryAreaInvolved
End If
If Not IsNull(Me.cboCountryServed) Then
swhere = swhere & " AND [CountryServedUnder?] = " & Me.cboCountryServed
End If
If Not IsNull(Me.cboPoliceRank) Then
swhere = swhere & " AND [PoliceRank] = " & Me.cboPoliceRank
End If

If Me.lstSpokenLang.ItemsSelected.Count > 0 Then
itemcount = 0
For Each var In Me.lstSpokenLang.ItemsSelected
swhere = swhere & " AND [Candidates ID] IN (SELECT [Candidates ID] FROM tblCandidatesSpokenLanguages WHERE SpokenLanguagesID = " & Me.lstSpokenLang.ItemData(var) & " )"
Next var
End If
If Me.lstWrittenLang.ItemsSelected.Count > 0 Then
For Each var In Me.lstWrittenLang.ItemsSelected
swhere = swhere & " AND [Candidates ID] IN (SELECT [Candidates ID] FROM tblCandidatesWrittenLanguages WHERE WrittenLanguagesID = " & Me.lstWrittenLang.ItemData(var) & " )"
Next var
End If
If Me.lstProfessionalExpSearch.ItemsSelected.Count > 0 Then
itemcount = 0
For Each var In Me.lstProfessionalExpSearch.ItemsSelected
swhere = swhere & " AND [Candidates ID] IN (SELECT [Candidates ID] FROM tblCandidatesProfessionalExp WHERE ProfessionalExpID = " & Me.lstProfessionalExpSearch.ItemData(var) & " )"
Next var
End If
If Me.lstRegions.ItemsSelected.Count > 0 Then
itemcount = 0
For Each var In Me.lstRegions.ItemsSelected
swhere = swhere & " AND [Candidates ID] IN (SELECT [Candidates ID] FROM tblCandidatesRegionsAndCultures WHERE RegionsandCulturesexperiencedinID = " & Me.lstRegions.ItemData(var) & " )"
Next var
End If
If Me.lstIndustrialSector.ItemsSelected.Count > 0 Then
itemcount = 0
For Each var In Me.lstIndustrialSector.ItemsSelected
swhere = swhere & " AND [Candidates ID] IN (SELECT [Candidates ID] FROM tblCandidatesIndustrialSectors WHERE IndustrialSectorID = " & Me.lstIndustrialSector.ItemData(var) & " )"
Next var
End If
If Me.lstProfessionalQual.ItemsSelected.Count > 0 Then
itemcount = 0
For Each var In Me.lstProfessionalQual.ItemsSelected
swhere = swhere & " AND [Candidates ID] IN (SELECT [Candidates ID] FROM tblCandidatesProfessionalQualifications WHERE ProfessionalQualificationsID = " & Me.lstProfessionalQual.ItemData(var) & " )"
Next var
End If
If Me.lstArmyCorpService.ItemsSelected.Count > 0 Then
itemcount = 0
For Each var In Me.lstArmyCorpService.ItemsSelected
swhere = swhere & " AND [Candidates ID] IN (SELECT [Candidates ID] FROM tblCandidatesCorpService WHERE CorpServiceID = " & Me.lstArmyCorpService.ItemData(var) & " )"
Next var
End If
If Me.lstNavyBackground.ItemsSelected.Count > 0 Then
itemcount = 0
For Each var In Me.lstNavyBackground.ItemsSelected
swhere = swhere & " AND [Candidates ID] IN (SELECT [Candidates ID] FROM tblCandidatesNavyBackground WHERE NavyBackgroundID = " & Me.lstNavyBackground.ItemData(var) & " )"
Next var
End If
If Me.lstAirForceBackground.ItemsSelected.Count > 0 Then
itemcount = 0
For Each var In Me.lstAirForceBackground.ItemsSelected
swhere = swhere & " AND [Candidates ID] IN (SELECT [Candidates ID] FROM tblCandidatesAFBackground WHERE AirForceID = " & Me.lstAirForceBackground.ItemData(var) & " )"
Next var
End If
If Me.lstMilitaryQualifications.ItemsSelected.Count > 0 Then
itemcount = 0
For Each var In Me.lstMilitaryQualifications.ItemsSelected
swhere = swhere & " AND [Candidates ID] IN (SELECT [Candidates ID] FROM tblCandidatesMilitaryQualifications WHERE MilitaryQualificationsID = " & Me.lstMilitaryQualifications.ItemData(var) & " )"
Next var
End If
If Me.lstOtherExperience.ItemsSelected.Count > 0 Then
itemcount = 0
For Each var In Me.lstOtherExperience.ItemsSelected
swhere = swhere & " AND [Candidates ID] IN (SELECT [Candidates ID] FROM tblCandidatesOtherExp WHERE OtherExperienceID = " & Me.lstOtherExperience.ItemData(var) & " )"
Next var
End If
If Me.LstRegionsServiedIn.ItemsSelected.Count > 0 Then
itemcount = 0
For Each var In Me.LstRegionsServiedIn.ItemsSelected
swhere = swhere & " AND [Candidates ID] IN (SELECT [Candidates ID] FROM tblCandidatesRegionsServedIn WHERE RegionsServedID = " & Me.LstRegionsServiedIn.ItemData(var) & " )"
Next var
End If

strsql = "SELECT [Candidates ID], [Full Name] " & _
"FROM tblCandidatesDetails " & swhere & ";"
Set dbs = Application.CurrentData
tempquery = "no"
For Each obj In dbs.AllQueries
If obj.Name = "Search Query" Then
tempquery = "yes"
End If
Next obj
If tempquery = "yes" Then
DoCmd.DeleteObject acQuery, "Search Query"
End If
With CurrentDb
Set qdfNew = .CreateQueryDef("Search Query", strsql)
End With
DoCmd.OpenForm "frmCriteriaResults"
Exit Sub
errorcatch:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

There is also a "Form Filter" version that filters the form or subform that you are using to search.


Multi List Boxes for Searching
Public Sub createFilter()
Dim strType As String
Dim strCritical As String
Dim strScope As String
Dim strRRB1 As String
Dim strRRB2 As String
Dim strArea As String
Dim strKPP
Dim strFilter As String
Dim itm As Variant

'Filter by Type
For Each itm In Me.lstFilterByType.ItemsSelected
If strType = "" Then
strType = "strRequirementType_Threshold_Objective = '" & Me.lstFilterByType.ItemData(itm) & "'"
Else
strType = strType & " OR strRequirementType_Threshold_Objective = '" & Me.lstFilterByType.ItemData(itm) & "'"
End If
Next itm
If Not strType = "" Then
strType = " (" & strType & ") AND "
End If

'Filter by Critical
For Each itm In Me.lstFilterByCritical.ItemsSelected
If strCritical = "" Then
strCritical = "blnCriticalRequirement = " & Me.lstFilterByCritical.ItemData(itm)
Else
strCritical = strCritical & " OR blnCriticalRequirement = " & Me.lstFilterByCritical.ItemData(itm)
End If
Next itm
If Not strCritical = "" Then
strCritical = "(" & strCritical & ") AND "
End If

'Filter by scope
For Each itm In Me.lstFilterByScope.ItemsSelected
If strScope = "" Then
strScope = "inScope = " & Me.lstFilterByScope.ItemData(itm)
Else
strScope = strScope & " OR inScope = " & Me.lstFilterByScope.ItemData(itm)
End If
Next itm
If Not strScope = "" Then
strScope = " (" & strScope & ") AND "
End If

'Filter by RRB1 resolution
For Each itm In Me.lstRRB1.ItemsSelected
If strRRB1 = "" Then
strRRB1 = "strResults = '" & Me.lstRRB1.ItemData(itm) & "'"
Else
strRRB1 = strRRB1 & " OR strResults = '" & Me.lstRRB1.ItemData(itm) & "'"
End If
Next itm
If Not strRRB1 = "" Then
strRRB1 = " (" & strRRB1 & ") AND "
End If

'Filter by RRB2 Resolution
For Each itm In Me.lstRRB2.ItemsSelected
If strRRB2 = "" Then
strRRB2 = "strRRB2Results = '" & Me.lstRRB2.ItemData(itm) & "'"
Else
strRRB2 = strRRB2 & " OR strRRB2Results = '" & Me.lstRRB2.ItemData(itm) & "'"
End If
Next itm
If Not strRRB2 = "" Then
strRRB2 = " (" & strRRB2 & ") AND "
End If

'Filter by KPP
For Each itm In Me.lstFilterByKPP.ItemsSelected
If strKPP = "" Then
strKPP = "isKPP = " & Me.lstFilterByKPP.ItemData(itm)
Else
strKPP = strKPP & " OR isKPP = " & Me.lstFilterByKPP.ItemData(itm)
End If
Next itm
If Not strKPP = "" Then
strKPP = "(" & strKPP & ") AND "
End If

'Filter by Area
For Each itm In Me.lstFilterByArea.ItemsSelected
If strArea = "" Then
strArea = "strFunctionalArea = '" & Me.lstFilterByArea.ItemData(itm) & "'"
Else
strArea = strArea & " OR strFunctionalArea = '" & Me.lstFilterByArea.ItemData(itm) & "'"
End If
Next itm
If Not strArea = "" Then
strArea = " (" & strArea & ") AND "
End If

strFilter = strType & strCritical & strScope & strRRB1 & strRRB2 & strKPP & strArea
If Not strFilter = "" Then
strFilter = Left(strFilter, Len(strFilter) - 5)
End If
'Debug.Print strFilter


Me.FilterOn = False
Me.Filter = ""
Me.Filter = strFilter
Me.FilterOn = True
If Me.Recordset.RecordCount = 0 Then
Me.FilterOn = False
MsgBox "No Records"
End If

[nsajeff]

Wow, thanks for those code snippets! My lack of VBA experience is really biting me in the rear right now but that stuff made sense. Thanks for your help!

[OBP]

My pleasure, if you need some explanation of what the various parts of the code do, that you would like to use, just ask.
The main point is build up the SQL string one part at a time and then send it as a QueryDef to "Create" a query.
The Syntax is critical though as VBA SQL is very unforgiving.

[nsajeff]

...
The Syntax is critical though as VBA SQL is very unforgiving.

The whole process has been unforgiving. 2 weeks ago I had only ever used Access databases with peoples user-friendly forms and output. Now I'm designing one from scratch.

As of right now I've temporarily abandoned the form until I get get my data filtering setup.

I've created a table called 'SearchValues' that I want to pass to my 'MadIDQuery' and 'ExchangeIDQuery'. 'SearchValues' has 'ExchangeID' and 'MadNumber' in it and now I just need to link those up and I might actually be close to finishing this nightmare....probably not.