Passing a string text from a list box to a variable

[andrew hudson via AccessMonster.com]

Hi....I am slowly cracking up....

I have a form with six list boxes on. Four of the list boxes contain numeric data and passing this info to a variable isn't a problem. Two of the list boxes contain strings and I cannot pass the selected string to a variable. For example in the subroutine below I am trying to capture the selected item in the listbox LstLeague to the variable strLeague and then use this variable in an embedded SQL statement to update a new table. This however is not working. Anyone any ideas?

Private Sub btnUpdate_Click()
Dim mySQL As String
Dim strAge As Variant
Dim strLeague As Variant
'Dim strLevel As Variant
'Dim strPos As Variant



Set strAge = Forms!PlayerStats.[LstAge]
Set strLeague = Forms!PlayerStats.[LstLeague]
mySQL = mySQL + "Select *"
mySQL = mySQL + " INTO PlayerSummaryTable"
mySQL = mySQL + " FROM PlayerStats"
mySQL = mySQL + " WHERE PlayerAge = " & strAge & "AND leaguename =" & Chr(34) & strLeague & Chr(34) & ";"

 

[Nikos Yannacopoulos]

Andy,

Chr(34) returns double quote, whereas what you must use to denote string in
an SQL expression in VBA is single quote. Change the last line of the
snippet to:

mySQL = mySQL + " WHERE PlayerAge = " & strAge & "AND leaguename = '" &
strLeague & "';"

HTH,
Nikos

Hi....I am slowly cracking up....

I have a form with six list boxes on. Four of the list boxes contain

numeric data and passing this info to a variable isn't a problem. Two of the
list boxes contain strings and I cannot pass the selected string to a
variable. For example in the subroutine below I am trying to capture the
selected item in the listbox LstLeague to the variable strLeague and then
use this variable in an embedded SQL statement to update a new table. This
however is not working. Anyone any ideas?

Private Sub btnUpdate_Click()
Dim mySQL As String
Dim strAge As Variant
Dim strLeague As Variant
'Dim strLevel As Variant
'Dim strPos As Variant



Set strAge = Forms!PlayerStats.[LstAge]
Set strLeague = Forms!PlayerStats.[LstLeague]
mySQL = mySQL + "Select *"
mySQL = mySQL + " INTO PlayerSummaryTable"
mySQL = mySQL + " FROM PlayerStats"
mySQL = mySQL + " WHERE PlayerAge = " & strAge & "AND leaguename =" &

Chr(34) & strLeague & Chr(34) & ";"

 

[Marshall Barton]

I have a form with six list boxes on. Four of the list boxes contain numeric data and passing this info to a variable isn't a problem. Two of the list boxes contain strings and I cannot pass the selected string to a variable. For example in the subroutine below I am trying to capture the selected item in the listbox LstLeague to the variable strLeague and then use this variable in an embedded SQL statement to update a new table. This however is not working. Anyone any ideas?

Private Sub btnUpdate_Click()
Dim mySQL As String
Dim strAge As Variant
Dim strLeague As Variant
'Dim strLevel As Variant
'Dim strPos As Variant



Set strAge = Forms!PlayerStats.[LstAge]
Set strLeague = Forms!PlayerStats.[LstLeague]
mySQL = mySQL + "Select *"
mySQL = mySQL + " INTO PlayerSummaryTable"
mySQL = mySQL + " FROM PlayerStats"
mySQL = mySQL + " WHERE PlayerAge = " & strAge & "AND leaguename =" & Chr(34) & strLeague & Chr(34) & ";"

What you have is logically correct, except for a missing
space before AND.

When you have trouble with a constructed SQL statement, it's
a good debugging technique to use Debug.Print or a msg box
to display the whole statement. Things like a missing space
are easy to spot when you can see the result of all those
concatenations.

I recommend using & as the preferred concatenation operator.
+ has other uses and I always have to do a double take to
make sure that there's no chance of an addition occuring or
if there some kind of subtle Null propogation involved.

 

[andrew hudson via AccessMonster.com]

The Klondike....success.

Initially it didn't pick up any data at all - I think it was the way my list box was set up because when I manually added the list ...it work. Thanks a million.