[AccessD] Query expression refers to column(1) on a form

Depending on what version you're using, you may not be able to reference
anything but the 0 column in a combo both in a query.  One option is to
build the SQL in VBA with a static function like Stuart mentioned.  If
you're using 2013, I would recommend using a TempVar instead.  They seem to
be very stable and even persist after unhandled errors.  All you need do is
set one either in code or using a data macro.  Queries in 2013 are quite
happy to use a TempVar, where the expression would be
[TempVars]![MyTempVarName].  They are a global collection in 2013 and
persist through an entire Access session unless you change them somewhere
else in your code or macros.  You set one by simply writing
TempVars!MyTempVarName = <whatever value>.  The square brackets are because
tempvars are an object like the Forms collection.

Charlotte

On Tue, Sep 30, 2014 at 5:30 PM, David McAfee <davidmcafee at gmail.com> wrote:

> So this is weird, I know I've done this before, I've just been away from
> Access for too long (I think).
>
> I assumed that I can create an expression in a query that would refer to a
> column of a combo box on a form as such:
>
> Expr1: Forms![frmBuild]![cboBuildType].Column(1)
>
> I get an error when I try to run the query: Undefined Function 'Expr1:
> Forms![frmBuild]![cboBuildType].Column' in experession
>
> (notice the column number is missing)
>
> When I enter  ? Forms![frmBuild]![cboBuildType].Column(1) in the immediate
> window, it displays the value as expected
>
> I made a work around to get this going by creating a public function and
> calling it as such:
>
> Expr2: GetBatchName()
>
>
> Public Function GetBatchName()
>     GetBatchName = Forms![frmBuild]![cboBuildType].Column(1) & " " &
> Format(Forms![frmBuild]![cboMonth].Column(2), "yyyymmdd")
> End Function
>
> I know, I know, there isn't any error handling, just testing it out...
>
> Anyway, what am I forgetting?
>
> It's been a  while.
>
> D
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>