Use a string to indicate a field in a SQL statment

[simaonobrega]

Dear Bytes community,

Framework: I have an SQL statment in a public function at a module. This fuction will be used in different forms to populate Comboboxes. My question is:
- Is it possible to use a string instead of a field in the criteria of the SQL statment? The goal was to change only the argument of my Public Function in order to populate my combobox, depending on the form.

Problem: Using the code on the example bellow, Access cannot identify the field.

Simple debug example:

MyField = "Completed" 'Completed is a field of tbValve
strSQL = "Select [ValveName] from tbValve Where MyField Is Null"

Thank you in advance for the help.

Best regards.

[NeoPa]

Hi Simão.

Try this :

MyField = "Completed" 'Completed is a field of tbValve
strSQL = Replace("Select [ValveName] from tbValve Where ([%FN] Is Null)" _
               , "%FN", MyField)

[simaonobrega]

Hello NeoPa,

Thank you very much for your fast and accurate answer!
Worked very well.
In reality, cannot understand why using the replace function the problem is solved.

[NeoPa]

Simão:
In reality, cannot understand why using the replace function the problem is solved.

That's because the string ends up saying :

Where ([Completed] Is Null)

Instead of :

Where ([MyField] Is Null)

SQL has no protocol that allows it to specify a field name indirectly via a VBA variable. Jet/ACE, which interprets and executes the SQL, has no idea what's going on at the VBA level at all, so wouldn't recognise MyField even if there were a protocol for indirect referencing of Fields (which there isn't of course).

[MikeTheBike]

Hi NeoPa

I am just curious as to why you suggest the Replace() function in stead of just concatenating the variable in the string

strSQL = "Select [ValveName] from tbValve Where [" & MyField & "] Is Null"

or have I missed something??

MTB

[simaonobrega]

Hello NeoPa,

Thank you for your detailed explanation.
Always learning!

@MikeTheBike,
I was unable to put the combobox working with your solution.
Maybe NeoPa will explain later the reason for not working.

Regards.

[NeoPa]

Hi Mike.

I very rarely use simple concatenation nowadays as I've seen too many examples where the meaning of the code is just lost in the complexity of it. Many times have I had questions where the OP was simply confused by the mixing of strings, the ampersands and the variables (Not to mention the quote characters of course).

With Replace(), and my own function MultiReplace(), this is so much easier to follow. The first parameter is always the basic template of the string and each replaced value is clearly seen in the original context. It leads to fewer mistakes, and fewer similar questions on the boards.

I've posted my MultiReplace() function previously but I'm happy to do so again if required. I don't want to keep posting it unless there's real interest though.

[NeoPa]

Hi Mike.

I very rarely use simple concatenation nowadays as I've seen too many examples where the meaning of the code is just lost in the complexity of it. Many times have I had questions where the OP was simply confused by the mixing of strings, the ampersands and the variables (Not to mention the quote characters of course).

With Replace(), and my own function MultiReplace(), this is so much easier to follow. The first parameter is always the basic template of the string and each replaced value is clearly seen in the original context. It leads to fewer mistakes, and fewer similar questions on the boards.

I've posted my MultiReplace() function previously but I'm happy to do so again if required. I don't want to keep posting it unless there's real interest though.

@Simão.
There's no good reason for Mike's suggestion to fail. If used as suggested it should work perfectly. It uses a differnt approach at the VBA level, but a perfectly valid one.