Re: Excel Calcs in Access

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Is there anywhere I can get an example of that code and how to use it?

This may be completely irrelevant, cause im new at this, but what if I added
a row number for each line and then use that as a reference?


"John Nurick" wrote:

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.


.

Relevant Pages

  • Re: Selection Combined with Search
    ... Searching for the entire value in the cell wouldn't be required if you ... changed xlWhole to xlPart ... The identifier begins with a period. ... If rngStart Is Nothing Then Exit Sub ...
    (microsoft.public.excel.programming)
  • Re: Excel Calcs in Access
    ... to sort the records into the order you need. ... certain that the query will find the correct "cell above" (i.e. the ... section identifier which is in the same column as the "D"s. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Help with Multiple Excel Sheets
    ... Its not too important if the identifier is in column A as further data ... Do you want the identifier from cell B2 in the first cell of ... >> First problem is that Row X is variable in every sheet but the row ... >> Then open the next workbook and repeat but appending the data to the ...
    (microsoft.public.excel.programming)
  • Re: HELP with PHP and javascript in a grid matrix
    ... > way to have an identifier ... I'm not an expert, and the PHP princes may have a more elegant way, but I've ... Create a separate hidden field for each cell which contains the ... When the form is submitted the values of the hidden fields will be sent up ...
    (alt.php)
  • Re: HELP with PHP and javascript in a grid matrix
    ... > way to have an identifier ... I'm not an expert, and the PHP princes may have a more elegant way, but I've ... Create a separate hidden field for each cell which contains the ... When the form is submitted the values of the hidden fields will be sent up ...
    (comp.lang.php)