Variable Created For a Query Not Working

[SHIPP]

I create a variable in vb code. If I debug and print it it looks like so...

"'HOLD' or 'HARD HOLD' or 'AUTOHD'"

The code for creating it is sending it through a loop...

strSQL = Chr(39) & varRet & Chr(39) & " or "

and then


strSQL = Chr(34) & Mid(strSQL, 1, Len(strSQL) - 4) & Chr(34)

It does not come up with any results. However when I manually put in


"HOLD" or "HARD HOLD" or "AUTOHD"

the query runs. What am I doing wrong?

 

[Michel Walsh]

because the query designer REWRITE your manual input to the right syntax
required by SQL, which is available in SQL view, and should look like:


.... WHERE FieldName = 'Hold' OR FieldName = "hard hold' OR
fieldName = 'autoHD'


You can choose an IN construction:


.... WHERE FieldName IN( 'Hold', "Hard Hold', 'AutoHD' )





Hoping it may help,
Vanderghast, Access MVP

 

[SHIPP]

That didn't work. My complete code is as follows:

Dim varRet As Variant
Dim evarRow As Variant
Dim strSQL As String

strSQL = ""
varRet = Null

For Each evarRow In rctl.ItemsSelected
If IsNull(varRet) Then
varRet = rctl.Column(rintCol, evarRow)
strSQL = " [ingres_wms_contst_key]=" & Chr(39) & varRet & Chr(39) & "
or "
Else
varRet = rctl.Column(rintCol, evarRow)
strSQL = strSQL & " [ingres_wms_contst_key]=" & Chr(39) & varRet &
Chr(39) & " or "
End If
Next evarRow
strSQL = Chr(34) & " (WHERE" & Mid(strSQL, 1, Len(strSQL) - 4) & ")" &
Chr(34)
Debug.Print strSQL
GetSelections = strSQL

In the query under parameters I have GetSelections()

When I debug and print strSQL it returns:


" (WHERE [ingres_wms_contst_key]='HOLD' or [ingres_wms_contst_key]='HARD
HOLD')"


What am I doint wrong?

 

[SHIPP]

Problem solved. I used the basic logic from....

http://www.fontstuff.com/access/acctut19.htm
--
M. Shipp

That didn't work. My complete code is as follows:

Dim varRet As Variant
Dim evarRow As Variant
Dim strSQL As String

strSQL = ""
varRet = Null

For Each evarRow In rctl.ItemsSelected
If IsNull(varRet) Then
varRet = rctl.Column(rintCol, evarRow)
strSQL = " [ingres_wms_contst_key]=" & Chr(39) & varRet & Chr(39) & "
or "
Else
varRet = rctl.Column(rintCol, evarRow)
strSQL = strSQL & " [ingres_wms_contst_key]=" & Chr(39) & varRet &
Chr(39) & " or "
End If
Next evarRow
strSQL = Chr(34) & " (WHERE" & Mid(strSQL, 1, Len(strSQL) - 4) & ")" &
Chr(34)
Debug.Print strSQL
GetSelections = strSQL

In the query under parameters I have GetSelections()

When I debug and print strSQL it returns:


" (WHERE [ingres_wms_contst_key]='HOLD' or [ingres_wms_contst_key]='HARD
HOLD')"


What am I doint wrong?

--
M. Shipp

because the query designer REWRITE your manual input to the right syntax
required by SQL, which is available in SQL view, and should look like:


.... WHERE FieldName = 'Hold' OR FieldName = "hard hold' OR
fieldName = 'autoHD'


You can choose an IN construction:


.... WHERE FieldName IN( 'Hold', "Hard Hold', 'AutoHD' )





Hoping it may help,
Vanderghast, Access MVP