Using Number instead of Character in SQL Criteria

[nxqviet]

Hi all,

I have a very simple question, but I just can't get it right after many
tries. I have this code, this is a part of a very long code that allow
you to select items from a list box and use them as a criteria for an
SQL statement.

This code uses the character in the list box rather than number ( i'm
refering to the Chr(34) portion of the two lines marked with stars). I
need to use number instead. I tried many time but can't get it rught.

Also, I do not fully understand this code, since it was developed with
a help of a friend of mine. Can someone explain for me what this line
do in the code? [strCriteria = Left(strCriteria, Len(strCriteria) -
3)]

---------------------------
If Me!lstList.ItemsSelected.Count > 0 Then

For Each varItem In Me!lstList.ItemsSelected
strCriteria = strCriteria & "tblTemp.ProductName = " & Chr(34) _
'*************
& Me!lstList.ItemData(varItem) & Chr(34) & "OR
" '*************
Next varItem

strCriteria = Left(strCriteria, Len(strCriteria) - 3)

Else

Msgbox "Must Select An Item From The List First"

Exit Sub

End If

 

[Van T. Dinh]

Try:

For Each varItem In Me!lstList.ItemsSelected
strCriteria = strCriteria & " tblTemp.ProductName = " & Chr(34) & _
Me!lstList.ItemData(varItem) & Chr(34) & " OR"

***Note the extra space character just before tblTemp and OR!

strCriteria = Left(strCriteria, Len(strCriteria) - 3)

removes the lat 3 characters, i.e. the " OR" at the end of the constructed
String.

Maybe a better construction is shown at:

http://www.mvps.org/access/forms/frm0007.htm

 

[nxqviet]

Van

The code I posted actually works. But it is using character rather than
number in the strCriteria. I need to switch to Number, replacing the
"Char(34)" with something else so that the the query can run. In other
word, this code inserting a text string as a criteria for a record
column with a "number" format.

Thanks anyway, I'm still trying, I think i'm close to getting it right.

V_

Try:

For Each varItem In Me!lstList.ItemsSelected
strCriteria = strCriteria & " tblTemp.ProductName = " & Chr(34) & _
Me!lstList.ItemData(varItem) & Chr(34) & " OR"

***Note the extra space character just before tblTemp and OR!

strCriteria = Left(strCriteria, Len(strCriteria) - 3)

removes the lat 3 characters, i.e. the " OR" at the end of the constructed
String.

Maybe a better construction is shown at:

http://www.mvps.org/access/forms/frm0007.htm

--
HTH
Van T. Dinh
MVP (Access)

Hi all,

I have a very simple question, but I just can't get it right after many
tries. I have this code, this is a part of a very long code that allow
you to select items from a list box and use them as a criteria for an
SQL statement.

This code uses the character in the list box rather than number ( i'm
refering to the Chr(34) portion of the two lines marked with stars). I
need to use number instead. I tried many time but can't get it rught.

Also, I do not fully understand this code, since it was developed with
a help of a friend of mine. Can someone explain for me what this line
do in the code? [strCriteria = Left(strCriteria, Len(strCriteria) -
3)]

---------------------------
If Me!lstList.ItemsSelected.Count > 0 Then

For Each varItem In Me!lstList.ItemsSelected
strCriteria = strCriteria & "tblTemp.ProductName = " & Chr(34) _
'*************
& Me!lstList.ItemData(varItem) & Chr(34) & "OR
" '*************
Next varItem

strCriteria = Left(strCriteria, Len(strCriteria) - 3)

Else

Msgbox "Must Select An Item From The List First"

Exit Sub

End If
-----------------------------------------------------

Thanks,

V

 

[RoyVidar]

Van

The code I posted actually works. But it is using character rather
than number in the strCriteria. I need to switch to Number, replacing
the "Char(34)" with something else so that the the query can run. In
other word, this code inserting a text string as a criteria for a
record column with a "number" format.

Thanks anyway, I'm still trying, I think i'm close to getting it
right.

V_

Try:

For Each varItem In Me!lstList.ItemsSelected
strCriteria = strCriteria & " tblTemp.ProductName = " &
Chr(34) & _ Me!lstList.ItemData(varItem) & Chr(34) & "
OR"

***Note the extra space character just before tblTemp and OR!

strCriteria = Left(strCriteria, Len(strCriteria) - 3)

removes the lat 3 characters, i.e. the " OR" at the end of the
constructed String.

Maybe a better construction is shown at:

http://www.mvps.org/access/forms/frm0007.htm

--
HTH
Van T. Dinh
MVP (Access)

Hi all,

I have a very simple question, but I just can't get it right after
many tries. I have this code, this is a part of a very long code
that allow you to select items from a list box and use them as a
criteria for an SQL statement.

This code uses the character in the list box rather than number (
i'm refering to the Chr(34) portion of the two lines marked with
stars). I need to use number instead. I tried many time but can't
get it rught.

Also, I do not fully understand this code, since it was developed
with a help of a friend of mine. Can someone explain for me what
this line do in the code? [strCriteria = Left(strCriteria,
Len(strCriteria) - 3)]

---------------------------
If Me!lstList.ItemsSelected.Count > 0 Then

For Each varItem In Me!lstList.ItemsSelected
strCriteria = strCriteria & "tblTemp.ProductName = " &
Chr(34) _ '*************
& Me!lstList.ItemData(varItem) & Chr(34) &
"OR " '*************
Next varItem

strCriteria = Left(strCriteria, Len(strCriteria) - 3)

Else

Msgbox "Must Select An Item From The List First"

Exit Sub

End If
-----------------------------------------------------

Thanks,

V

But "tblTemp.ProductName", does sound like a text field ;-)

Numeric fields does not need any delimiters, so if the "ProductName"
field is numeric, then

strCriteria = strCriteria & "tblTemp.ProductName = " & _
& Me!lstList.ItemData(varItem) & Chr(34) & "OR "

 

[RoyVidar]

<[email protected]>:
Forgot the last Chr(34)

strCriteria = strCriteria & "tblTemp.ProductName = " & _
& Me!lstList.ItemData(varItem) & "OR "

 

[nxqviet]

Roy,

You are right, the field in there is a text column, but I'm trying to
swith to a unique ID column, which is more accurate and eliminating the
spelling error and other problem.

Thanks, I'll give it a try right now.

V_

 

[nxqviet]

Roy, It worked perfectly, except needed the first "&" in the second
line to be deleted.

Thanks

V_