Concatenating variable in SQL statement

[tomhughes]

I am trying to use a string Value in an SQL statement. The string Value is "strTabNo". When I hard code the value in as shown below my code works.

strSQL = "SELECT tblData.* FROM tblData WHERE tblData.TopicHead" & strTabNo & _
" = '" & Forms!frmMain.TopicHeadA & "'"

But I have not been successful in adding the string Value in the code. This is only one of many code variations I have tried.

strSQL = "SELECT tblData.* FROM tblData WHERE tblData.TopicHead" & strTabNo & _
" = '" & Forms!frmMain.TopicHead & "' & strTabNo"

What is the proper syntax for adding a string value in the code? The first instance of the string value works fine.

 

[dhookom]

Are we supposed to understand what typical values are in strTabNo? Is this a part of a field name? Why do you have strTabNo at the end? It would help if you provided a value for strTabNo and then provided the expect SQL syntax.

It also looks like you might have multiple similar fields which suggests your table isn't normalized. I could be wrong.

This might work.

strSQL = "SELECT tblData.* FROM tblData WHERE tblData.TopicHead" & strTabNo & _
" = '" & Forms!frmMain.TopicHead & "'"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[HarleyQuinn]

Something like:

strSQL = "SELECT tblData.* FROM tblData WHERE tblData.TopicHead & " & strTabNo & _
" = '" & Forms!frmMain.TopicHead & strTabNo & "'"

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[tomhughes]

strTabNo is a capital letter from A-Z. That is why the first example works, but I don't know how to concatenate it at the end of the statement.

if strTabNo = "A"

then "TopicHead" and "strTabNo" will form the word "TopicHeadA"

I have tried all the examples that all of you have sugested, and many others, but can't get it to work.

 

[dhookom]

HarleyQuinn's answer should work but it doesn't answer any questions about a table structure that includes 26 TopicHead fields.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]