Separator # and want to parse

[Tarbuza]

I have a text in the following format:

#Vol.1#Pg.132#December 2002#Source:ABC
##Pg.140#January 2001#

How can I parse it through query and store values as follows through update query:

First one after # sign should go in a field called Volume (for instance Vol.1). The second one after # should go into a field called Page (for instance Pg.132. The third one after # should into a field called PubDate field (for instance: December 2002) and the fourth field should go into a field called source (for instance ABC).

If you look at the second one, sometimes there won't be value.

Any helps would be greatly appreciated.

 

[KenReay]

Hi

Have you considered importing the text file containing the data into a table, and using that table in an update query.

What you have here is effectively a Comma Seperated vaiable (CSV) file, but using # in place of ,.

If you set up an import specification, you can tell Access you are using a seperator # instead of , and it will do all of the parsing for you, puting the data into a table.

See File \ Get External Data \ Import, then see Advanced and Specification buttons.

Note if you are using Access 2000 / XP, then the file extension must be one of those regisistered for text file imports, otherwise you will get a very misleading error messgae about the database being read only, .txt is an acceptable extension.

Hope this helps Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Tarbuza]

Thanks.

It's not a text file but it's alreay in Access. There is one field which contains # separators. Therefore, import is out of questions.

It has to be solved by parsing through queries. Any help would be greatly appreciated.

 

[MichaelRed]

i am not totally in agreement that "It has to be solved by parsing through queries", unless (of course) that is part of the 'assignment'. At least a small mod to Ken's suggestion would be simply to 'export' the field (along with the record key) through the TransferText method (to generate the test file), and at least try his approach.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[KenReay]

Hi

Before leaving the idea of import, you could write it out to a (temporary) text file, and then import it, all in code.

But if you want to parse it you need to look at the following functions

Instr()
Left()
Mid()
Right()

what level of VBA knowledge do you have?
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MichaelRed]

also consider the Split function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Tarbuza]

Very little knowledge of VBA. It's alreay in Microsoft Access in one single field parsed with #. Should I export to a text file and then re-import it?

Please advise.

 

[KenReay]

Hi

If you have little VBA, then I would say that is the easiest way to go Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Tarbuza]

I have done a little bit of VBA with Microsoft Word but not with Microsoft Access.

I would appreciate a jumpstart code or something that I can do on update query etc.

Thanks.

 

[Tarbuza]

Any helps would be greatly appreciated. Thanks.