VB Help

[Guest]

I have a table imported from a text file.
The format of the file has a record that relates to rows below it. but is
not shown against all lines
In excell I use the following

Record is in column A- output to column B
In colum B I would use the following-=If(a2="",B1,A2)- This then puts the
record against all lines- until it gets to a new record- and acts on that then

Can someone tell me the VB coding required to do this function in access

 

[Guest]

You can create a query that return the desire field

Select A1,B1,IIF(A1="" Or A1 is null,B1,A1) as C1 From TableName

C1 is the field that return the desire value

 

[Guest]

THanks for that
That formula only returns a value from the same line- I need the formula /
VB code to direct to the line above if the condition is not met- i.e if a2 is
null goto a1 else a2

Thanks for you help anyway

David

 

[SteveS]

THanks for that
That formula only returns a value from the same line- I need the formula /
VB code to direct to the line above if the condition is not met- i.e if a2 is
null goto a1 else a2

Thanks for you help anyway

David

:

David,

There is no "above" in a table. There is no guarantee that the order will
remain the same.

This is how I would do it.

Make a copy of the table and name it "tblTemp".
Add a new field named "lngOrder" (type number - long)

Now you have a table (tblTemp) with three fields - Field_A, Field_B and lngOrder.

In a Sub, create varA string, varB string, Kount as integer.
(Note: change varA & varB to the proper type.)

Create a recordset, based on tblTemp.

Open the text file and read the first line.
Parse the line (to varA & varB), add a new record, use 1 for the lngOrder field.

Save the value (Field_B?) to a variable - varNewValue.
Kount = 2

Loop thru the text file,
read line from text file
parse line
check if isnull(varA) then use varNewValue
add new record
if isnull(varA) then varNewValue= varB
'Increment counter
Kount = kount+1
Until text file EOF

do cleanup


Then run an Append query to append Field_A and Field_B to the Main table.

It would be easier to answer knowing the field names and having an example of
the data, but this should get you moving in the right direction.

HTH