SQL Parameter Query - String Problem

[ApexData]

I have an SQL statement in the RowSource of a Listbox.
I want to build the WHERE clause in code as a String as follows:

strWhereSQL = "((([T-PERS].SDX2)=[Form].[txtBestSoundex2]))"

'The following code is in my Listbox RowSource
SELECT [T-PERS].LNAME,[T-PERS].FNAME
FROM [T-PERS]
WHERE strWhereSQL
ORDER BY [T-PERS].LNAME;


When I execute the code, I'm prompted to enter a parameter value.
I believe this is occurring because of a delimeter/& issue?

Any Suggestions???

 

[Guest]

Use code like:

strWhereSQL = "[T-PERS].SDX2=""" & [Form].[txtBestSoundex2] & """ "
Me.lboMyListBox.RowSource ="SELECT [T-PERS].LNAME,[T-PERS].FNAME " & _
"FROM [T-PERS] " & _
"WHERE " & strWhereSQL & _
"ORDER BY [T-PERS].LNAME;"

 

[Ruel Cespedes via AccessMonster.com]

Your syntax is wrong. The Where Clause should like this:

strWhereSQL = "T-PERS.SDX2 = " & [Form].[txtBestSoundex2]

I recreated the ListBox and its RowSource and it works like this below:

Me.ListBox.RowSource = "SELECT T-PERS.LNAME, T-PERS.FNAME FROM T-PERS WHERE "
& strWhereSQL & " ORDER BY T-PERS.LNAME;"

I have an SQL statement in the RowSource of a Listbox.
I want to build the WHERE clause in code as a String as follows:

strWhereSQL = "((([T-PERS].SDX2)=[Form].[txtBestSoundex2]))"

'The following code is in my Listbox RowSource
SELECT [T-PERS].LNAME,[T-PERS].FNAME
FROM [T-PERS]
WHERE strWhereSQL
ORDER BY [T-PERS].LNAME;

When I execute the code, I'm prompted to enter a parameter value.
I believe this is occurring because of a delimeter/& issue?

Any Suggestions???

 

[Marshall Barton]

I have an SQL statement in the RowSource of a Listbox.
I want to build the WHERE clause in code as a String as follows:

strWhereSQL = "((([T-PERS].SDX2)=[Form].[txtBestSoundex2]))"

'The following code is in my Listbox RowSource
SELECT [T-PERS].LNAME,[T-PERS].FNAME
FROM [T-PERS]
WHERE strWhereSQL
ORDER BY [T-PERS].LNAME;


When I execute the code, I'm prompted to enter a parameter value.
I believe this is occurring because of a delimeter/& issue?

VBA variables are only available in the VBA environment.
Thye are outside the name spaces for queries and control
source expressions.

I think the simplest way to do this is to rewrite the list
box's entire row source:

Dim strSelect As String
Dim strWhere As String
Dim strOrderBy As String

strSelect = "SELECT LNAME, FNAME FROM [T-PERS] "
strWhere = "WHERE SDX2 = " & Me.txtBestSoundex2 & " "
strOrderBy = "ORDER BY LNAME, FNAME"

Me.listbox.RowSource = strSelect & strWhere & strOrderBy

 

[ApexData]

Thanks for the suggestions. This is what I wound up with and it works
for me!
I placed the entire query in a string and will do the same for the
others I will use.

strWhereSQL = "SELECT EMPID, LNAME, FNAME, MI, BADGE, ADDR1, CITY, ZIP
" & _
"FROM [T-PERS] WHERE SDX2 =
[Form].[txtBestSoundex2]" & _
"ORDER BY LNAME, FNAME, MI;"
Me.lstNames.RowSource = strWhereSQL

I never thought to use the .RowSource property in code this way. I
had the SQL statement physically sitting in the RowSource property with
a strWhere variable nested at the WHERE location, and attempting to
just pass that value. But the suggested way works real well.

I had trouble with Me.txtBestSoundex2 working. It kept prompting for a
value?
The following syntax did work [Form].[txtBestSoundex2]
I don't know why it was requiring [Form]??? Maybe because
txtBestSoundex2 is unbound?

Thankyou All
Greg

I have an SQL statement in the RowSource of a Listbox.
I want to build the WHERE clause in code as a String as follows:

strWhereSQL = "((([T-PERS].SDX2)=[Form].[txtBestSoundex2]))"

'The following code is in my Listbox RowSource
SELECT [T-PERS].LNAME,[T-PERS].FNAME
FROM [T-PERS]
WHERE strWhereSQL
ORDER BY [T-PERS].LNAME;


When I execute the code, I'm prompted to enter a parameter value.
I believe this is occurring because of a delimeter/& issue?

VBA variables are only available in the VBA environment.
Thye are outside the name spaces for queries and control
source expressions.

I think the simplest way to do this is to rewrite the list
box's entire row source:

Dim strSelect As String
Dim strWhere As String
Dim strOrderBy As String

strSelect = "SELECT LNAME, FNAME FROM [T-PERS] "
strWhere = "WHERE SDX2 = " & Me.txtBestSoundex2 & " "
strOrderBy = "ORDER BY LNAME, FNAME"

Me.listbox.RowSource = strSelect & strWhere & strOrderBy

 

[Ruel Cespedes via AccessMonster.com]

Great to hear! Good luck and much success to you!

Ruel

--
Ruel Cespedes
Sr. Programmer Analyst
(e-mail address removed)

Message posted via AccessMonster.com