Get Values SQL in table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, i'm having problems to get the other values of one table with an SQL
statement.
i did the next code, but it always show me that the value number it's false:

For i = 0 To d - 1
b = Me.[Lmedicamento].Column(i) 'Name of the field selected in the LisBox
Next i
MsgBox "O nome é " & b & ".", vbOKOnly

strsql = "SELECT Código FROM Gotas Coliriose Pomadas WHERE Medicamento = b"

MsgBox "o numero é " & strsql & "."

i did the SQL statement to select the value of the Código in the table where
the field Medicamento is b, for example the b = PANASORBE and the code of b
is
code = 123456, what i want to the sql statement to do it's, get valeu of the
code, like in this case the value of the code = 123546

The MSgbox in the Program are too show what is happened, so don't worry
about that.
thanks
 
Hi, Bruno.

A table name can't have spaces in it within a SQL statement, because the
database engine won't be able to parse the string correctly before executing
the commands. Placing brackets around non-standard syntax in Access will
often fix it, but there's no guarantee that this Band-Aid will work in every
situation. Also the variable value, b, needs to be concatenated to the
string, not used as a string literal, because b is not defined, nor assigned
a value, within the SQL statement or the table that is the data source.

If b is a string value, then try:

strsql = "SELECT Código " & _
"FROM [Gotas Coliriose Pomadas] " & _
"WHERE Medicamento = '" & b & "';"

However, if b is a numerical value, then try:

strsql = "SELECT Código " & _
"FROM [Gotas Coliriose Pomadas] " & _
"WHERE Medicamento = " & b

And since the SQL string construction line of code is outside of the
FOR-LOOP, the value of b will always be equal to the value of
Me.[Lmedicamento].Column(d-1), because that was the last value assigned
before exiting the loop.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Hi, thanks
I still have on more question, the (strsql) it's working well, but, in the
texbox where i'm gone put the value of the code, it don't show the value, it
shows all the sql statement, like this (SELECT Código FROM [Gotas Colirios e
Pomadas] WHERE Medicamento = 'Clorhexidina Pomada';) --> in the Textbox
(Tcode).
you have any sugestion

Thanks a lot;

"'69 Camaro" escreveu:
Hi, Bruno.

A table name can't have spaces in it within a SQL statement, because the
database engine won't be able to parse the string correctly before executing
the commands. Placing brackets around non-standard syntax in Access will
often fix it, but there's no guarantee that this Band-Aid will work in every
situation. Also the variable value, b, needs to be concatenated to the
string, not used as a string literal, because b is not defined, nor assigned
a value, within the SQL statement or the table that is the data source.

If b is a string value, then try:

strsql = "SELECT Código " & _
"FROM [Gotas Coliriose Pomadas] " & _
"WHERE Medicamento = '" & b & "';"

However, if b is a numerical value, then try:

strsql = "SELECT Código " & _
"FROM [Gotas Coliriose Pomadas] " & _
"WHERE Medicamento = " & b

And since the SQL string construction line of code is outside of the
FOR-LOOP, the value of b will always be equal to the value of
Me.[Lmedicamento].Column(d-1), because that was the last value assigned
before exiting the loop.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Bruno Coelho said:
Hi, i'm having problems to get the other values of one table with an SQL
statement.
i did the next code, but it always show me that the value number it's false:

For i = 0 To d - 1
b = Me.[Lmedicamento].Column(i) 'Name of the field selected in the LisBox
Next i
MsgBox "O nome é " & b & ".", vbOKOnly

strsql = "SELECT Código FROM Gotas Coliriose Pomadas WHERE Medicamento = b"

MsgBox "o numero é " & strsql & "."

i did the SQL statement to select the value of the Código in the table where
the field Medicamento is b, for example the b = PANASORBE and the code of b
is
code = 123456, what i want to the sql statement to do it's, get valeu of the
code, like in this case the value of the code = 123546

The MSgbox in the Program are too show what is happened, so don't worry
about that.
thanks
Click to expand...
Click to expand...
 
Hi, Bruno.

If I understand your question correctly, you want to display the resulting
data set (the Código from the table) in the text box, not the actual SQL
statement. If this is the case, then you'll need to use a different control
type, such as a combo box or list box, which has a Row Source Property that
can take the results of the query and display them in columns. The text box
is only capable of displaying the actual text string, not running the query
to retrieve the results. For example, try:

Me!lstCodes.RowSource = "SELECT Código " & _
"FROM [Gotas Coliriose Pomadas] " & _
"WHERE Medicamento = '" & b & "';"
Me!lstCodes.Requery

.. . . where lstCodes is the name of the list box.

Also, you may have better luck communicating in an Access newsgroup for
Portuguese speakers. Please see the following Web page:

http://www.microsoft.com/communitie...4a4-9789-487f-9f6f-46b367533814&lang=pt&cr=PT

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.


Bruno Coelho said:
Hi, thanks
I still have on more question, the (strsql) it's working well, but, in the
texbox where i'm gone put the value of the code, it don't show the value,
it
shows all the sql statement, like this (SELECT Código FROM [Gotas Colirios
e
Pomadas] WHERE Medicamento = 'Clorhexidina Pomada';) --> in the Textbox
(Tcode).
you have any sugestion

Thanks a lot;

"'69 Camaro" escreveu:
Hi, Bruno.

A table name can't have spaces in it within a SQL statement, because the
database engine won't be able to parse the string correctly before
executing
the commands. Placing brackets around non-standard syntax in Access will
often fix it, but there's no guarantee that this Band-Aid will work in
every
situation. Also the variable value, b, needs to be concatenated to the
string, not used as a string literal, because b is not defined, nor
assigned
a value, within the SQL statement or the table that is the data source.

If b is a string value, then try:

strsql = "SELECT Código " & _
"FROM [Gotas Coliriose Pomadas] " & _
"WHERE Medicamento = '" & b & "';"

However, if b is a numerical value, then try:

strsql = "SELECT Código " & _
"FROM [Gotas Coliriose Pomadas] " & _
"WHERE Medicamento = " & b

And since the SQL string construction line of code is outside of the
FOR-LOOP, the value of b will always be equal to the value of
Me.[Lmedicamento].Column(d-1), because that was the last value assigned
before exiting the loop.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.


Bruno Coelho said:
Hi, i'm having problems to get the other values of one table with an
SQL
statement.
i did the next code, but it always show me that the value number it's
false:

For i = 0 To d - 1
b = Me.[Lmedicamento].Column(i) 'Name of the field selected in the
LisBox
Next i
MsgBox "O nome é " & b & ".", vbOKOnly

strsql = "SELECT Código FROM Gotas Coliriose Pomadas WHERE Medicamento
= b"

MsgBox "o numero é " & strsql & "."

i did the SQL statement to select the value of the Código in the table
where
the field Medicamento is b, for example the b = PANASORBE and the code
of b
is
code = 123456, what i want to the sql statement to do it's, get valeu
of the
code, like in this case the value of the code = 123546

The MSgbox in the Program are too show what is happened, so don't worry
about that.
thanks
Click to expand...
Click to expand...
Click to expand...
 
Thanks, 69 camaro it's working well

"'69 Camaro" escreveu:
Hi, Bruno.

If I understand your question correctly, you want to display the resulting
data set (the Código from the table) in the text box, not the actual SQL
statement. If this is the case, then you'll need to use a different control
type, such as a combo box or list box, which has a Row Source Property that
can take the results of the query and display them in columns. The text box
is only capable of displaying the actual text string, not running the query
to retrieve the results. For example, try:

Me!lstCodes.RowSource = "SELECT Código " & _
"FROM [Gotas Coliriose Pomadas] " & _
"WHERE Medicamento = '" & b & "';"
Me!lstCodes.Requery

.. . . where lstCodes is the name of the list box.

Also, you may have better luck communicating in an Access newsgroup for
Portuguese speakers. Please see the following Web page:

http://www.microsoft.com/communitie...4a4-9789-487f-9f6f-46b367533814&lang=pt&cr=PT

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.


Bruno Coelho said:
Hi, thanks
I still have on more question, the (strsql) it's working well, but, in the
texbox where i'm gone put the value of the code, it don't show the value,
it
shows all the sql statement, like this (SELECT Código FROM [Gotas Colirios
e
Pomadas] WHERE Medicamento = 'Clorhexidina Pomada';) --> in the Textbox
(Tcode).
you have any sugestion

Thanks a lot;

"'69 Camaro" escreveu:
Hi, Bruno.

A table name can't have spaces in it within a SQL statement, because the
database engine won't be able to parse the string correctly before
executing
the commands. Placing brackets around non-standard syntax in Access will
often fix it, but there's no guarantee that this Band-Aid will work in
every
situation. Also the variable value, b, needs to be concatenated to the
string, not used as a string literal, because b is not defined, nor
assigned
a value, within the SQL statement or the table that is the data source.

If b is a string value, then try:

strsql = "SELECT Código " & _
"FROM [Gotas Coliriose Pomadas] " & _
"WHERE Medicamento = '" & b & "';"

However, if b is a numerical value, then try:

strsql = "SELECT Código " & _
"FROM [Gotas Coliriose Pomadas] " & _
"WHERE Medicamento = " & b

And since the SQL string construction line of code is outside of the
FOR-LOOP, the value of b will always be equal to the value of
Me.[Lmedicamento].Column(d-1), because that was the last value assigned
before exiting the loop.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.


:

Hi, i'm having problems to get the other values of one table with an
SQL
statement.
i did the next code, but it always show me that the value number it's
false:

For i = 0 To d - 1
b = Me.[Lmedicamento].Column(i) 'Name of the field selected in the
LisBox
Next i
MsgBox "O nome é " & b & ".", vbOKOnly

strsql = "SELECT Código FROM Gotas Coliriose Pomadas WHERE Medicamento
= b"

MsgBox "o numero é " & strsql & "."

i did the SQL statement to select the value of the Código in the table
where
the field Medicamento is b, for example the b = PANASORBE and the code
of b
is
code = 123456, what i want to the sql statement to do it's, get valeu
of the
code, like in this case the value of the code = 123546

The MSgbox in the Program are too show what is happened, so don't worry
about that.
thanks
Click to expand...
Click to expand...
Click to expand...
 
You're welcome!

Good luck.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.


Bruno Coelho said:
Thanks, 69 camaro it's working well

"'69 Camaro" escreveu:
Hi, Bruno.

If I understand your question correctly, you want to display the
resulting
data set (the Código from the table) in the text box, not the actual SQL
statement. If this is the case, then you'll need to use a different
control
type, such as a combo box or list box, which has a Row Source Property
that
can take the results of the query and display them in columns. The text
box
is only capable of displaying the actual text string, not running the
query
to retrieve the results. For example, try:

Me!lstCodes.RowSource = "SELECT Código " & _
"FROM [Gotas Coliriose Pomadas] " & _
"WHERE Medicamento = '" & b & "';"
Me!lstCodes.Requery

.. . . where lstCodes is the name of the list box.

Also, you may have better luck communicating in an Access newsgroup for
Portuguese speakers. Please see the following Web page:

http://www.microsoft.com/communitie...4a4-9789-487f-9f6f-46b367533814&lang=pt&cr=PT

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.


Bruno Coelho said:
Hi, thanks
I still have on more question, the (strsql) it's working well, but, in
the
texbox where i'm gone put the value of the code, it don't show the
value,
it
shows all the sql statement, like this (SELECT Código FROM [Gotas
Colirios
e
Pomadas] WHERE Medicamento = 'Clorhexidina Pomada';) --> in the Textbox
(Tcode).
you have any sugestion

Thanks a lot;

"'69 Camaro" escreveu:

Hi, Bruno.

A table name can't have spaces in it within a SQL statement, because
the
database engine won't be able to parse the string correctly before
executing
the commands. Placing brackets around non-standard syntax in Access
will
often fix it, but there's no guarantee that this Band-Aid will work in
every
situation. Also the variable value, b, needs to be concatenated to
the
string, not used as a string literal, because b is not defined, nor
assigned
a value, within the SQL statement or the table that is the data
source.

If b is a string value, then try:

strsql = "SELECT Código " & _
"FROM [Gotas Coliriose Pomadas] " & _
"WHERE Medicamento = '" & b & "';"

However, if b is a numerical value, then try:

strsql = "SELECT Código " & _
"FROM [Gotas Coliriose Pomadas] " & _
"WHERE Medicamento = " & b

And since the SQL string construction line of code is outside of the
FOR-LOOP, the value of b will always be equal to the value of
Me.[Lmedicamento].Column(d-1), because that was the last value
assigned
before exiting the loop.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a
message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message,
which
adds your question and the answers to the database of answers.
Remember
that
questions answered the quickest are often from those who have a
history
of
rewarding the contributors who have taken the time to answer questions
correctly.


:

Hi, i'm having problems to get the other values of one table with an
SQL
statement.
i did the next code, but it always show me that the value number
it's
false:

For i = 0 To d - 1
b = Me.[Lmedicamento].Column(i) 'Name of the field selected in
the
LisBox
Next i
MsgBox "O nome é " & b & ".", vbOKOnly

strsql = "SELECT Código FROM Gotas Coliriose Pomadas WHERE
Medicamento
= b"

MsgBox "o numero é " & strsql & "."

i did the SQL statement to select the value of the Código in the
table
where
the field Medicamento is b, for example the b = PANASORBE and the
code
of b
is
code = 123456, what i want to the sql statement to do it's, get
valeu
of the
code, like in this case the value of the code = 123546

The MSgbox in the Program are too show what is happened, so don't
worry
about that.
thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top