Getting Syntax right for SQL String

  • Thread starter Thread starter Kahuna
  • Start date Start date
K

Kahuna

Hi Folks

Been struggling with this for too long - got an SQL string in VBA and I cant
for the life of me figure out the correct way to phrase it:

strSQL_Lookup = "SELECT [txtReal_Field_Name] FROM Name_List"

txtReal_Field_Name is a variable holding the name of a field, and my
intention is to look-up all of the items from that field in the table
Name_List (and hopefully put a Distinct in there too).

How do I correctly form this Select incorporating the variable?

TIA
 
Kahuna said:
Hi Folks

Been struggling with this for too long - got an SQL string in VBA and I cant
for the life of me figure out the correct way to phrase it:

strSQL_Lookup = "SELECT [txtReal_Field_Name] FROM Name_List"

txtReal_Field_Name is a variable holding the name of a field, and my
intention is to look-up all of the items from that field in the table
Name_List (and hopefully put a Distinct in there too).
Click to expand...

"SELECT " & txtReal_Field_Name & " FROM Name_List"
 
Bas thanks for the feedback - but even more bizarrely (because I thought
that would work nicely) I get the following error:

The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect. (Error 3141)

Any more advice?
TIA
--
Kahuna
------------
Bas Cost Budde said:
Kahuna said:
Hi Folks

Been struggling with this for too long - got an SQL string in VBA and I
cant for the life of me figure out the correct way to phrase it:

strSQL_Lookup = "SELECT [txtReal_Field_Name] FROM Name_List"

txtReal_Field_Name is a variable holding the name of a field, and my
intention is to look-up all of the items from that field in the table
Name_List (and hopefully put a Distinct in there too).
Click to expand...

"SELECT " & txtReal_Field_Name & " FROM Name_List"

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea
Click to expand...
 
Bas Cost Budde said:
Kahuna said:
Hi Folks

Been struggling with this for too long - got an SQL string in VBA and I cant
for the life of me figure out the correct way to phrase it:

strSQL_Lookup = "SELECT [txtReal_Field_Name] FROM Name_List"

txtReal_Field_Name is a variable holding the name of a field, and my
intention is to look-up all of the items from that field in the table
Name_List (and hopefully put a Distinct in there too).
Click to expand...

"SELECT " & txtReal_Field_Name & " FROM Name_List"
Click to expand...

Just in case txtReal_Field_Name might contain non-standard field names (with
blanks or dashes, for example), or the field names are reserved words, it
might be better to include square brackets in there:

"SELECT [" & txtReal_Field_Name & "] FROM Name_List"
 
Bas - some further info - this is the error when building a recordset on the
Select statement. And after the recordset is built (after the error is
posted) the recordset says 'SELECT FROM Name_List' and completely excludes
the argument??

TIA

--
Kahuna
------------
Kahuna said:
Bas thanks for the feedback - but even more bizarrely (because I thought
that would work nicely) I get the following error:

The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect. (Error 3141)

Any more advice?
TIA
--
Kahuna
------------
Bas Cost Budde said:
Kahuna said:
Hi Folks

Been struggling with this for too long - got an SQL string in VBA and I
cant for the life of me figure out the correct way to phrase it:

strSQL_Lookup = "SELECT [txtReal_Field_Name] FROM Name_List"

txtReal_Field_Name is a variable holding the name of a field, and my
intention is to look-up all of the items from that field in the table
Name_List (and hopefully put a Distinct in there too).
Click to expand...

"SELECT " & txtReal_Field_Name & " FROM Name_List"

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea
Click to expand...
Click to expand...
 
Doug - there are no non-standard field names in there but taking your
suggestion (and noting that Bas's comments didn't fully resolve the issue) I
have modified the SQL string and now get the error

Too Few Parameters - Expected 1 - and that appears on attempting to build
the recordset using:

Set rstLookup = dbCurr.OpenRecordset(strSQL_Lookup)

Any further suggestions my friends?

TIA

--
Kahuna
------------
Douglas J. Steele said:
Bas Cost Budde said:
Kahuna said:
Hi Folks

Been struggling with this for too long - got an SQL string in VBA and I cant
for the life of me figure out the correct way to phrase it:

strSQL_Lookup = "SELECT [txtReal_Field_Name] FROM Name_List"

txtReal_Field_Name is a variable holding the name of a field, and my
intention is to look-up all of the items from that field in the table
Name_List (and hopefully put a Distinct in there too).
Click to expand...

"SELECT " & txtReal_Field_Name & " FROM Name_List"
Click to expand...

Just in case txtReal_Field_Name might contain non-standard field names
(with
blanks or dashes, for example), or the field names are reserved words, it
might be better to include square brackets in there:

"SELECT [" & txtReal_Field_Name & "] FROM Name_List"
Click to expand...
 
What's in strSQL_Lookup?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Kahuna said:
Doug - there are no non-standard field names in there but taking your
suggestion (and noting that Bas's comments didn't fully resolve the issue) I
have modified the SQL string and now get the error

Too Few Parameters - Expected 1 - and that appears on attempting to build
the recordset using:

Set rstLookup = dbCurr.OpenRecordset(strSQL_Lookup)

Any further suggestions my friends?

TIA

--
Kahuna
------------
Douglas J. Steele said:
Bas Cost Budde said:
Kahuna wrote:
Hi Folks

Been struggling with this for too long - got an SQL string in VBA and
Click to expand...
I
cant
for the life of me figure out the correct way to phrase it:

strSQL_Lookup = "SELECT [txtReal_Field_Name] FROM Name_List"

txtReal_Field_Name is a variable holding the name of a field, and my
intention is to look-up all of the items from that field in the table
Name_List (and hopefully put a Distinct in there too).

"SELECT " & txtReal_Field_Name & " FROM Name_List"
Click to expand...

Just in case txtReal_Field_Name might contain non-standard field names
(with
blanks or dashes, for example), or the field names are reserved words, it
might be better to include square brackets in there:

"SELECT [" & txtReal_Field_Name & "] FROM Name_List"
Click to expand...
Click to expand...
 
That's the SELECT statement Doug:

strSQL_Lookup = "SELECT [" & txtReal_Field_Name & "] FROM Name_List"

now
TIA

--
Kahuna
------------
Douglas J. Steele said:
What's in strSQL_Lookup?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Kahuna said:
Doug - there are no non-standard field names in there but taking your
suggestion (and noting that Bas's comments didn't fully resolve the
issue) I
have modified the SQL string and now get the error

Too Few Parameters - Expected 1 - and that appears on attempting to build
the recordset using:

Set rstLookup = dbCurr.OpenRecordset(strSQL_Lookup)

Any further suggestions my friends?

TIA

--
Kahuna
------------
Douglas J. Steele said:
Kahuna wrote:
Hi Folks

Been struggling with this for too long - got an SQL string in VBA
and I
cant
for the life of me figure out the correct way to phrase it:

strSQL_Lookup = "SELECT [txtReal_Field_Name] FROM Name_List"

txtReal_Field_Name is a variable holding the name of a field, and my
intention is to look-up all of the items from that field in the
table
Name_List (and hopefully put a Distinct in there too).

"SELECT " & txtReal_Field_Name & " FROM Name_List"

Just in case txtReal_Field_Name might contain non-standard field names
(with
blanks or dashes, for example), or the field names are reserved words, it
might be better to include square brackets in there:

"SELECT [" & txtReal_Field_Name & "] FROM Name_List"
Click to expand...
Click to expand...
Click to expand...
 
I don't want to know the VBA code: I want to know what the variable contains
when you're trying to use it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Kahuna said:
That's the SELECT statement Doug:

strSQL_Lookup = "SELECT [" & txtReal_Field_Name & "] FROM Name_List"

now
TIA

--
Kahuna
------------
Douglas J. Steele said:
What's in strSQL_Lookup?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Kahuna said:
Doug - there are no non-standard field names in there but taking your
suggestion (and noting that Bas's comments didn't fully resolve the
issue) I
have modified the SQL string and now get the error

Too Few Parameters - Expected 1 - and that appears on attempting to build
the recordset using:

Set rstLookup = dbCurr.OpenRecordset(strSQL_Lookup)

Any further suggestions my friends?

TIA

--
Kahuna
------------
Kahuna wrote:
Hi Folks

Been struggling with this for too long - got an SQL string in VBA
and I
cant
for the life of me figure out the correct way to phrase it:

strSQL_Lookup = "SELECT [txtReal_Field_Name] FROM Name_List"

txtReal_Field_Name is a variable holding the name of a field, and my
intention is to look-up all of the items from that field in the
table
Name_List (and hopefully put a Distinct in there too).

"SELECT " & txtReal_Field_Name & " FROM Name_List"

Just in case txtReal_Field_Name might contain non-standard field names
(with
blanks or dashes, for example), or the field names are reserved
Click to expand...
words,
it
might be better to include square brackets in there:

"SELECT [" & txtReal_Field_Name & "] FROM Name_List"
Click to expand...
Click to expand...
Click to expand...
 
John said:
strSQL_Lookup = "SELECT [" & txtReal_Field_Name & "] FROM Name_List"
Click to expand...
Click to expand...
It would appear that the variable txtReal_Field_Name is either a null
Variant or an empty string.
Click to expand...

Or, something that is not a real field name! Calling a variable 'real'
does not ensure its contents match its promise! Nomen non est omen. :-)

Kahuna, put a break point on that line (F9) and check the value, either
by hovering the mouse over its name, or in the Debug window. Check
whether it is a correct field name.

Douglas, thanks again for the brackets.
 
Thanks Guys - got really late and the blurriness caused temporary blindness!
<lol>

Your right - seemed the variable did contain my filed name in MSGBOX just
above the rst, but not actually in the rst!

Investigation this morning revealed that I had used that name
(txtReal_Field_Name) for a text field on the form too (just as an
invisible - at run time - check) to make sure I had the right data.

Too stupid for words I know!

Cheers to all - have a good Christmas why don't ya! (Probably be back to
pester you all again before then though <lol>.

--
Kahuna
------------
Bas Cost Budde said:
John said:
strSQL_Lookup = "SELECT [" & txtReal_Field_Name & "] FROM Name_List"
Click to expand...
Click to expand...
It would appear that the variable txtReal_Field_Name is either a null
Variant or an empty string.
Click to expand...

Or, something that is not a real field name! Calling a variable 'real'
does not ensure its contents match its promise! Nomen non est omen. :-)

Kahuna, put a break point on that line (F9) and check the value, either by
hovering the mouse over its name, or in the Debug window. Check whether it
is a correct field name.

Douglas, thanks again for the brackets.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea
Click to expand...
 
Kahuna said:
Investigation this morning revealed that I had used that name
(txtReal_Field_Name) for a text field on the form too (just as an
invisible - at run time - check) to make sure I had the right data.
Click to expand...

Ahaa! The famous scoping. If only you had to explicitly put Me! before
all control references...

:-) Cheers
 
Back
Top