part II:
The following 2 steps could be done as a single step, but to make it
simple we'll split the tasks.
7. Create another query in design view. At the Show Table prompt
select tmpInputdata and click the add button. This query is where all
the
hard work comes. Save the query as qryMakeParsedInputDataTable.
Looking at all the formulas you had listed in your original post, at
least
half of the formula content was conditional logic based on invalid data
that we just stripped out in qryCleanInputData. Here's a breakdown
of what you need to do (you may want to do a lot of this off-line in a
text editor):
a. Replace the target ranges with the name you want for each column
followed by a ":", as the following example shows:
'Range("A" & i).FormulaR1C1 =
"=IF(RC[4]<>"""",TEXT(RC[4]*1,""yyyymm""),"""")"
Becomes
StatementDte:=IF(RC[4]<>"""",TEXT(RC[4]*1,""yyyymm""),"""")
b. Remove all the conditional logic in your formulas that depended upon
whether TransDte was invalid, since the query we created cleans that
up. So TransDte becomes:
TransDte:=TRIM(LEFT(TRIM(RC[10]),10))
c. Replace the references to cells with Col1 - Col5 references. The
references are in the format TABLE_NAME![FIELD_NAME], so TransDte now
becomes
TransDte:=TRIM(LEFT(TRIM(tmpInputData![Col1]),10)
d. Replace any other cell references that refer to newly created
columns to their new field names. So COAS becomes
COAS:=IF(LEFT(TRIM(tmpInputData![Col1]),5)="COAS:",TRIM(MID(tmpInputData![Col1],6,4)),[StatementDte])
{note the new reference to StatementDte}
e. Replace all your Excel functions with Access equivalents. The only
2 I see are:
- TEXT([TransDte], "yyymm") becomes Format$([TransDte], "yyyymm")
{Note, I've changed the Cell reference to the column name that will
be created in this query}
- IF(condition, true, false) becomes Iif(condition, true, false) {note
the extra "i" in "Iif"}.
f. Replace any remaining """"'s with "" if there are any.
g. At this point you should have a formula that matches every column in
the table. Take each formula and paste it into a separate field in
the query. In the query editor, select query from the menu and select
make table Query. Give the new table the name tmpParsedInputData.
h. Save the query and close it. Then run it by double-clicking on it.
i. Open the resulting table and check the contents. If it doesn't look
right, go back and check your formulas in the query.