Re: Excel Calcs in Access

It's only possible to do this sort of thing reliably with a query if
there is a field or combination of fields in the data that can be used
to sort the records into the order you need. Otherwise, you can't be
certain that the query will find the correct "cell above" (i.e. the
"previous" record.

This doesn't appear to be the case with your data. So the "Access"
approach might be to write VBA code that reads a line from the text
file, stores the value you're interested in for comparison next time,
uses the previously stored value to process the current line, and then
loops back to read the next line.



On Fri, 10 Mar 2006 08:13:27 -0800, dcozzi
<dcozzi@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

After reading some other posts, I want to correct my question.

Can the result of the excel function described below be achieved thru a
query in Access?

"dcozzi" wrote:

Is there any chance you can help me with the second part regarding the
formula I only know how to run in excel? It is in step 2 below and it is the
first calc that needs to be done in order to create the first part of the
identifier. Just to clarify, the text file I currently receive only has a
section identifier which is in the same column as the "D"s. I need to use the
formula to say if the cell to the right is not a "D", then it equals that
cell, if it is a "D", use the result of the cell above. Is this possible in
access?

2. I perform the following calculations create the identifier.
a. I need to take out the following characters from column C via a find all
and replace all
Find All ], replace all with nothing
Find all ., replace with nothing.
B. In A2, I run following function (via a macro all the way down to line
60,000)
=IF(A2="D",A1,B2)
D in the above expression is a specific character

An example of the data manipulating I achieve in step 2 is below. B and C
are what I paste in. D is my unique identifier.

IDENTIFER = (CONCENTRATE A2,C2)
ROW COLUMN A COLUMN B COLUMN C IDENTIFIER
1 [P2004JURAT1 [P2004JURAT1 1 [P2004JURAT11
2 [P2004JURAT1 D 2
3 [P2004JURAT2 [P2004JURAT2 1
4 [P2004JURAT2 D 2
5 [P2004JURAT3 [P2004JURAT3 1
6 [P2004JURAT3 D 2
7 [P2004JURAT3 D 3
8 [P2004JURAT3 D 4
9 [P2004JURAT3 D 5
10 [P2004JURAT3 D 6
11 [P2004JURAT3 D 7


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

.