Sgurdon:
Ok, well sorry - I assumed you knew more about Access. Let's back up and
discuss a couple things.
You will import your data into a table. A table stores data and not
formulas. The only similarity between Access' table and Excel is the fact
they look similar and each box is called a cell. The similarities absolutely
end there. Now you are in Access world - things are different and you
accomplish tasks using different objects versus cell or sheet manipulation.
I am not going to explain the details of a table and its design view versus
data view, but you need to conceptualize each row is a record and each column
is a field in that record and you don't/can't store formulas/calculated
values in a table. Instead we use queries.
In your case you need to create a new query in design view, then add the
table which contains your imported data onto the Layout Area (top part of
query design screen). The grided area is called the Design Grid. You drag
fields from tables in the Layout Area down to a column in the Design Grid.
You also create formulas (referred to as calculated fields in Access) in the
Design Grid.
In your case you do not need to drag any fields down to the design to build
a calculated field. Your formulas will be built along the same logic used in
Excel, using many of the same function names you used in Excel (except SEARCH
is now what we call InStr). In Access we don't use the equals sign (=) in
formulas in queries; it is improper syntax. Just begin typing your formula
in the row labeled Field.
In Access we don't refer to data by using cell intersection names such as
A2, G74; we use field names. When you write a formula it is assumed/implied
you are working with a record in a table. You don't reference fields/values
in other records, the context is the one record in the table you are working
with. Most folks really struggle with this coming from Excel, but it just is.
So anytime you are thinking of referring to cell A1 you would instead type
the field name from the table you are working with in the Layout Area.
Remember, cell references are a thing of Excel, not Access. Now you are
working with fields (columns) and records (rows). Once you enter a formula
in a query it will calculate a value for each record in the table. There is
no AutoFilling or copying and pasting of formulas. What you do for one
record is done for all records in a query.
A note about calculated fields: after you enter your formula, then tab out
of the field or click elsewhere you will notice a name is given to your to
your formula - Expr1:. This is because in a sense you have created a new
'field' and every field must have a name. You will notice this name as the
column header where you are used to seeing A, B, C, D, E, F. You can rename
the field to something meaningful by selecting Expr and overwriting it. You
will learn in time to do this when entering your formula similar to - Item:
Mid( . . .
See how this helps you, and then come back with additional questions.
Seth Schwarm
Sgurdon said:
Not snapping in Access; maybe i'm missing something.
This the formula I used in Excel to extract the first variable, how does it
translate to Access?
Cell A2=date
Cell B2=Stored ITEM: 4610201465 QTY: 5 LOC: A 105-130-C
This is the function I use: "=MID(B2,1,SEARCH(":",B2)-1)" it brings back
Stored ITEM
If I can get that translated to use the InStr function I should be able to
figure how to use it to pull all of the tasks.
Expr1: Mid([action],1,InStr(":",[action])-1)
I tried replacing "Search" with "InStr" and "B2" with "[action]", the name
of the field containing the string, but it returns an error message.
--
Always in search of knowledge
Seth Schwarm said:
If you were successful in Excel, then this will be a snap for you in Access.
Instead of the SEARCH function use the InStr function along with Left, Mid
and Right and you'll be done in no time.
You would do this in a query, not a table.
Seth Schwarm
:
I have some text strings that I need to extract specific data from.
SAMPLES:
Stored ITEM: 4610201465 QTY: 5 LOC: A 105-130-C
Rewarehouse Pick: 4610060729 QTY: 44 LOC: A 101-111-T SERIAL:
Rewarehouse Stored: 4610060729 QTY: 44 LOC: A 101-111-A SERIAL:
I need to extract the task: Stored Item; Rewarehouse pick; Rewarehouse
Stored.
The task always ends at the ":".
I need to extract the item number. It is usually 10 characters but some are
14 characters, The item number is always followed by QTY.
I need to extract the QTY. It is usually a number from 0 to 10,000. It
always follows the Qty's ":" and ends 1 space before the LOC.
Finally, I need to extract the LOC. It is always 11 characters.
In the past I have used Excel and used the MID, SEARCH, and other functions
to extract from the strings but I would like to import the CSV file directly
into Access and do it all from there.
Thanking everyone in advance.
Sgurdon
