Please Help...Using the column function within a query field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to use the column function when defining an expression within
a query?

I typed

Labor1:Val([Labor].Column(1))

But, Access gives the "Undefined function "[Labor].Column(1))"

Please help
 
What is LABOR? Is it a control on a form?

If so, you MIGHT be able to use the following.

Labor1: EVal(Forms!SomeFormName!Labor.Column(1))
 
I think the Eval() works like:
EVal("Forms!SomeFormName!Labor.Column(1)")


--
Duane Hookom
MS Access MVP


John Spencer (MVP) said:
What is LABOR? Is it a control on a form?

If so, you MIGHT be able to use the following.

Labor1: EVal(Forms!SomeFormName!Labor.Column(1))
Is it possible to use the column function when defining an expression
within
a query?

I typed

Labor1:Val([Labor].Column(1))

But, Access gives the "Undefined function "[Labor].Column(1))"

Please help
Click to expand...
Click to expand...
 
Labor is a column within my query. I have created a query for components. I
used combo box so the user can decide which component they want to use. But
I need to calculate the total labor from their inputs.

Here is structure, I have tblComponents which lists everything, from this I
have creted several queries that are unique based on the type of component.
The form that the user interfaces with is based on "tblComponents"

Basically, I am trying to keep track of components used, the prices and
labor associated with each component.

I hope this clarifies

John Spencer (MVP) said:
What is LABOR? Is it a control on a form?

If so, you MIGHT be able to use the following.

Labor1: EVal(Forms!SomeFormName!Labor.Column(1))
Is it possible to use the column function when defining an expression within
a query?

I typed

Labor1:Val([Labor].Column(1))

But, Access gives the "Undefined function "[Labor].Column(1))"

Please help
Click to expand...
Click to expand...
 
If Labor is a column, then it doesn't have a column of its own. I'm afraid that
I have no idea what you are trying to do.


Labor is a column within my query. I have created a query for components. I
used combo box so the user can decide which component they want to use. But
I need to calculate the total labor from their inputs.

Here is structure, I have tblComponents which lists everything, from this I
have creted several queries that are unique based on the type of component.
The form that the user interfaces with is based on "tblComponents"

Basically, I am trying to keep track of components used, the prices and
labor associated with each component.

I hope this clarifies

John Spencer (MVP) said:
What is LABOR? Is it a control on a form?

If so, you MIGHT be able to use the following.

Labor1: EVal(Forms!SomeFormName!Labor.Column(1))
Is it possible to use the column function when defining an expression within
a query?

I typed

Labor1:Val([Labor].Column(1))

But, Access gives the "Undefined function "[Labor].Column(1))"

Please help
Click to expand...
Click to expand...
Click to expand...
 
I think I am trying to do something similar. I'm getting all mixed up with
the form controls. And trying to get the value of a field instead of the
underlying number -

Basically I have this:
A table called Projects with seven columns (ProjName, City, ProjEng,
ProjPhase) etc. In the table, I have assigned all the data types as "text",
except ProjEng - which I created as a number - because it's taking that data
(that populates the Projects.ProjEng field) from an Contacts table.
Everything worked just fine thus far.
Then I added a Projects Form with a link from Issues Table, to edit the
Projects.
When all of the fields in the Projects Form were text-only, everything was
fine. However I changed the fields on the form to combo boxes in order to
limit the selection to a list (to prevent typographical errors in the
Projects Table, hoping to prevent duplicate records because of typos.
Again, all of the drop down lists (on the Projects Form) work fine (pulling
the data from the Projects Table to the combo drop down list- and -also
updating the selection back to the Projects Table according to the selection.
However, since the ProjEng column is a number-type in the Projects Table it
is showing as a number in the drop down list - instead of the names.

I've tried but can't seem to find a way to get it to select the Value of the
data in the field instead of the actual number.
Any solutions to this would be greatly appreciated.
 
I think I am trying to do something similar. I'm getting all mixed up with
the form controls. And trying to get the value of a field instead of the
underlying number -
Click to expand...

The value of the field, as stored in your Table, *IS* the number.

That simple fact is concealed from your view by Microsoft's
misdesigned, misleading, and all but useless Lookup Wizard misfeature.

Your table CONTAINS the number; the lookup field connects that number
to a text value *in a different table*, the Lookup table.

See http://www.mvps.org/access/lookupfields.htm for a critique of the
Lookup Field type.
Basically I have this:
A table called Projects with seven columns (ProjName, City, ProjEng,
ProjPhase) etc. In the table, I have assigned all the data types as "text",
except ProjEng - which I created as a number - because it's taking that data
(that populates the Projects.ProjEng field) from an Contacts table.
Click to expand...

Correction; it's not "taking" that data from a contacts table. It's
*linking to* the data in the contacts table. The text exists in the
Contacts table, and only in the Contacts table.
Everything worked just fine thus far.
Then I added a Projects Form with a link from Issues Table, to edit the
Projects.
Click to expand...

What sort of "link"?
When all of the fields in the Projects Form were text-only, everything was
fine. However I changed the fields on the form to combo boxes in order to
limit the selection to a list (to prevent typographical errors in the
Projects Table, hoping to prevent duplicate records because of typos.
Again, all of the drop down lists (on the Projects Form) work fine (pulling
the data from the Projects Table to the combo drop down list- and -also
updating the selection back to the Projects Table according to the selection.
However, since the ProjEng column is a number-type in the Projects Table it
is showing as a number in the drop down list - instead of the names.
Click to expand...

Change the ColumnWidths property of this combo box so that the column
width of the number field is 0. E.g. if your combo is based on a query
selecting the ProjEng ID and the Contacts LastName, you can use a
ColumnWidths property of

0;1

to conceal the ID and display the name.


John W. Vinson[MVP]
 
Oh boy... After reading that article.. no wonder why.........
That's exactly the merry-go-round I've been dealing with. So what do you
suggest? Just get rid of the lookup column in the original table? and put the
data? It seems inappropriate to duplicate the same data in two separate
tables (ProjEng in the Projects table -- and the same names in the AssignedTo
Table).
Do you have any other suggestions? I'm getting confused here.
Thanks in advance.
Sarah
 
Oh boy... After reading that article.. no wonder why.........
That's exactly the merry-go-round I've been dealing with. So what do you
suggest? Just get rid of the lookup column in the original table? and put the
data? It seems inappropriate to duplicate the same data in two separate
tables (ProjEng in the Projects table -- and the same names in the AssignedTo
Table).
Do you have any other suggestions? I'm getting confused here.
Click to expand...

Lookup TABLES are fine, and are used routinely.

But you need to use them in the right place - on a Form, not in a
table.

It is emphatically *not* necessary to use a Lookup Field in a table to
do so. You can go into your table and for each lookup field, view its
Properties; select the Lookup tab; and change it to Textbox instead of
Combo Box. This will leave the numeric link in your table (which is
fine, and appropriate) - but you'll be able to *see* it now.

You may be making the (rather common) assumption that "if it's not in
the table then I cannot see it or use it". That assumption is WRONG.
Tables are not designed for seeing or printing data - they're designed
for STORING data, and should be kept "under the hood". If you want to
see the ProjEng name in association with project data there are many
ways to do it. The two most common are to use a Combo Box on a Form;
the bound column of the combo being the ID, and the only visible
column being the looked-up name. That way the computer sees the
unique, stable, short numeric ID and the user sees a person's name,
and they both are happy.

For printing names on a Report, you can create a Query joining the
Projects table to the "people" table; you would pull the project
information from the projects table, and the name information from the
lookup table.

John W. Vinson[MVP]
 
Back
Top