What SQL interpreter can and can't recognize (1 Viewer)

There's a difference between the phrases
"SELECT * FROM Employees WHERE EmployeeID = [cbxEmployee]"
and
"SELECT * FROM Employees WHERE EmployeeID = [cbxEmployee].column(1)"

sql interpreter (btw who is that?) can not interpret the second. In order to make it work, you have to rewrite it as

"SELECT * FROM Employees WHERE EmployeeID = " & [cbxEmployee].column(1)

I wonder as to why the combobox object name can be passed as SQL but not its column property.

Square brackets are used to enclose the entire column name. Thus the ".column(1)" would be being interpreted as being beyond the column name, thus, an error.

SQL is separate from VBA and Access objects. .column() is a property of a control on a form. SQL knows nothing about forms or controls. Access does "convert" a form control reference for our convenience so the .value property (the default control property) can be passed to a query but Access doesn't provide this conversion for all properties. Notice that when you reference a form field, you don't actually mention the property so "Where somefield = Forms!formname!controlname.Value" is incorrect.

If you need to reference a field from the rowsource other than the bound field in your SQL, copy it to a hidden form field and reference that field in the query.

Me.txtHiddenControl = Me.cbxEmployee.Column(1)

Pat Hartman said:

Notice that when you reference a form field, you don't actually mention the property so "Where somefield = Forms!formname!controlname.Value" is incorrect.

Click to expand...

No, it is not incorrect. There is nothing wrong with that expression. Value is the default property of a control (fields too) and hence the Value property can be optionally omitted.

There is a substantial network of defaults in Access and most expressions we use are in fact abbreviated.

If you need to reference a field from the rowsource other than the bound field in your SQL, copy it to a hidden form field and reference that field in the query.

Click to expand...

Using incorrect terminology perpetuates misunderstandings. The boxes on forms are called CONTROLS. The fields in a form are in its Recordset.

ty for the answers. I guess a more elaborate way to present my case would be:
"SELECT * FROM Employees WHERE EmployeeID = Forms![frmEmployees]![cbxEmployee].Value"
and
"SELECT * FROM Employees WHERE EmployeeID = Forms![frmEmployees]![cbxEmployee].column(0)"

they now mean the same thing also. I find it odd that the SQL interpreter can understand some objects that are in the domain of Access' library (ACEDAO I think) but not some of its properties.

Queries in earlier versions of Access would not accept "Forms!formname!controlname.Value". They would only accept "Forms!formname!controlname". It appears that A2010 at least now accepts the .value property. The Value property does not show up however in the intellisense provided by the QBE.

delikedi said:

I find it odd that the SQL interpreter can understand some objects that are in the domain of Access' library (ACEDAO I think) but not some of its properties.

Click to expand...

AFAIK The SQL interpreter doesn't understand any property of any object except the default (Value). On a form it can only see a control or a field in the recordset.

When a property required in the expression it needs to be concatenated in using VBA.

Pat Hartman said:

Queries in earlier versions of Access would not accept "Forms!formname!controlname.Value". They would only accept "Forms!formname!controlname". It appears that A2010 at least now accepts the .value property. The Value property does not show up however in the intellisense provided by the QBE.

Click to expand...

A2007 also supports Value but I can't remmeber what happened in 2003.

I assume that Intellisense in the QBE is a new feature in A2010. Or is there a facility in earlier versions I have overlooked?

@Galaxiom, yes, it was introduced in 2010.

@OP,

In all versions of Access, though, you can always right-click a cell in QBE and select "Build..." to open the expression builder. Within it, you can navigate Forms -> Loaded Forms -> <name of your form>. The listing you can then see in the middle/rightmost listboxes is what Access can 'resolve' automatically for you.

Additionally, it may help to remember that though Access is a single product, in reality, it's two distinct products tightly integrated -- it's both an IDE and a database engine. Give this a try:

'This code will work OK

However, this will not be OK

If you then look where you get DoCmd from, it's within Access library while OpenRecordset is a part of DAO library. DAO is at lower level and closer to the engine. Engine has no concept of forms, controls, or anything. All it knows about is the table, queries, and what is available through expression services.

DoCmd, being a part of Access can work with the Access' interface and retrieve the information and automatically resolve the references, allowing RunSQL to succeed where OpenRecordset needs its references resolved prior.

I hope that helps illustrate the 'why'.

ty all for the informative replies.
@ banana,
ty for your reply. I believe your message confirms what many novice access users somewhat "sense". People have to take disproportionately special care to prepare syntax-correct strings for the consumption of the "SQL God" (which I guess is the DAO dbengine) compared to regular access-vba syntax, and that can be tedious. Your example on docmd also hit the spot. I believe a similar duality exists between "dbengine" and "currentdb" objects. anywys, ty and bye.

delikedi said:

People have to take disproportionately special care to prepare syntax-correct strings for the consumption of the "SQL God" (which I guess is the DAO dbengine) compared to regular access-vba syntax, and that can be tedious.

Click to expand...

DAO is an object model. It doesn't include a database engine or any native capability for processing SQL.

All the SQL, whether that be from VBA or Access quries, is handled by a separate database engine. Until Access 2007 when ACE was introduced, that engine was the same JET engine that is an integral part of Windows and used to manage the databases that Windows relies on to function.

References to Forms are not part of SQL. These references are interpreted by the Application and converted to SQL parameters which are passed to JET/ACE with the query.

As Banana pointed out, it is simply a matter of understanding where the processing is first directed. If it is handled by the Application Object then it can read the objects in the application. If it is directed to the Database engine then it doesn't have any comprehension of Forms etc.

I believe a similar duality exists between "dbengine" and "currentdb" objects. anywys, ty and bye.

Click to expand...

Currentdb is not a Object but a Method of the Application object, (Application.CurrentDb). It directs a query to the DbEngine and returns a reference to the current database. Since that reference is still outside the Application it cannot process the Form contructs.