Parameter Value required after SQL update

[abroniewski]

I am passing values from a form to the criteria of a query. The value of the string strSQL shows up perfectly everytime when viewing using the debug.Print command. When looking at the Query in Design View, the search word is surrounded by square brackets, resulting in a prompt for a parameter value. If the user does not enter a search term, the query works fine.

THE CODE:

If IsNull(Me!txtSearchWord) Then
strWord = Chr(34) & "*" & Chr(34)
Else
strWord = Chr(34) & "*" & Chr(34) & " & " & Me!txtSearchWord & " & " & Chr(34) & "*" & Chr(34)
End If

strSQL = "SELECT * FROM [Table-CommitmentsMaster] " & _
"WHERE ((([Table-CommitmentsMaster]![Project Phase]) IN(" & strCriteria & ")) AND (([Table-CommitmentsMaster]![Commitment]) LIKE (" & strWord & ")));"
qdf.SQL = strSQL

DoCmd.OpenQuery "Query-Catagories"


Any help you can give is much appreciated!

 

[jdraw]

You asked for any help...

Do yourself a real favor get rid of the space in your field names, get rid of the "-" in Table-CommitmentsMaster.

Use Option Explicit in your code. That will force you to define (Dim) every variable.

eg strWord and strCriteria.

Show the Debug.Print output you refer to.

We have no idea what strCriteria is?

Within the SQL statement you use TableName.Fieldname

Good luck.

 

[boblarson]

I agree that you need to provide more about what the strCriteria is.

But, that aside, I would rewrite the current code you showed to be this:

    [B][COLOR=red]Dim strWhere As String[/COLOR][/B]
[B][COLOR=#ff0000][/COLOR][/B] 
    If [B][COLOR=red]Not[/COLOR][/B] IsNull(Me!txtSearchWord) Then
        strWord = [B][COLOR=red]Chr(34) & "*" &[/COLOR][/B] Me!txtSearchWord [B][COLOR=red]& "*" & Chr(34)[/COLOR][/B]
    End If
 
   [COLOR=red][B] If strWord <> vbNullString Then[/B][/COLOR]
        [B][COLOR=red]strWhere = [/COLOR][/B]"WHERE ((([Table-CommitmentsMaster]![Project Phase]) IN(" & strCriteria & ")) AND (([Table-CommitmentsMaster]![Commitment]) LIKE (" & strWord & ")))"
   [B][COLOR=red] Else
[/COLOR][/B]        [B][COLOR=red]strWhere = "WHERE ((([Table-CommitmentsMaster]![Project Phase]) IN(" & strCriteria & "))"[/COLOR][/B]
    [COLOR=red][B]End If
[/B][/COLOR]    
    strSql = "SELECT * FROM [Table-CommitmentsMaster] "
    [B][COLOR=red]strSql = strSql & strWhere[/COLOR][/B]
    qdf.sql = strSql
 
    DoCmd.OpenQuery "Query-Catagories"

 

[abroniewski]

Thanks for the input! To answer your questions:
1) I am currently using the Explicit option, just didn't have that code pasted.
2) strCriteria is a string formed from the selection made in a multiselect box. I had to use the column statement as there are two columns, the first has a width of zero and contains the IDKey.
3) The debug.Print statement for strSQL prints
SELECT * FROM [Table-CommitmentsMaster] WHERE ((([Table-CommitmentsMaster]![Project Phase]) IN('Notice to Proceed & Construction')) AND (([Table-CommitmentsMaster]![Commitment]) LIKE ("*" & test & "*")));
where test was the word I put in a searchbox on the form (not the word put in during parameter value prompt).

Here is the entire code:

Option Compare Database
Option Explicit

Private Sub cmdOK_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim var As Variant
Dim strCriteria As String
Dim strSQL As String
Dim word As String
Dim strWord As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Query-Catagories")

'Cycle through listbox and extract each slected term
For Each var In Me!lstProjectPhase.ItemsSelected
strCriteria = strCriteria & ",'" & lstProjectPhase.Column(1, var) & "'"
Next var
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

'Incorporating Search Term into SQL statement
If IsNull(Me!txtSearchWord) Then
strWord = Chr(34) & "*" & Chr(34)
Else
strWord = Chr(34) & "*" & Chr(34) & " & " & Me!txtSearchWord & " & " & Chr(34) & "*" & Chr(34)
End If

'Create SQL statement to be used as Criteria for Query
strSQL = "SELECT * FROM [Table-CommitmentsMaster] " & _
"WHERE ((([Table-CommitmentsMaster]![Project Phase]) IN(" & strCriteria & ")) AND (([Table-CommitmentsMaster]![Commitment]) LIKE (" & strWord & ")));"

qdf.SQL = strSQL
Debug.Print strSQL
DoCmd.OpenQuery "Query-Catagories"

Set db = Nothing
Set qdf = Nothing
End Sub


Thanks again for all of your help. This has proven to be a real tough project for me, and the end is in sight!!

 

[abroniewski]

Bob,

Are your suggestions based on best practice/ good style? The reason it is set up with the current IF structure is because I will have many more multiselect boxes to choose catagories from, and will be using IF statements to cycle through all those options.

I will make the changes you've suggested and post back!

Thanks

 

[boblarson]

Bob,

Are your suggestions based on best practice/ good style? The reason it is set up with the current IF structure is because I will have many more multiselect boxes to choose catagories from, and will be using IF statements to cycle through all those options.

I will make the changes you've suggested and post back!

Thanks

My suggestion is meant to show that you do NOT need to include a wildcard if you aren't selecting any criteria for that particular item. I used your code to show that but this is more of what I would do (this is an example from some code I did for someone else in another thread. This was for 5 listboxes but the concept is the same for text boxes with the exception of the looping and building of the In statement.):

Dim varItem As Variant
Dim strWhere   As String
Dim strITLead  As String
Dim strProgram As String
Dim strProject As String
Dim strProjDriv As String
Dim strSponsoringBanks As String
Dim strSql As String
 
' Build criteria string for GBT IT Lead
' if All is selected then we do not need the variable
If Me.lstITLead.Selected(0) <> True Then
    For Each varItem In Me.lstITLead.ItemsSelected
        strITLead = strITLead & Chr(34) & Me.lstITLead.ItemData(varItem) & Chr(34) & ","
    Next varItem
    strITLead = Left(strITLead, Len(strITLead) - 1)
    strWhere = "[GBT IT LEAD] IN(" & strITLead & ") AND "
End If

 
' Build criteria string for Program
' if ALL is selected then no need to use any variable
If Me.lstProgram.Selected(0) <> True Then
    For Each varItem In Me.lstProgram.ItemsSelected
        strProgram = strProgram & Chr(34) & Me.lstProgram.ItemData(varItem) & Chr(34) & ","
    Next varItem
    strProgram = Left(strProgram, Len(strProgram) - 1)
    strWhere = strWhere & "[Program] IN(" & strProgram & ") AND "
End If

 
' Build criteria string for Project name
If Me.lstProject.Selected(0) <> True Then
    For Each varItem In Me.lstProject.ItemsSelected
        strProject = strProject & Chr(34) & Me.lstProject.ItemData(varItem) & Chr(34) & ","
    Next varItem
    strProject = Left(strProject, Len(strProject) - 1)
    strWhere = strWhere & "[Project] IN(" & strProject & ") AND "
End If

 
' Build criteria string for Project Driver
If Me.lstProDriver.Selected(0) <> True Then
    For Each varItem In Me.lstProDriver.ItemsSelected
        strProjDriv = strProjDriv & Chr(34) & Me.lstProDriver.ItemData(varItem) & Chr(34) & ","
    Next varItem
    strProjDriv = Left(strProjDriv, Len(strProjDriv) - 1)
    strWhere = strWhere & "[Project Driver] IN(" & strProjDriv & ") AND "
End If
 
' Build criteria string for SponsoringBanks
If Me.lstSpoBk.Selected(0) <> True Then
    For Each varItem In Me.lstSpoBk.ItemsSelected
        strSponsoringBanks = strSponsoringBanks & Chr(34) & Me.lstSpoBk.ItemData(varItem) & Chr(34) & ","
    Next varItem
 
    strSponsoringBanks = Left(strSponsoringBanks, Len(strSponsoringBanks) - 1)
    strWhere = strWhere & "[Sponsoring Banks] IN(" & strSponsoringBanks & ")"
End If
 
'strip the AND off (we put one on every one of them so as to not have to test for it first)
strWhere = "WHERE " & Left(strWhere, Len(strWhere) - 5)
 
' Build SQL statement
strSql = "SELECT [GBT Project Dashboard].* FROM [GBT Project Dashboard] " & strWhere

 

[abroniewski]

Wow, this is actually exactly what I am looking for. Since the code is being used several times, and the only thing changing is the listbox, would it not be best to use a subroutine that passes the listbox as an argument? I tried doing this earlier, but ran into too many problems and have decided to go for code that just works...

 

[boblarson]

You could create a function to do it. You would have to account for different datatypes and pass back the right string to add to the Where variable.

 

[abroniewski]

I'm not too sure about the usage of the line
If Me.lstProgram.Selected(0) <> True Then
I understand this tests to see if something is selected. I read it as
"If the first item (is not equal to the second value?) Then...

What does it mean/do?

Also, if I were to just use the "For each selected item" loop, is there not an if statement built in, as in it only enters the for loop if there are selected items?

 

[boblarson]

That part is because the user I helped with that code had _ALL as the first selection in the list box and if that was the part selected then we bypassed creating the where clause part for that one because to include all of one means no filtering on it.

So, sorry - if you don't have something like that you can just check the SelectedItems to see if the SelectedItems.Count is greater than 0.