Select the last record
 

2 questions - Text File example:

Account,Status,Val
001,NB,0
001,PI,50
001,PI,100
001,CE,100
002,PE,500
002,PI,250
002,PU,0
002,PU,0
003,PE,100
003,CE,100
I would like to pick up ALL records of any account where there is NO CE
status and write it out to a separate file i.e.
002,PE,500
002,PI,250
002,PU,0
002,PU,0

I would like to pick up the LAST record of any account where PU = 0
002,PU,0
and write it out to a separate file

It is easy to do this using a database but a need has arisen to do it
upfront i.e before importing the data into the database and I thought a
simple VBA app will suffice. I'm not looking for anything intricate - just a
simple example of how to code this.

Hil

Select the last record
 

Can anyone help out?

"Hilton" wrote in message
...
2 questions - Text File example:

Account,Status,Val
001,NB,0
001,PI,50
001,PI,100
001,CE,100
002,PE,500
002,PI,250
002,PU,0
002,PU,0
003,PE,100
003,CE,100
I would like to pick up ALL records of any account where there is NO CE
status and write it out to a separate file i.e.
002,PE,500
002,PI,250
002,PU,0
002,PU,0

I would like to pick up the LAST record of any account where PU = 0
002,PU,0
and write it out to a separate file

It is easy to do this using a database but a need has arisen to do it
upfront i.e before importing the data into the database and I thought a
simple VBA app will suffice. I'm not looking for anything intricate - just
a
simple example of how to code this.

Hil

Select the last record
 

"Hilton" wrote ...

Text File example:

Account,Status,Val
001,NB,0
001,PI,50
001,PI,100
001,CE,100
002,PE,500
002,PI,250
002,PU,0
002,PU,0
003,PE,100
003,CE,100

I would like to pick up ALL records of any account where there is NO CE
status and write it out to a separate file i.e.
It is easy to do this using a database but a need has arisen to do it
upfront i.e before importing the data into the database

As you are database proficient, you will no doubt appreciate that a
sql solution is simplest. A text file can be queried as a data source
using a Jet provider/driver. Therefore, execute this Jet syntax sql
against a Jet datasource e.g. a connection to a .mdb or .xls (but not
a text file connection), in a query object in the MS Access app, etc:

SELECT Account,Status,Val
INTO [Text;Database=C:\MyFolder\;].MyNewTextFile#txt
FROM [Text;Database=C:\MyFolder\;].MyExistingTextFile#txt
WHERE Status<'CE'
;

002,PE,500
002,PI,250
002,PU,0
002,PU,0

I would like to pick up the LAST record of any account where PU = 0
002,PU,0
and write it out to a separate file

'Last row' has no meaning in sql. If you had a timestamp column, we
could determine which is the most *recent* row. In other words, you
need to specify a sort order before we can determine which is the
'last'.

Jamie.

--