passing a variable to a query

[megatronixs]

Hi all,

I have an outlook macro that checks an email based on a table added at the bottom. In one of the fields there is a BIN nr. I can debug.print it and shows up in the immediate window. Later in the macro, the code will connent to an access database and look for the analyst name inside a table based on the BIN nr to find the correct record. If I add the BIN nr to the code itself, it will find the record and debug print the analyst name:

SQL = "SELECT [BIN],[Analyst] FROM tbl_case_list WHERE [BIN] = 1234567890"

I would like to know how I could change to use BIN_nr variable that is a text string and pass it to the SQL statement.

Any ideas how to do this?

Greetings.

 

[jdraw]

??? Where is the text string?? Is this going on a Form??

could try
SQL = "SELECT [BIN],[Analyst] FROM tbl_case_list WHERE [BIN nr] = '" & Me.TextControl & "'"

We need to know where your text string is located.

 

[megatronixs]

Hi,
the variable comes from a macro that finds text inside an email and puts this one in memory. I declared "Dim BIN_nr As String" (maybe my idea of variable is bad)
With that text found, I need it go connect to a database and in the query it needs to find the analyst that is on that BIN_nr. so on the above code, the text that is captured, could be something like below (that does not work):

SQL = "SELECT [BIN],[Analyst] FROM tbl_case_list WHERE [BIN] = BIN_nr"

Any idea how to get that one?

Greetings.

 

[stumac]

You are passing the text 'bin_nr' instead of the variable - you need to add this outside of the quotation marks...

Ifthe field [BIN] within your database is a text field:

SQL = "SELECT [BIN],[Analyst] FROM tbl_case_list WHERE [BIN] = '" & BIN_nr & "'"

However if it is stored as a number then:

SQL = "SELECT [BIN],[Analyst] FROM tbl_case_list WHERE [BIN] = " & BIN_nr

 

[megatronixs]

Hi,
That works great BIG thanks.

Greetings.