Calculated Fields

[Guest]

I'm new to access. I have my access database linked to an ODBC database
file. I'm frustrated with the limitations in the relationships between
linked tables, but am still trying to use Access as a tool.

In our system, the sales part we is a nine character field. Within the
sales part, there are characters that have particular importance.

Is there a way to add a calculated field so that characters 6-8 would appear
in a field by themselves? These particulare numbers make up a code that we
often query on.

If not, is there specific syntax that I could use in my query that would
allow me to query on these characters only, in the field that contains nine
characters? Is there a syntax that would allow me to query on multiple
combinations at the same time.

 

[Steve Huff]

Is there a way to add a calculated field so that characters 6-8 would
appear
in a field by themselves? These particulare numbers make up a code that
we
often query on.

The mid function should do the job for you. If the field name is PARTNUMBER
then you would say MID([PARTNUMBER],6,3) to return characters 6-8.

 

[Rick B]

You would not store calculated fields in the database. That is redundant.
You are on the right track when you ask if there is a way to pull them to
the query. Any time you need a calculated result, jsut pull it at that
time. You can do this in the query, report or form as you mentioned.

Rick B

 

[Joseph Meehan]

I'm new to access. I have my access database linked to an ODBC
database file. I'm frustrated with the limitations in the
relationships between linked tables, but am still trying to use
Access as a tool.

In our system, the sales part we is a nine character field. Within
the sales part, there are characters that have particular importance.

Is there a way to add a calculated field so that characters 6-8 would
appear in a field by themselves? These particulare numbers make up a
code that we often query on.

To add to what Steve wrote, You need to use the function in a query,
form or report.

 

[Dirk Goldgar]

I'm new to access. I have my access database linked to an ODBC
database file. I'm frustrated with the limitations in the
relationships between linked tables, but am still trying to use
Access as a tool.

[...]

Others have addressed your main question, but I'm curious about what
about the relationships is frustrating you. If you're new to Access,
maybe you're going about things the wrong way and we can suggest some
better approach.

 

[John Vinson]

On Mon, 27 Dec 2004 08:01:01 -0800, "Excel GuRu" <Excel

I'm new to access. I have my access database linked to an ODBC database
file. I'm frustrated with the limitations in the relationships between
linked tables, but am still trying to use Access as a tool.

In our system, the sales part we is a nine character field. Within the
sales part, there are characters that have particular importance.

Is there a way to add a calculated field so that characters 6-8 would appear
in a field by themselves? These particulare numbers make up a code that we
often query on.

If not, is there specific syntax that I could use in my query that would
allow me to query on these characters only, in the field that contains nine
characters? Is there a syntax that would allow me to query on multiple
combinations at the same time.

If the two portions of the Sales Part field have an independent
existance, then they should be stored in your table as different
fields. A Primary Key does NOT need to be a single field - it can
consist of up to ten fields.

I'd suggest storing this "number" in three (indexed) fields, bytes 1
to 5; 6 to 8; and 9 by itself. It's very easy to concatenate the three
fields for display, and each field can be searched or sorted, either
on its own or in conjunction with the other fields.


John W. Vinson[MVP]

 

[Guest]

John,
How do you concatenate fields in access? I keep gettng a message stating
that When I try to refresh my query, I keep getting a message stating that
"concatenate"
is an undefined function.

Send along the syntax or exact name of the function if you can.

 

[Guest]

use the & symbol to concatenate, for example:

[field1] & " " & [field2] & " " & [field3]

-Steve Huff
http://www.huffs.us

 

[John Vinson]

John,
How do you concatenate fields in access? I keep gettng a message stating
that When I try to refresh my query, I keep getting a message stating that
"concatenate"
is an undefined function.

Send along the syntax or exact name of the function if you can.

If the three fields are named Salespart1, Salespart2, and Salespart3
you could set the Control Source of a textbox to

=[Salespart1] & [Salespart2] & [Salespart3]

The & is the Access concatenation operator.

John W. Vinson[MVP]