|
|
|
|
|
|
|
|
|
|
| |
On Sat, 30 Oct 2004 23:33:01 -0700, "CK"
<CK@discussions.microsoft.com> wrote:
>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
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
| |
 Answer |
|
| |
"John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:sfiao09kridjlp1ee41j2ufnqh5uh6dsa9@4ax.com...
> On Sat, 30 Oct 2004 23:33:01 -0700, "CK"
> <CK@discussions.microsoft.com> wrote:
>
> >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]));
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
Question
Notify me of replies