Replace or insert characters in text string

[Robert5833]

Good day all;

Windows Vista for Business / Access 2007

This is a follow-on issue to a previous post where I was looking for and was
provided with a solution for text string parsing and formatting. (Thank you
Jerry Whittle and John Spencer!)

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.

So far what I have works well in most instances, but there are still two
cases that I need help with; *variable length strings* and *strings
containing ASCII type characters*.

One example *but not the only one* is the variable length string issue as a
result of formatted dates not being of a fixed length; e.g., 1/1/09, vs.
11/11/09.

Here’s the raw string from the barcode scanner:

090728100223*DeviceID001*1110%7%20%2009*RRL

I need a method to measure the length of the string, and add a leading “0â€
if needed, to result in a final string of 01/01/09 to match what would be the
maximum length string, of say 10/10/09.

The second issue, ASCII type characters, results from dates which I collect
using a barcode. The VB conversion of 7/20/2009 to a Code 39 barcode is
returned by the barcode scanner as %7%20%2009.

For this I need a method to replace the “%†character with the “/†character
in the returned text string.

With the help of this group on similar topics, I am able to parse the date
(text string), replace the “%†with the “/†character, and then concatenate
the results, but I would like to do that with a single method.

Here’s what I have now:

Rplc_%_1: Replace(Mid([F1],31,2),"%","/")
Rplc_%_2: Replace(Mid([F1],33,3),"%","/")
Rplc_%_3: Mid([F1],36,4)
DateConc: [rplc_%_1] & "" & [rplc_%_2] & "" & [rplc_%_3]

I have tried unsuccessfully to group the three replace methods into a single
string, but kept getting the “Wrong number of arguments†error.

Note; as I write this post I see that in order to add a leading “0†to the
“day†portion of the string, I may have to parse the string into its
constituent parts anyway.

The barcode scanner does have a timestamp (always 6 characters; 010109,
etc.), which is useful, and thanks to the help I have received from this
group I am able to format that information into the proper data type. For
that I am using the following:

TrueP_Date: CDate(Format(Mid([F1],1,6),"""20""@@\/@@\/@@"))

But there are other instances where I need to scan an actual date.

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

Robert

 

[KARL DEWEY]

Try this --
Create a table Date_Parse --
One_Digit Two_Digit
/1/ /01/
/2/ /02/
/3/ /03/
/4/ /04/
/5/ /05/
/6/ /06/
/7/ /07/
/8/ /08/
/9/ /09/

SELECT Replace(Replace([YourField],"%","/"),[One_Digit],[Two_Digit]) AS Expr2
FROM YourTable, Date_Parse
WHERE (((Replace([YourField],"%","/")) Like "*" & [One_Digit] & "*"));

--
Build a little, test a little.

Good day all;

Windows Vista for Business / Access 2007

This is a follow-on issue to a previous post where I was looking for and was
provided with a solution for text string parsing and formatting. (Thank you
Jerry Whittle and John Spencer!)

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.

So far what I have works well in most instances, but there are still two
cases that I need help with; *variable length strings* and *strings
containing ASCII type characters*.

One example *but not the only one* is the variable length string issue as a
result of formatted dates not being of a fixed length; e.g., 1/1/09, vs.
11/11/09.

Here’s the raw string from the barcode scanner:

090728100223*DeviceID001*1110%7%20%2009*RRL

I need a method to measure the length of the string, and add a leading “0â€
if needed, to result in a final string of 01/01/09 to match what would be the
maximum length string, of say 10/10/09.

The second issue, ASCII type characters, results from dates which I collect
using a barcode. The VB conversion of 7/20/2009 to a Code 39 barcode is
returned by the barcode scanner as %7%20%2009.

For this I need a method to replace the “%†character with the “/†character
in the returned text string.

With the help of this group on similar topics, I am able to parse the date
(text string), replace the “%†with the “/†character, and then concatenate
the results, but I would like to do that with a single method.

Here’s what I have now:

Rplc_%_1: Replace(Mid([F1],31,2),"%","/")
Rplc_%_2: Replace(Mid([F1],33,3),"%","/")
Rplc_%_3: Mid([F1],36,4)
DateConc: [rplc_%_1] & "" & [rplc_%_2] & "" & [rplc_%_3]

I have tried unsuccessfully to group the three replace methods into a single
string, but kept getting the “Wrong number of arguments†error.

Note; as I write this post I see that in order to add a leading “0†to the
“day†portion of the string, I may have to parse the string into its
constituent parts anyway.

The barcode scanner does have a timestamp (always 6 characters; 010109,
etc.), which is useful, and thanks to the help I have received from this
group I am able to format that information into the proper data type. For
that I am using the following:

TrueP_Date: CDate(Format(Mid([F1],1,6),"""20""@@\/@@\/@@"))

But there are other instances where I need to scan an actual date.

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

Robert

 

[John Spencer]

If you can parse out the string portion
%7%20%2009 or %12%31%2008 or even %1%1%2007
then you can change that to a real date using
CDate(Mid(Replace(DateString,"%","-"),2))

And if you want that formatted as a string in month, day, year form then
format the result.
Format(CDate(Mid(Replace(DateString,"%","-"),2)),"mm/dd/yyyy")

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

Good day all;

Windows Vista for Business / Access 2007

This is a follow-on issue to a previous post where I was looking for and was
provided with a solution for text string parsing and formatting. (Thank you
Jerry Whittle and John Spencer!)

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.

So far what I have works well in most instances, but there are still two
cases that I need help with; *variable length strings* and *strings
containing ASCII type characters*.

One example *but not the only one* is the variable length string issue as a
result of formatted dates not being of a fixed length; e.g., 1/1/09, vs.
11/11/09.

Here’s the raw string from the barcode scanner:

090728100223*DeviceID001*1110%7%20%2009*RRL

I need a method to measure the length of the string, and add a leading “0â€
if needed, to result in a final string of 01/01/09 to match what would be the
maximum length string, of say 10/10/09.

The second issue, ASCII type characters, results from dates which I collect
using a barcode. The VB conversion of 7/20/2009 to a Code 39 barcode is
returned by the barcode scanner as %7%20%2009.

For this I need a method to replace the “%†character with the “/†character
in the returned text string.

With the help of this group on similar topics, I am able to parse the date
(text string), replace the “%†with the “/†character, and then concatenate
the results, but I would like to do that with a single method.

Here’s what I have now:

Rplc_%_1: Replace(Mid([F1],31,2),"%","/")
Rplc_%_2: Replace(Mid([F1],33,3),"%","/")
Rplc_%_3: Mid([F1],36,4)
DateConc: [rplc_%_1] & "" & [rplc_%_2] & "" & [rplc_%_3]

I have tried unsuccessfully to group the three replace methods into a single
string, but kept getting the “Wrong number of arguments†error.

Note; as I write this post I see that in order to add a leading “0†to the
“day†portion of the string, I may have to parse the string into its
constituent parts anyway.

The barcode scanner does have a timestamp (always 6 characters; 010109,
etc.), which is useful, and thanks to the help I have received from this
group I am able to format that information into the proper data type. For
that I am using the following:

TrueP_Date: CDate(Format(Mid([F1],1,6),"""20""@@\/@@\/@@"))

But there are other instances where I need to scan an actual date.

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

Robert

 

[Robert5833]

Hi Karl,

I tried the approach you provided, and it works great!

I continue to be amazed at the level of knowledge in this community; and I
continue to be overwhelmed by people like you, who show such a willingness to
help out underlings like myself.

Thanks again!

Try this --
Create a table Date_Parse --
One_Digit Two_Digit
/1/ /01/
/2/ /02/
/3/ /03/
/4/ /04/
/5/ /05/
/6/ /06/
/7/ /07/
/8/ /08/
/9/ /09/

SELECT Replace(Replace([YourField],"%","/"),[One_Digit],[Two_Digit]) AS Expr2
FROM YourTable, Date_Parse
WHERE (((Replace([YourField],"%","/")) Like "*" & [One_Digit] & "*"));

--
Build a little, test a little.

Good day all;

Windows Vista for Business / Access 2007

This is a follow-on issue to a previous post where I was looking for and was
provided with a solution for text string parsing and formatting. (Thank you
Jerry Whittle and John Spencer!)

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.

So far what I have works well in most instances, but there are still two
cases that I need help with; *variable length strings* and *strings
containing ASCII type characters*.

One example *but not the only one* is the variable length string issue as a
result of formatted dates not being of a fixed length; e.g., 1/1/09, vs.
11/11/09.

Here’s the raw string from the barcode scanner:

090728100223*DeviceID001*1110%7%20%2009*RRL

I need a method to measure the length of the string, and add a leading “0â€
if needed, to result in a final string of 01/01/09 to match what would be the
maximum length string, of say 10/10/09.

The second issue, ASCII type characters, results from dates which I collect
using a barcode. The VB conversion of 7/20/2009 to a Code 39 barcode is
returned by the barcode scanner as %7%20%2009.

For this I need a method to replace the “%†character with the “/†character
in the returned text string.

With the help of this group on similar topics, I am able to parse the date
(text string), replace the “%†with the “/†character, and then concatenate
the results, but I would like to do that with a single method.

Here’s what I have now:

Rplc_%_1: Replace(Mid([F1],31,2),"%","/")
Rplc_%_2: Replace(Mid([F1],33,3),"%","/")
Rplc_%_3: Mid([F1],36,4)
DateConc: [rplc_%_1] & "" & [rplc_%_2] & "" & [rplc_%_3]

I have tried unsuccessfully to group the three replace methods into a single
string, but kept getting the “Wrong number of arguments†error.

Note; as I write this post I see that in order to add a leading “0†to the
“day†portion of the string, I may have to parse the string into its
constituent parts anyway.

The barcode scanner does have a timestamp (always 6 characters; 010109,
etc.), which is useful, and thanks to the help I have received from this
group I am able to format that information into the proper data type. For
that I am using the following:

TrueP_Date: CDate(Format(Mid([F1],1,6),"""20""@@\/@@\/@@"))

But there are other instances where I need to scan an actual date.

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

Robert

 

[Robert5833]

Hi John,

Thank you for the quick response, and of course for the suggested method! I
am deeply grateful for your patience, and your willingness to help out.

As this post is similar to another of mine that you replied to, I was
careful to review your original reply so as not to miss anything you had
offered, and how that might have resolved my questions in this post.

It is very likely though, that as green as I am with some of this, you may
have already provided an answer. If I missed it, I apologize…

Relative to pseudo date text strings, the problem I am having is with
parsing, and because of the variable length. Here’s what I am getting for two
different string lengths:

Parsing query field; TimeCardDate: (Mid([F1],30,10))

Raw string (for 11/12/2009 (tilde added to denote parse coordinates);

090728100226*DeviceID001*1110 ~ %11%12%2009 ~ *RRL

Returns; %11%12%200

Raw string (for 7/21/2009; tilde added to denote parse coordinates);

090728100224*DeviceID001*1110 ~ %7%21%2009 ~ *RRL

Returns; %7%21%2009

Two side notes here; one, I am using character breaks or separators in the
data returned by the barcode scanner, and two, there is RS232 scanner
interface software available to do some of the parsing and formatting for the
values returned by the scanner device. On the latter point, I am having
issues bringing the hardware and interface software together, so what I am
doing here is a work around for that issue, however, what I am learning here
will help me with many other similar situations with the need to parse string
text.

Meanwhile, I have tried to use what you provided, modified to include the
applicable field, but it errors out, which I am sure is due to my lack of
understanding of the principles and constructs of the method.

Here’s what I have: TryThis: CDate(Mid(Replace([F1],"%","-"),2))

The F1 field holds this string: 090728100224*DeviceID001*1110%7%21%2009*RRL

To help me better understand what I’m doing wrong, would it be too much to
ask that you break down the pieces of the following string from your reply?

If you can parse out the string portion
%7%20%2009 or %12%31%2008 or even %1%1%2007
then you can change that to a real date using
CDate(Mid(Replace(DateString,"%","-"),2))

For what it’s worth; I’ve had no formal training on either SQL or VBA, and
all of what I know to this point is what I’ve learned by trial and error
*heavily weighted on the error side* <smile>, and of course, the wealth of
knowledge offered by members of this user group.

Thank you in advance; and as always, I appreciate whatever additional help
or suggestions you may have to offer.

Robert

If you can parse out the string portion
%7%20%2009 or %12%31%2008 or even %1%1%2007
then you can change that to a real date using
CDate(Mid(Replace(DateString,"%","-"),2))

And if you want that formatted as a string in month, day, year form then
format the result.
Format(CDate(Mid(Replace(DateString,"%","-"),2)),"mm/dd/yyyy")

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

Good day all;

Windows Vista for Business / Access 2007

This is a follow-on issue to a previous post where I was looking for and was
provided with a solution for text string parsing and formatting. (Thank you
Jerry Whittle and John Spencer!)

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.

So far what I have works well in most instances, but there are still two
cases that I need help with; *variable length strings* and *strings
containing ASCII type characters*.

One example *but not the only one* is the variable length string issue as a
result of formatted dates not being of a fixed length; e.g., 1/1/09, vs.
11/11/09.

Here’s the raw string from the barcode scanner:

090728100223*DeviceID001*1110%7%20%2009*RRL

I need a method to measure the length of the string, and add a leading “0â€
if needed, to result in a final string of 01/01/09 to match what would be the
maximum length string, of say 10/10/09.

The second issue, ASCII type characters, results from dates which I collect
using a barcode. The VB conversion of 7/20/2009 to a Code 39 barcode is
returned by the barcode scanner as %7%20%2009.

For this I need a method to replace the “%†character with the “/†character
in the returned text string.

With the help of this group on similar topics, I am able to parse the date
(text string), replace the “%†with the “/†character, and then concatenate
the results, but I would like to do that with a single method.

Here’s what I have now:

Rplc_%_1: Replace(Mid([F1],31,2),"%","/")
Rplc_%_2: Replace(Mid([F1],33,3),"%","/")
Rplc_%_3: Mid([F1],36,4)
DateConc: [rplc_%_1] & "" & [rplc_%_2] & "" & [rplc_%_3]

I have tried unsuccessfully to group the three replace methods into a single
string, but kept getting the “Wrong number of arguments†error.

Note; as I write this post I see that in order to add a leading “0†to the
“day†portion of the string, I may have to parse the string into its
constituent parts anyway.

The barcode scanner does have a timestamp (always 6 characters; 010109,
etc.), which is useful, and thanks to the help I have received from this
group I am able to format that information into the proper data type. For
that I am using the following:

TrueP_Date: CDate(Format(Mid([F1],1,6),"""20""@@\/@@\/@@"))

But there are other instances where I need to scan an actual date.

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

Robert

 

[John Spencer]

First problem is that I thought you were already parsing the string into its
various components. Obviously not.

090728100224*DeviceID001*1110%7%21%2009*RRL

From your earlier post I think that should be split up into at least
090728 (Date) plus 100224 (Time?)
DeviceID001
1110%7%21%2009
RRL

Extracting the date field
MID(SomeData,30) gets all the characters from 30 on
%7%21%2009*RRL

Left(X,InstrRev(X,"*")-1) gets just the date portion where x is the first
expression.
Instr determines the position of the * and Left gets all of the string up
to the star
%7%21%2009

Replacing X with the first expression
Left(MID(SomeData,30),InstrRev(MID(SomeData,30),"*")-1)

Now we can use the earlier expression

CDate(Mid(Replace(Left(MID(SomeData,30),InstrRev(MID(SomeData,30),"*")-1),"%","-"),2))

Replace changes the % to -
The additional mid strips off the first -
CDate changes the string to a real date value

And since the date always starts at the 31st character you can change the
final expression to:

CDate(Replace(Left(MID(SomeData,31),InstrRev(MID(SomeData,31),"*")-1),"%","-"))

If the date does not always start at character 31, but it is always after the
first % sign, then you will need to calculate the start point using
Instr(1,SomeData,"%") and replace 31 in the above expression with this.

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

Hi John,

Thank you for the quick response, and of course for the suggested method! I
am deeply grateful for your patience, and your willingness to help out.

As this post is similar to another of mine that you replied to, I was
careful to review your original reply so as not to miss anything you had
offered, and how that might have resolved my questions in this post.

It is very likely though, that as green as I am with some of this, you may
have already provided an answer. If I missed it, I apologize…

Relative to pseudo date text strings, the problem I am having is with
parsing, and because of the variable length. Here’s what I am getting for two
different string lengths:

Parsing query field; TimeCardDate: (Mid([F1],30,10))

Raw string (for 11/12/2009 (tilde added to denote parse coordinates);

090728100226*DeviceID001*1110 ~ %11%12%2009 ~ *RRL

Returns; %11%12%200

Raw string (for 7/21/2009; tilde added to denote parse coordinates);

090728100224*DeviceID001*1110 ~ %7%21%2009 ~ *RRL

Returns; %7%21%2009

Two side notes here; one, I am using character breaks or separators in the
data returned by the barcode scanner, and two, there is RS232 scanner
interface software available to do some of the parsing and formatting for the
values returned by the scanner device. On the latter point, I am having
issues bringing the hardware and interface software together, so what I am
doing here is a work around for that issue, however, what I am learning here
will help me with many other similar situations with the need to parse string
text.

Meanwhile, I have tried to use what you provided, modified to include the
applicable field, but it errors out, which I am sure is due to my lack of
understanding of the principles and constructs of the method.

Here’s what I have: TryThis: CDate(Mid(Replace([F1],"%","-"),2))

The F1 field holds this string: 090728100224*DeviceID001*1110%7%21%2009*RRL

To help me better understand what I’m doing wrong, would it be too much to
ask that you break down the pieces of the following string from your reply?

If you can parse out the string portion
%7%20%2009 or %12%31%2008 or even %1%1%2007
then you can change that to a real date using
CDate(Mid(Replace(DateString,"%","-"),2))

For what it’s worth; I’ve had no formal training on either SQL or VBA, and
all of what I know to this point is what I’ve learned by trial and error
*heavily weighted on the error side* <smile>, and of course, the wealth of
knowledge offered by members of this user group.

Thank you in advance; and as always, I appreciate whatever additional help
or suggestions you may have to offer.

Robert

If you can parse out the string portion
%7%20%2009 or %12%31%2008 or even %1%1%2007
then you can change that to a real date using
CDate(Mid(Replace(DateString,"%","-"),2))

And if you want that formatted as a string in month, day, year form then
format the result.
Format(CDate(Mid(Replace(DateString,"%","-"),2)),"mm/dd/yyyy")

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

Good day all;

Windows Vista for Business / Access 2007

This is a follow-on issue to a previous post where I was looking for and was
provided with a solution for text string parsing and formatting. (Thank you
Jerry Whittle and John Spencer!)

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.

So far what I have works well in most instances, but there are still two
cases that I need help with; *variable length strings* and *strings
containing ASCII type characters*.

One example *but not the only one* is the variable length string issue as a
result of formatted dates not being of a fixed length; e.g., 1/1/09, vs.
11/11/09.

Here’s the raw string from the barcode scanner:

090728100223*DeviceID001*1110%7%20%2009*RRL

I need a method to measure the length of the string, and add a leading “0â€
if needed, to result in a final string of 01/01/09 to match what would be the
maximum length string, of say 10/10/09.

The second issue, ASCII type characters, results from dates which I collect
using a barcode. The VB conversion of 7/20/2009 to a Code 39 barcode is
returned by the barcode scanner as %7%20%2009.

For this I need a method to replace the “%†character with the “/†character
in the returned text string.

With the help of this group on similar topics, I am able to parse the date
(text string), replace the “%†with the “/†character, and then concatenate
the results, but I would like to do that with a single method.

Here’s what I have now:

Rplc_%_1: Replace(Mid([F1],31,2),"%","/")
Rplc_%_2: Replace(Mid([F1],33,3),"%","/")
Rplc_%_3: Mid([F1],36,4)
DateConc: [rplc_%_1] & "" & [rplc_%_2] & "" & [rplc_%_3]

I have tried unsuccessfully to group the three replace methods into a single
string, but kept getting the “Wrong number of arguments†error.

Note; as I write this post I see that in order to add a leading “0†to the
“day†portion of the string, I may have to parse the string into its
constituent parts anyway.

The barcode scanner does have a timestamp (always 6 characters; 010109,
etc.), which is useful, and thanks to the help I have received from this
group I am able to format that information into the proper data type. For
that I am using the following:

TrueP_Date: CDate(Format(Mid([F1],1,6),"""20""@@\/@@\/@@"))

But there are other instances where I need to scan an actual date.

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

Robert

 

[Robert5833]

Hi John,

This works perfectly! Thank you very much! And thank you too for the
detailed breakdown of the different methods, and their functions. I really
appreciate the opportunity to become better educated in the various methods
and techniques.

As they day; teach a man to fish…

Thanks again!
Robert

First problem is that I thought you were already parsing the string into its
various components. Obviously not.

090728100224*DeviceID001*1110%7%21%2009*RRL

From your earlier post I think that should be split up into at least
090728 (Date) plus 100224 (Time?)
DeviceID001
1110%7%21%2009
RRL

Extracting the date field
MID(SomeData,30) gets all the characters from 30 on
%7%21%2009*RRL

Left(X,InstrRev(X,"*")-1) gets just the date portion where x is the first
expression.
Instr determines the position of the * and Left gets all of the string up
to the star
%7%21%2009

Replacing X with the first expression
Left(MID(SomeData,30),InstrRev(MID(SomeData,30),"*")-1)

Now we can use the earlier expression

CDate(Mid(Replace(Left(MID(SomeData,30),InstrRev(MID(SomeData,30),"*")-1),"%","-"),2))

Replace changes the % to -
The additional mid strips off the first -
CDate changes the string to a real date value

And since the date always starts at the 31st character you can change the
final expression to:

CDate(Replace(Left(MID(SomeData,31),InstrRev(MID(SomeData,31),"*")-1),"%","-"))

If the date does not always start at character 31, but it is always after the
first % sign, then you will need to calculate the start point using
Instr(1,SomeData,"%") and replace 31 in the above expression with this.

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

Hi John,

Thank you for the quick response, and of course for the suggested method! I
am deeply grateful for your patience, and your willingness to help out.

As this post is similar to another of mine that you replied to, I was
careful to review your original reply so as not to miss anything you had
offered, and how that might have resolved my questions in this post.

It is very likely though, that as green as I am with some of this, you may
have already provided an answer. If I missed it, I apologize…

Relative to pseudo date text strings, the problem I am having is with
parsing, and because of the variable length. Here’s what I am getting for two
different string lengths:

Parsing query field; TimeCardDate: (Mid([F1],30,10))

Raw string (for 11/12/2009 (tilde added to denote parse coordinates);

090728100226*DeviceID001*1110 ~ %11%12%2009 ~ *RRL

Returns; %11%12%200

Raw string (for 7/21/2009; tilde added to denote parse coordinates);

090728100224*DeviceID001*1110 ~ %7%21%2009 ~ *RRL

Returns; %7%21%2009

Two side notes here; one, I am using character breaks or separators in the
data returned by the barcode scanner, and two, there is RS232 scanner
interface software available to do some of the parsing and formatting for the
values returned by the scanner device. On the latter point, I am having
issues bringing the hardware and interface software together, so what I am
doing here is a work around for that issue, however, what I am learning here
will help me with many other similar situations with the need to parse string
text.

Meanwhile, I have tried to use what you provided, modified to include the
applicable field, but it errors out, which I am sure is due to my lack of
understanding of the principles and constructs of the method.

Here’s what I have: TryThis: CDate(Mid(Replace([F1],"%","-"),2))

The F1 field holds this string: 090728100224*DeviceID001*1110%7%21%2009*RRL

To help me better understand what I’m doing wrong, would it be too much to
ask that you break down the pieces of the following string from your reply?

If you can parse out the string portion
%7%20%2009 or %12%31%2008 or even %1%1%2007
then you can change that to a real date using
CDate(Mid(Replace(DateString,"%","-"),2))

For what it’s worth; I’ve had no formal training on either SQL or VBA, and
all of what I know to this point is what I’ve learned by trial and error
*heavily weighted on the error side* <smile>, and of course, the wealth of
knowledge offered by members of this user group.

Thank you in advance; and as always, I appreciate whatever additional help
or suggestions you may have to offer.

Robert

If you can parse out the string portion
%7%20%2009 or %12%31%2008 or even %1%1%2007
then you can change that to a real date using
CDate(Mid(Replace(DateString,"%","-"),2))

And if you want that formatted as a string in month, day, year form then
format the result.
Format(CDate(Mid(Replace(DateString,"%","-"),2)),"mm/dd/yyyy")

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

Robert5833 wrote:
Good day all;

Windows Vista for Business / Access 2007

This is a follow-on issue to a previous post where I was looking for and was
provided with a solution for text string parsing and formatting. (Thank you
Jerry Whittle and John Spencer!)

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.

So far what I have works well in most instances, but there are still two
cases that I need help with; *variable length strings* and *strings
containing ASCII type characters*.

One example *but not the only one* is the variable length string issue as a
result of formatted dates not being of a fixed length; e.g., 1/1/09, vs.
11/11/09.

Here’s the raw string from the barcode scanner:

090728100223*DeviceID001*1110%7%20%2009*RRL

I need a method to measure the length of the string, and add a leading “0â€
if needed, to result in a final string of 01/01/09 to match what would be the
maximum length string, of say 10/10/09.

The second issue, ASCII type characters, results from dates which I collect
using a barcode. The VB conversion of 7/20/2009 to a Code 39 barcode is
returned by the barcode scanner as %7%20%2009.

For this I need a method to replace the “%†character with the “/†character
in the returned text string.

With the help of this group on similar topics, I am able to parse the date
(text string), replace the “%†with the “/†character, and then concatenate
the results, but I would like to do that with a single method.

Here’s what I have now:

Rplc_%_1: Replace(Mid([F1],31,2),"%","/")
Rplc_%_2: Replace(Mid([F1],33,3),"%","/")
Rplc_%_3: Mid([F1],36,4)
DateConc: [rplc_%_1] & "" & [rplc_%_2] & "" & [rplc_%_3]

I have tried unsuccessfully to group the three replace methods into a single
string, but kept getting the “Wrong number of arguments†error.

Note; as I write this post I see that in order to add a leading “0†to the
“day†portion of the string, I may have to parse the string into its
constituent parts anyway.

The barcode scanner does have a timestamp (always 6 characters; 010109,
etc.), which is useful, and thanks to the help I have received from this
group I am able to format that information into the proper data type. For
that I am using the following:

TrueP_Date: CDate(Format(Mid([F1],1,6),"""20""@@\/@@\/@@"))

But there are other instances where I need to scan an actual date.

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

Robert