422,027 Members | 1,149 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,027 IT Pros & Developers. It's quick & easy.

Use a string to indicate a field in a SQL statment

P: 22
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:
Expand|Select|Wrap|Line Numbers
  1. MyField = "Completed" 'Completed is a field of tbValve
  2. strSQL = "Select [ValveName] from tbValve Where MyField Is Null"
Thank you in advance for the help.

Best regards.
Sep 10 '17 #1

✓ answered by NeoPa

Hi Simão.

Try this :
Expand|Select|Wrap|Line Numbers
  1. MyField = "Completed" 'Completed is a field of tbValve
  2. strSQL = Replace("Select [ValveName] from tbValve Where ([%FN] Is Null)" _
  3.                , "%FN", MyField)

Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 30,549
Hi Simão.

Try this :
Expand|Select|Wrap|Line Numbers
  1. MyField = "Completed" 'Completed is a field of tbValve
  2. strSQL = Replace("Select [ValveName] from tbValve Where ([%FN] Is Null)" _
  3.                , "%FN", MyField)
Sep 10 '17 #2

P: 22
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.
Sep 10 '17 #3

NeoPa
Expert Mod 15k+
P: 30,549
Simão:
In reality, cannot understand why using the replace function the problem is solved.
That's because the string ends up saying :
Expand|Select|Wrap|Line Numbers
  1. Where ([Completed] Is Null)
Instead of :
Expand|Select|Wrap|Line Numbers
  1. 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).
Sep 11 '17 #4

Expert 100+
P: 634
Hi NeoPa

I am just curious as to why you suggest the Replace() function in stead of just concatenating the variable in the string
Expand|Select|Wrap|Line Numbers
  1. strSQL = "Select [ValveName] from tbValve Where [" & MyField & "] Is Null"
or have I missed something??

MTB
Sep 12 '17 #5

P: 22
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.
Sep 12 '17 #6

NeoPa
Expert Mod 15k+
P: 30,549
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.
Sep 13 '17 #7

NeoPa
Expert Mod 15k+
P: 30,549
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.
Sep 13 '17 #8

Post your reply

Sign in to post your reply or Sign up for a free account.