Query Builder - What's the trick?

[Guest]

I have a combo box with a rowsource defined by using a query builder
(clicking on the ... button). In the Criteria of the query builder, I just
can't seem to refer to a control on a form. My typical query would be like:

SELECT [field1], [field2] FROM tblTable1 WHERE lngSemEnrolNo = "&
Me.lngSemEnrolNo &"

The problem is that I keep getting either a "Data type mismatch in criteria
expression" error or a Enter Parameter Value box. I've tried various
combinations with single quotes, double quotes, single & double quotes, etc.
but keep getting the same error. The only time I don't get the error and it
works is to use the full [Forms]![Form1]![lngSemEnrolNo] notation. Any ideas
why? Thanks.
ck

 

[Brian]

I have a combo box with a rowsource defined by using a query builder
(clicking on the ... button). In the Criteria of the query builder, I just
can't seem to refer to a control on a form. My typical query would be like:

SELECT [field1], [field2] FROM tblTable1 WHERE lngSemEnrolNo = "&
Me.lngSemEnrolNo &"

The problem is that I keep getting either a "Data type mismatch in criteria
expression" error or a Enter Parameter Value box. I've tried various
combinations with single quotes, double quotes, single & double quotes, etc.
but keep getting the same error. The only time I don't get the error and it
works is to use the full [Forms]![Form1]![lngSemEnrolNo] notation. Any ideas
why? Thanks.
ck

"Me" can only be used within Visual Basic class modules, it has no meaning
anywhere else. You can use [Form]![lngSemEnrolNo] in order to avoid some
typing.

 

[John Vinson]

I have a combo box with a rowsource defined by using a query builder
(clicking on the ... button). In the Criteria of the query builder, I just
can't seem to refer to a control on a form. My typical query would be like:

SELECT [field1], [field2] FROM tblTable1 WHERE lngSemEnrolNo = "&
Me.lngSemEnrolNo &"

The problem is that I keep getting either a "Data type mismatch in criteria
expression" error or a Enter Parameter Value box. I've tried various
combinations with single quotes, double quotes, single & double quotes, etc.
but keep getting the same error. The only time I don't get the error and it
works is to use the full [Forms]![Form1]![lngSemEnrolNo] notation. Any ideas
why? Thanks.
ck

The Query has no way to know which form you mean - there might be
several open, each of which refers to itself as "me".

Use

=[Forms]!{NameOfForm]![lngSemEnrollNo]

as the criterion. Make sure that the Bound Column of the combo box is
the actual value stored in the table - if you've fallen victim to the
abominable Lookup Wizard (that's the creepy character with green hair
that may show up at your door tonight), you may *see* one value while
the table contains a different one.

If lngWemEnrolNo is a Text field (despite the indication from its name
that it's numeric), you need quotemarks: using a hypothetical string
field,

WHERE strSemEnrolNo = '" & [Forms]![frmYourForm]![cboEnrollNo] & "'"


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Brian]

I have a combo box with a rowsource defined by using a query builder
(clicking on the ... button). In the Criteria of the query builder, I just
can't seem to refer to a control on a form. My typical query would be like:

SELECT [field1], [field2] FROM tblTable1 WHERE lngSemEnrolNo = "&
Me.lngSemEnrolNo &"

The problem is that I keep getting either a "Data type mismatch in criteria
expression" error or a Enter Parameter Value box. I've tried various
combinations with single quotes, double quotes, single & double quotes, etc.
but keep getting the same error. The only time I don't get the error and it
works is to use the full [Forms]![Form1]![lngSemEnrolNo] notation. Any ideas
why? Thanks.
ck

The Query has no way to know which form you mean - there might be
several open, each of which refers to itself as "me".

Use

=[Forms]!{NameOfForm]![lngSemEnrollNo]

as the criterion. Make sure that the Bound Column of the combo box is
the actual value stored in the table - if you've fallen victim to the
abominable Lookup Wizard (that's the creepy character with green hair
that may show up at your door tonight), you may *see* one value while
the table contains a different one.

If lngWemEnrolNo is a Text field (despite the indication from its name
that it's numeric), you need quotemarks: using a hypothetical string
field,

WHERE strSemEnrolNo = '" & [Forms]![frmYourForm]![cboEnrollNo] & "'"


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

Actually, in this case the query does know which form, because it's not a
query object, it's the row source for a combo box. Hence the shorter
expression works:

[Form]![lngSemEnrollNo]

"Me" still doesn't work, though, because "Me" is only meaningful in a VBA
class module.

The expression referring to the field does NOT need to be in quotes, even if
it's a text field. For example, the following query works fine in
Northwind, where CustomerID is a text field:

SELECT Orders.OrderID
FROM Orders
WHERE (((Orders.CustomerID)=[Forms]![Customers]![CustomerID]));