Parse text string and insert characters

R

Robert5833

Good day all;

Vista Business / Access 2007

I am trying to manipulate a string of data from a barcode scanner, which
will then be appended to a table. The RS232 device is using a wedge
interface. The string to be manipulated is Text. The data string is as
follows:

090723103216*RRL*232*ALL

The string text is comprised of the following elements:

090723 date stamp (always 6 characters)
103216 time stamp (always 6 characters)
* data string sequence break(s) (scanner always 3, always *)
RRL user defined prefix (ASCII)
232 barcode information (can be any number of alpha/num characters)
ALL user defined suffix (ASCII) (know it’s a reserved word, but it’s a
stored value, not used for VBA purposes)

The parsed string text will be appended to a table as follows:

Table: tblBarcodeData
Fields (attributes date/time/num/txt as appropriate): fldDate, fldTime,
fldPfx, fldScnNum, fldSfx

I am able to parse the data using SQL as follows:

SELECT lnkSprdshtBarcodeUpload.F1, Mid([F1],4,3) AS NewP_Date, Mid([F1],7,6)
AS NewP_Time, Mid([F1],14,3) AS NewP_Pfx, Right([F1],4) AS NewP_Sfx_1,
Left([NewP_Sfx_1],3) AS NewP_Sfx
FROM lnkSprdshtBarcodeUpload;

Yields; 723, 103216, RRL, 232, and ALL

What I cannot figure out how to do is insert the date and time special
characters ( / and : ) to result in the following:

7/23
10:32:16

Any advice or examples (SQL or VBA) would be greatly appreciated, and thank
you in advance for your help.

Robert
 
J

Jerry Whittle

First off I believe that you need to rethink the "Mid([F1],4,3)". It's going
to probably cause you problems starting in October.....

Mid([F1],3,2) & "/" & Mid([F1],5,2) AS NewP_Date,

The above will have the leadiing zero for January through September.

Once you get that working, I'm sure you'll figured out the time.
 
J

John Spencer

Use the format function on the string values.

Format(Mid(F1,7,6),"@@\:mad:@\:mad:@")

Format(Mid(F1,3,4),"@@\/@@")
By the way you need to get all four characters or you will introduce errors
into your data when the date hits October (10 not 0), November (11 not 1),
and December (12 not 2). If you try to pass just the month and day, Access
will convert the date to the present year. This will work in most cases for
you but if you are importing data on Jan 1, 2010 and the data is stamped with
091231 the date will be converted to December 31, 2010. You should get the
full date and then use the following format to convert it to an unambiguous
date string.

FORMAT(Mid(F1,1,6),"\2\0@@\/@@\/@@")
which should return a string
"2009/07/23"
which can be converted from a string to a date value using
CDate(FORMAT(Mid(F1,1,6),"\2\0@@\/@@\/@@"))

And you can also convert the time string to a date value using CDate also
CDate(Format(Mid(F1,7,6),"@@\:mad:@\:mad:@"))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
R

Robert5833

Hi Jerry,
This is awesome! And thank you for the tip on the date issue. I had dropped
"09" because I didn't know if the text string could be converted. Now that I
know it can, it's back in.

Many thanks!

Robert

Jerry Whittle said:
First off I believe that you need to rethink the "Mid([F1],4,3)". It's going
to probably cause you problems starting in October.....

Mid([F1],3,2) & "/" & Mid([F1],5,2) AS NewP_Date,

The above will have the leadiing zero for January through September.

Once you get that working, I'm sure you'll figured out the time.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Robert5833 said:
Good day all;

Vista Business / Access 2007

I am trying to manipulate a string of data from a barcode scanner, which
will then be appended to a table. The RS232 device is using a wedge
interface. The string to be manipulated is Text. The data string is as
follows:

090723103216*RRL*232*ALL

The string text is comprised of the following elements:

090723 date stamp (always 6 characters)
103216 time stamp (always 6 characters)
* data string sequence break(s) (scanner always 3, always *)
RRL user defined prefix (ASCII)
232 barcode information (can be any number of alpha/num characters)
ALL user defined suffix (ASCII) (know it’s a reserved word, but it’s a
stored value, not used for VBA purposes)

The parsed string text will be appended to a table as follows:

Table: tblBarcodeData
Fields (attributes date/time/num/txt as appropriate): fldDate, fldTime,
fldPfx, fldScnNum, fldSfx

I am able to parse the data using SQL as follows:

SELECT lnkSprdshtBarcodeUpload.F1, Mid([F1],4,3) AS NewP_Date, Mid([F1],7,6)
AS NewP_Time, Mid([F1],14,3) AS NewP_Pfx, Right([F1],4) AS NewP_Sfx_1,
Left([NewP_Sfx_1],3) AS NewP_Sfx
FROM lnkSprdshtBarcodeUpload;

Yields; 723, 103216, RRL, 232, and ALL

What I cannot figure out how to do is insert the date and time special
characters ( / and : ) to result in the following:

7/23
10:32:16

Any advice or examples (SQL or VBA) would be greatly appreciated, and thank
you in advance for your help.

Robert
Click to expand...
Click to expand...
 
R

Robert5833

Hi John,

I've already tried this out and it works great! On the date issue and
picking up all the information, I didn't know the text string could be
formatted (as year info it does appear in reverse order) so I had dropped the
09 characters. But CDate / Format resolved that perfectly!

Thanks a bunch for the help, and for the quick reply!

Robert

John Spencer said:
Use the format function on the string values.

Format(Mid(F1,7,6),"@@\:mad:@\:mad:@")

Format(Mid(F1,3,4),"@@\/@@")
By the way you need to get all four characters or you will introduce errors
into your data when the date hits October (10 not 0), November (11 not 1),
and December (12 not 2). If you try to pass just the month and day, Access
will convert the date to the present year. This will work in most cases for
you but if you are importing data on Jan 1, 2010 and the data is stamped with
091231 the date will be converted to December 31, 2010. You should get the
full date and then use the following format to convert it to an unambiguous
date string.

FORMAT(Mid(F1,1,6),"\2\0@@\/@@\/@@")
which should return a string
"2009/07/23"
which can be converted from a string to a date value using
CDate(FORMAT(Mid(F1,1,6),"\2\0@@\/@@\/@@"))

And you can also convert the time string to a date value using CDate also
CDate(Format(Mid(F1,7,6),"@@\:mad:@\:mad:@"))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Good day all;

Vista Business / Access 2007

I am trying to manipulate a string of data from a barcode scanner, which
will then be appended to a table. The RS232 device is using a wedge
interface. The string to be manipulated is Text. The data string is as
follows:

090723103216*RRL*232*ALL

The string text is comprised of the following elements:

090723 date stamp (always 6 characters)
103216 time stamp (always 6 characters)
* data string sequence break(s) (scanner always 3, always *)
RRL user defined prefix (ASCII)
232 barcode information (can be any number of alpha/num characters)
ALL user defined suffix (ASCII) (know it’s a reserved word, but it’s a
stored value, not used for VBA purposes)

The parsed string text will be appended to a table as follows:

Table: tblBarcodeData
Fields (attributes date/time/num/txt as appropriate): fldDate, fldTime,
fldPfx, fldScnNum, fldSfx

I am able to parse the data using SQL as follows:

SELECT lnkSprdshtBarcodeUpload.F1, Mid([F1],4,3) AS NewP_Date, Mid([F1],7,6)
AS NewP_Time, Mid([F1],14,3) AS NewP_Pfx, Right([F1],4) AS NewP_Sfx_1,
Left([NewP_Sfx_1],3) AS NewP_Sfx
FROM lnkSprdshtBarcodeUpload;

Yields; 723, 103216, RRL, 232, and ALL

What I cannot figure out how to do is insert the date and time special
characters ( / and : ) to result in the following:

7/23
10:32:16

Any advice or examples (SQL or VBA) would be greatly appreciated, and thank
you in advance for your help.

Robert
Click to expand...
Click to expand...
 
R

Robert5833

All;

In case anyone is interested in seeing the final result, the following is
the current SQL to parse and convert this text string:

090723103216*RRL*232*ALL

To these formatted data: 7/23/2009, 10:32:16 AM, RRL, ALL

SELECT lnkSprdshtBarcodeUpload.F1, Mid([F1],14,3) AS NewP_Pfx, Right([F1],4)
AS NewP_Sfx_1, Left([NewP_Sfx_1],3) AS NewP_Sfx,
CDate(Format(Mid([F1],1,6),"""20""@@\/@@\/@@")) AS TrueP_Date,
CDate(Format(Mid([F1],7,6),"@@\:mad:@\:mad:@")) AS TrueP_Time
FROM lnkSprdshtBarcodeUpload;

Very handy indeed!

Thanks so much!

Robert


John Spencer said:
Use the format function on the string values.

Format(Mid(F1,7,6),"@@\:mad:@\:mad:@")

Format(Mid(F1,3,4),"@@\/@@")
By the way you need to get all four characters or you will introduce errors
into your data when the date hits October (10 not 0), November (11 not 1),
and December (12 not 2). If you try to pass just the month and day, Access
will convert the date to the present year. This will work in most cases for
you but if you are importing data on Jan 1, 2010 and the data is stamped with
091231 the date will be converted to December 31, 2010. You should get the
full date and then use the following format to convert it to an unambiguous
date string.

FORMAT(Mid(F1,1,6),"\2\0@@\/@@\/@@")
which should return a string
"2009/07/23"
which can be converted from a string to a date value using
CDate(FORMAT(Mid(F1,1,6),"\2\0@@\/@@\/@@"))

And you can also convert the time string to a date value using CDate also
CDate(Format(Mid(F1,7,6),"@@\:mad:@\:mad:@"))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Good day all;

Vista Business / Access 2007

I am trying to manipulate a string of data from a barcode scanner, which
will then be appended to a table. The RS232 device is using a wedge
interface. The string to be manipulated is Text. The data string is as
follows:

090723103216*RRL*232*ALL

The string text is comprised of the following elements:

090723 date stamp (always 6 characters)
103216 time stamp (always 6 characters)
* data string sequence break(s) (scanner always 3, always *)
RRL user defined prefix (ASCII)
232 barcode information (can be any number of alpha/num characters)
ALL user defined suffix (ASCII) (know it’s a reserved word, but it’s a
stored value, not used for VBA purposes)

The parsed string text will be appended to a table as follows:

Table: tblBarcodeData
Fields (attributes date/time/num/txt as appropriate): fldDate, fldTime,
fldPfx, fldScnNum, fldSfx

I am able to parse the data using SQL as follows:

SELECT lnkSprdshtBarcodeUpload.F1, Mid([F1],4,3) AS NewP_Date, Mid([F1],7,6)
AS NewP_Time, Mid([F1],14,3) AS NewP_Pfx, Right([F1],4) AS NewP_Sfx_1,
Left([NewP_Sfx_1],3) AS NewP_Sfx
FROM lnkSprdshtBarcodeUpload;

Yields; 723, 103216, RRL, 232, and ALL

What I cannot figure out how to do is insert the date and time special
characters ( / and : ) to result in the following:

7/23
10:32:16

Any advice or examples (SQL or VBA) would be greatly appreciated, and thank
you in advance for your help.

Robert
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Replace or insert characters in text string 6

Top