UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Instr Left, Right & Mid Functions, Access 2007    
 
   
chris1970
post Jan 3 2018, 10:29 AM
Post#1



Posts: 6
Joined: 15-November 17



I have successfully written some VBA code to extract data from the USPS website and view the results which are below. I can also find the position of the text I am looking for (highlighted in red). What I cannot do is understand how to use the Left, Right and Mid functions in combination to return results highlighted in blue and put them in fields in my database. The whole text is stored in a variable called readText. I have read dozens of posts and articles and for some reason my brain cannot see the logic of these functions. Thank you in advance for any help.

Tracking number:

4201316592612927005010180001915636


Tracking Details

Tuesday, November 21st:

Delivered at 2:07 PM

WATERLOO, NY


and

Tracking Details

Monday, December 11th:

Electronic Shipping Sent to USPS at 10:12 PM

and

Wednesday, November 15th:

Electronic Shipping Info Received at 9:55 AM

Inwood, NY

and

Ship to address:

El Paso, TX 79930


Or

*Tracking number not found. Please check your original tracking number. If the problem continues please contact your vendor for order support.
Go to the top of the page
 
Larry Larsen
post Jan 3 2018, 10:39 AM
Post#2


UA Editor + Utterly Certified
Posts: 24,134
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
Would be helpful to see the data in it's raw state as you would see it in [readText] variable..

Is what we are seeing the true format..??

Using the L/R/Mid functions it helps to see the raw data to work out where the functions can be used..
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
doctor9
post Jan 3 2018, 10:55 AM
Post#3


UtterAccess Editor
Posts: 17,984
Joined: 29-March 05
From: Wisconsin


Chris,

I'm with Larry here... this doesn't seem like a representation of one shipment, since the delivery address is in New York, and the ship to address is in Texas. Are the "and"s supposed to be included in the text?

If you post what's actually in readText (maybe wrap it in Code tags to preserve any unusual spacing), we can show you how to go through the string, extracting pieces of it as you go. Then, once you've extracted all of the pieces, you can use an Append query to write those pieces of data to your fields. It would also help to know the names/datatypes of the fields.

Hope this helps,

Dennis

EDIT: If those "and"s are really supposed to be "or"s (i.e. there are multiple, differently-formatted examples), can you clarify this? For example, it's odd that the words "Tracking Details" are above the bit about electronic info being SENT, but not above the bit about electronic info being RECEIVED.

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
kfield7
post Jan 3 2018, 01:44 PM
Post#4



Posts: 783
Joined: 12-November 03
From: Iowa Lot


Maybe a little higher level discussion than the specifics of Left, Right and Mid are in order.

I've used Access to read html from a realtor site, for example.
The process is identify how the target text is marked.

Go to the webpage and view it in html, find some of the text you are looking for, and note what key phrases come before and after it (hint, look for <>), the beginning and ending markers, and the position between those markers where the text actually begins and ends.

Create a function(s) to look for the keys within readText then parse out the result(s) you want.

Sometimes you have to do this in multiple levels - markers within markes.

I don't have my own code in front of me right now, but I use Instr() to find the position of the key text (beginning key and end key), add an appropriate count to get to the beginning of my target, mid() to grab the text between, and Trim() to remove spaces.
I also use a custom function to parse out numeric values (because Val() fails on any non-numeric characters other than "." and I think "-" - i.e., it chokes on commas and dollar signs).

Sometimes in between the <> markers you need to add or subtract from the beginning and ending markers to zone in on your target, but there should be some level of consistency, albeit different for each target type, if for no other reason than the html is computer generated bo begin with.

Now if you need more specific information that the Microsoft help sites and UtterAccess don't answer, let us know.
Go to the top of the page
 
orange999
post Jan 3 2018, 02:08 PM
Post#5



Posts: 1,724
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Chris,

There are descriptions and samples for the Left, Right and Mid functions @ TechonTheNet

As has been mentioned, if you provide some sample text, then more focused responses are likely.

Good luck.

--------------------
Good luck with your project!
Go to the top of the page
 
doctor9
post Jan 3 2018, 02:36 PM
Post#6


UtterAccess Editor
Posts: 17,984
Joined: 29-March 05
From: Wisconsin


Personally, I'd consider using the Split() function to simplify things, but until I see what the string actually can contain, I can't be certain that would help.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
chris1970
post Jan 3 2018, 07:29 PM
Post#7



Posts: 6
Joined: 15-November 17



This is an actual result






?


TAC_Returns_Icons_Calendar_AddTAC_Returns_Icons_Calendar_AddedTAC_Returns_Icons_
Delivered_ErrorTAC_Returns_Icons_Delivered_FillTAC_Returns_Icons_Delivered_LineTA
C_Returns_Icons_OFD_FillTAC_Returns_Icons_OFD_LineTAC_Returns_Icons_OrderPrep_Fil
lTAC_Returns_Icons_OrderPrep_LineTAC_Returns_Icons_Refund_FillTAC_Returns_Icons_R
efund_LineTAC_Returns_Icons_ReturnPrep_FillTAC_Returns_Icons_ReturnPrep_LineTAC_R
eturns_Icons_ReturnReceived_FillTAC_Returns_Icons_ReturnReceived_LineTAC_Returns_
Icons_Shipped_FillTAC_Returns_Icons_Shipped_LineTAC_Returns_Icons_In_Transit_Line
TAC_Returns_Icons_In_Transit_Fillminus_iconplus_icon


Preparing Order


Shipped


Out For Delivery


Delivered






Preparing Order


Shipped


Out For Delivery


Delivered


Tracking number:

4209815592612927005010180001954796


Return Policy

Every product we sell undergoes the strictest quality control procedure before it reaches you ? and we're confident that you'll be delighted with the end result. Hotter's 100% Happy Promise is hassle free and allows you to send your item(s) straight back to us for a replacement or refund within 90 days of purchase, so you can order with complete peace of mind.


Learn More




Tracking Details

Tuesday, November 28th:

Delivered at 3:02 PM

SEATTLE, WA


Tuesday, November 28th:

Out for Delivery at 7:51 AM

SEATTLE, WA


Tuesday, November 28th:

Arrival at USPS at 3:52 AM

SEATTLE, WA


Tuesday, November 28th:

Arrival at USPS at 1:19 AM

SEATTLE, WA


Friday, November 24th:

Electronic Shipping Sent to USPS at 5:36 PM




Friday, November 24th:

In transit at 4:46 PM

Sparks, NV


Friday, November 24th:

In transit at 4:29 PM

Sparks, NV


Friday, November 24th:

In transit at 8:29 AM

Sparks, NV


Wednesday, November 22nd:

In transit at 12:46 PM

Fishers, IN


Tuesday, November 21st:

In transit at 6:25 PM

Elizabeth, NJ


Tuesday, November 21st:

Arrived at Shipping Facility at 1:44 PM

Elizabeth, NJ


Wednesday, November 15th:

Electronic Shipping Info Received at 9:56 AM

Inwood, NY



Ship to address:

Shoreline, WA 98155


?







window.NREUM||(NREUM={});NREUM.info={"errorBeacon":"bam.nr-data.net","licenseKey":"eee7876c21","agent":"","beacon":"bam.nr-data.net","applicationTime":837,"applicationID":"15945358","transactionName":"ZwRWMBFTXEBQUUVcV15OZxQRW1xUcl1fQUpfDVgBER1GQVBRWlxWV05PERFedlpCQl1UQU1OTxA
RU1FYWFxWe01dA1EWHhIadHRmGA==","queueTime":0}
Tracking number found

Go to the top of the page
 
chris1970
post Jan 3 2018, 08:49 PM
Post#8



Posts: 6
Joined: 15-November 17



The results I want to glean from all of this information is:

- Ship to Address
- Electronic Shipping Info Received
- Electronic Shipping Sent to USPS
- The most recent activity (in this case "Delivered at..."
- Tracking number

I apologise if I am not giving enough info but this thing is driving me nuts.
Go to the top of the page
 
merlenicholson
post Jan 3 2018, 09:48 PM
Post#9



Posts: 518
Joined: 23-November 11
From: Tampa, Florida, USA - UTC -5:00


You're right, it isn't enough information. What you have sent is what it looks like when typed onto a screen. That's a long way from identifying the characters that you can't see. For instance, if a single character or two is always between each line of information, the split function can be used to create an array so that each row of the array has useful info. The tracking number itself would be in the row after the row that contained "Tracking number:". Or since we don't know the content of that blank line between the two, maybe there is an intervening row. Need more info.

The other approach is to use instr() to search for the string "Tracking number:" since instr() gives the beginning position, let's call it 200, then the end plus one is 217. Add to that the number of characters between the colon and the beginning digit of the tracking number and you have the start position of the number itself. With the beginning position and the length of the tracking number the mid() function has it. Too much missing info so far.

My approach is usual to do a hex representation of the characters to see what the line break characters are, figure out if the split function works best and especially see what the unseen intervening characters look like. That's where you start.

The coding is very simple vb, just a lot of it. But you need more, much more info.
This post has been edited by merlenicholson: Jan 3 2018, 09:49 PM

--------------------
Merle Nicholson
Ninety percent of all mental errors are in your head. - Yogi Berra -
Go to the top of the page
 
doctor9
post Jan 4 2018, 09:37 AM
Post#10


UtterAccess Editor
Posts: 17,984
Joined: 29-March 05
From: Wisconsin


chris1970,

> I apologise if I am not giving enough info but this thing is driving me nuts.

I get that, but you just need to take a breath and re-read our questions. You didn't include any info about your fields, like their datatype. If the two Electronic Shipping details are date fields, that's one thing, but if they're text, that's another. Is there more than one field for each of these details? That would make sense since there's date, time, city and state info for both of them.

When you say "the most recent activity" that also includes a date, a time, a city and a state. Do you want just the time? Do you want just the date and time? Or do you also want the city and state?

Hope this helps,

Dennis

EDIT: Here's a basic bit of code that will extract various details into variables. At the end, where I have the Debug.Print stuff, you can add code that takes these values and writes them to a new record in your table. I couldn't write that bit because you didn't provide any info about your table/fields.

CODE
Public Sub ParseTrackingInfo(ByVal strInput As String)

'- Ship to Address
'- Electronic Shipping Info Received
'- Electronic Shipping Sent to USPS
'- The most recent activity (in this case "Delivered at..."
'- Tracking number

    Dim strShipToAddress As String, dteInfoReceived As Date
    Dim dteShippingSentDate As Date
    Dim dteMostRecentActivity As Date, strMostRecentActivity As String
    Dim strTrackingNumber As String

    Dim strArray() As String, i As Integer, strTemp As String

'   Clean up string:

'   Reduce all consecutive spaces to single spaces
    While InStr(1, strInput, "  ") > 0
        strInput = Replace(strInput, "  ", " ")
    Wend
    
'   Reduce all consecutive carriage returns to single carriage returns
    While InStr(1, strInput, vbCrLf & vbCrLf) > 0
        strInput = Replace(strInput, vbCrLf & vbCrLf, vbCrLf)
    Wend

'   Split the multiple lines into separate strings
    strArray = Split(strInput, vbCrLf)
    
'   Loop through the array of strings (one string for each line of text).
    For i = 0 To UBound(strArray)
        
        If InStr(1, strArray(i), "Tracking number:") > 0 Then
'           If this line says "Tracking number:", then the next line is the tracking number
            strTrackingNumber = strArray(i + 1)
        End If
        
        If InStr(1, strArray(i), "Ship to address:") > 0 Then
'           If this line says "Ship to address:" then the next line is the ship to address
            strShipToAddress = strArray(i + 1)
        End If

        If InStr(1, strArray(i), "Electronic Shipping Info Received") > 0 Then
'           Grab the previous line of text and strip it down to just portion that can be converted to a date value
            strTemp = Mid(strArray(i - 1), InStr(1, strArray(i - 1), ",") + 1) '"Tuesday, November 21st:" -> " November 21st:"
            strTemp = Trim(Replace(strTemp, ":", ""))                          '" November 21st:" -> "November 21st"
            strTemp = Left(strTemp, Len(strTemp) - 2)                          '"November 21st" -> "November 21"
'           Convert the text from the previous line to a date, and add the time element from THIS line to that date
            dteInfoReceived = CDate(strTemp) + CDate(Mid(strArray(i), InStr(1, strArray(i), "at ") + 3)) '"Delivered at 2:07 PM" -> #2:07 PM#
'           If the calculated date hasn't occurred yet, it must be from last year.
            If dteInfoReceived > Date Then dteInfoReceived = DateAdd("yyyy", -1, dteInfoReceived)
        End If
                
'       Same strategy as the previous IF statement
        If InStr(1, strArray(i), "Electronic Shipping Sent to USPS") > 0 Then
            strTemp = Mid(strArray(i - 1), InStr(1, strArray(i - 1), ",") + 1) '"Tuesday, November 21st:" -> " November 21st:"
            strTemp = Trim(Replace(strTemp, ":", ""))                          '" November 21st:" -> "November 21st"
            strTemp = Left(strTemp, Len(strTemp) - 2)                          '"November 21st" -> "November 21"
            dteShippingSentDate = CDate(strTemp) + CDate(Mid(strArray(i), InStr(1, strArray(i), "at ") + 3)) '"Delivered at 2:07 PM" -> #2:07 PM#
            If dteShippingSentDate > Date Then dteShippingSentDate = DateAdd("yyyy", -1, dteShippingSentDate)
        End If
        
'       Same strategy again, only this time we're also grabbing the city and state as a single string.
        If InStr(1, strArray(i), "Tracking Details") > 0 Then
            strTemp = Mid(strArray(i + 1), InStr(1, strArray(i + 1), ",") + 1) '"Tuesday, November 21st:" -> " November 21st:"
            strTemp = Trim(Replace(strTemp, ":", ""))                          '" November 21st:" -> "November 21st"
            strTemp = Left(strTemp, Len(strTemp) - 2)                          '"November 21st" -> "November 21"
            dteMostRecentActivity = CDate(strTemp) + CDate(Mid(strArray(i + 2), InStr(1, strArray(i + 2), "at ") + 3)) '"Delivered at 2:07 PM" -> #2:07 PM#
            If dteMostRecentActivity > Date Then dteMostRecentActivity = DateAdd("yyyy", -1, dteMostRecentActivity)
            strMostRecentActivity = strArray(i + 3)
        End If
        
    Next i
    
'   Display what we found in the Immediate Window
    If strTrackingNumber <> "" Then
        Debug.Print "Tracking Number: " & strTrackingNumber
        Debug.Print "Ship To Address: " & strShipToAddress
        Debug.Print "Date Info Received: " & dteInfoReceived
        Debug.Print "Date Shipping Sent: " & dteShippingSentDate
        Debug.Print "Most Recent Activity date: " & dteMostRecentActivity
        Debug.Print "Most Recent Activity location: " & strMostRecentActivity
    Else
        Debug.Print "Tracking Number Not Found." & vbCrLf
    End If
    
End Sub


Syntax:

CODE
ParseTrackingInfo readText


Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
chris1970
post Jan 13 2018, 06:20 PM
Post#11



Posts: 6
Joined: 15-November 17



Hi All,

Thank you so much for your responses and sugestions. Doctor9, you are a genious, you got what I was asking and gave me an answer that I could both understand and adapt to other projects that I have going on.

I have a terrible habit (unfortunately) of dwelling on a very small area of an overall project and seem to miss the bigger picture. I can see exactly what I want to achieve but taken out of context may mean very little to anyone else.

I always know what I want to say and then don't say it.

I can work with the code you have provided and make it work for what I want.

You are waiting for the BUT? (and there always is one)...

The code that identifies the year may not work after April/May of this year as we started with our provider at this time last year. Do you have any suggestions as to a way I can identify the year by simply providing the day and the month?

Thank you again. I am forever in your debt.
Go to the top of the page
 
doctor9
post Yesterday, 09:17 AM
Post#12


UtterAccess Editor
Posts: 17,984
Joined: 29-March 05
From: Wisconsin


Chris1970,

> The code that identifies the year may not work after April/May of this year as we started with our provider at this time last year.

Can you explain why you think this? What happens in April or May that would change how the code works? Right now the basic idea is this: It takes the day and month provided by the string, and tacks on the current year. If that date is AFTER today, it subtracts a year.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th January 2018 - 06:44 AM