Parse Feild

[mattc66 via AccessMonster.com]

I have a feild of data that I need to parse. It is formated as follows: (This
comes from an external data souce this is how I get the data).

SHIP DATE B 6/5/06

I want to seperate it into 3 seperate feilds.

FLD1: SHIP DATE (Text) This will be static
FLD2: B (Text) This will be a Q, B, or and E
FLD3 6/5/06 (Date) This will be the desired ship date. Sometimes the user
types - instead of /.

How would it type the VB code to parse this data?

Thanks
Matt

 

[Marshall Barton]

I have a feild of data that I need to parse. It is formated as follows: (This
comes from an external data souce this is how I get the data).

SHIP DATE B 6/5/06

I want to seperate it into 3 seperate feilds.

FLD1: SHIP DATE (Text) This will be static
FLD2: B (Text) This will be a Q, B, or and E
FLD3 6/5/06 (Date) This will be the desired ship date. Sometimes the user
types - instead of /.

I'm not sure I understand the rule for Fld2, but here's a
shot at it:

Fld1: Left(field, 9)
Fld2: Mid(field, 11, 1)
Fld3: CDate(Mid(field, 13))

 

[mattc66 via AccessMonster.com]

That works okay, but it's not very forgiving. If they had a typed the ship
date and added ":" after it the query fails with that extra space.

Is there a way to just parse based on spaces, to be a little more forgiving?
In my query I am going to locate this date if the FLD1 = like "Ship*" and if
FLD2 = like "Q" or like "B" or like "E".

This data is coming from the order detail and this information is found in a
line comment. In a perfect world I would change the source so that the QBE
info was a dedicated data entry fld, but I cant.

Note: Fld2 is an internal flag that identifies the ship date. Q = Quoted the
customer a ship date, therefore it must ship on or before that date. E = It
must ship on this date, EXPECTED. B = Budgeted date or standard ship date.
Can ship around this date no later then 2 days after date. More then you
wanted to know, but I find it helpful to know what the data means.

Thanks
Matt

I have a feild of data that I need to parse. It is formated as follows: (This
comes from an external data souce this is how I get the data).

[quoted text clipped - 7 lines]

FLD3 6/5/06 (Date) This will be the desired ship date. Sometimes the user
types - instead of /.

I'm not sure I understand the rule for Fld2, but here's a
shot at it:

Fld1: Left(field, 9)
Fld2: Mid(field, 11, 1)
Fld3: CDate(Mid(field, 13))

 

[John Nurick]

Hi Matt,

You could use the rgxExtract function at
http://www.j.nurick.dial.pipex.com/Code/vbRegex/rgxExtract.htm. This
pattern

"(SHIP\s+DATE)\W+([BEQ])\s+(\d{1,2}[-/.]\d{1,2}[-/.](?:\d\d)?\d\d)"

will find "SHIP DATE" in upper or lower case with any number of spaces
between the words, ignore any punctuation, spaces or tabs following DATE
and grab B, E or Q, and then grab a date with its pieces separated by
slashes, hyphens or periods.

That works okay, but it's not very forgiving. If they had a typed the ship
date and added ":" after it the query fails with that extra space.

Is there a way to just parse based on spaces, to be a little more forgiving?
In my query I am going to locate this date if the FLD1 = like "Ship*" and if
FLD2 = like "Q" or like "B" or like "E".

This data is coming from the order detail and this information is found in a
line comment. In a perfect world I would change the source so that the QBE
info was a dedicated data entry fld, but I cant.

Note: Fld2 is an internal flag that identifies the ship date. Q = Quoted the
customer a ship date, therefore it must ship on or before that date. E = It
must ship on this date, EXPECTED. B = Budgeted date or standard ship date.
Can ship around this date no later then 2 days after date. More then you
wanted to know, but I find it helpful to know what the data means.

Thanks
Matt

I have a feild of data that I need to parse. It is formated as follows: (This
comes from an external data souce this is how I get the data).

[quoted text clipped - 7 lines]

FLD3 6/5/06 (Date) This will be the desired ship date. Sometimes the user
types - instead of /.

I'm not sure I understand the rule for Fld2, but here's a
shot at it:

Fld1: Left(field, 9)
Fld2: Mid(field, 11, 1)
Fld3: CDate(Mid(field, 13))

 

[mattc66 via AccessMonster.com]

This is awsome... Thank you it works great.

Hi Matt,

You could use the rgxExtract function at
http://www.j.nurick.dial.pipex.com/Code/vbRegex/rgxExtract.htm. This
pattern

"(SHIP\s+DATE)\W+([BEQ])\s+(\d{1,2}[-/.]\d{1,2}[-/.](?:\d\d)?\d\d)"

will find "SHIP DATE" in upper or lower case with any number of spaces
between the words, ignore any punctuation, spaces or tabs following DATE
and grab B, E or Q, and then grab a date with its pieces separated by
slashes, hyphens or periods.

That works okay, but it's not very forgiving. If they had a typed the ship
date and added ":" after it the query fails with that extra space.

[quoted text clipped - 28 lines]