Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 7th, 2006, 05:20 PM
Registered User
 
Join Date: Feb 2006
Location: hillsboro, Ohio, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to medix_911
Default Parsing a text string

Hello Folks,

I can't seem to get my mind around this one for some reason. I'm writing a custom package for the mobile auction industry and thought it would be cool if when they try to register a new bidder, they just use a magnetic card reader and swipe the drivers license. There in lies the problem. The data is ju,bled together and there is no common delimiter. Any help would be greatly appreciated. I'll include the text string that comes from the mag reader with this post.

%OHHILLSBORO^JACKMAN$BRANDEN$W^224$WOODLAND$DR?;63 60231810302872=000060619190612?#TS45133000000DA000 00M073200BROBLURJ111111000000000000000000?

The blue is full name
The brown is street address
The teal is the driver's license number
The orange is the postal zip code

In the form, i have a memo field to hold this string, then want to split those parts out and put them in their respective fields. Hopefully this makes sense. If anymore info is needed, please don't hesitate to ask. I'm going to keep working on this myself and maybe i'll figure it out on my own.

Branden
Reply With Quote
  #2 (permalink)  
Old February 7th, 2006, 05:50 PM
Friend of Wrox
 
Join Date: Dec 2005
Location: , , .
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

From the look of the string, there are a few delimiters you can pull out. The easiest way is probably to set up variables, parse the string entering the values into those variables, and then assign them to the field values. The only ones I can't see an easy way to pull out are the license number and zip code, unless "#TS" is a common delimiter for the zip. Here's a pseudocode, I'll leave it to you to look up the exact functions and syntax.

varState = substring(inputText, 1, 2)
delim = FindFirst(inputText, '^', 0)
varTown = substring(inputText, 3, delim)
temp = delim
delim = FindFirst(inputText, '$', temp)
temp = delim
delim = FindFirst(inputText, '$', temp)
varLastName = substring(inputText, temp, delim)
temp = delim
delim = FindFirst(inputText, '$', temp)
varFirstName = substring(inputText, temp, delim)
varMI = inputText[delim+1]
temp = delim
delim = FindFirst(inputText, '^', temp)
temp = delim
delim = FindFirst(inputText, '?', temp)
varAddress = substring(inputText, temp, delim)

Reply With Quote
  #3 (permalink)  
Old February 7th, 2006, 06:06 PM
Registered User
 
Join Date: Feb 2006
Location: hillsboro, Ohio, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to medix_911
Default

Is there a specific library i need to have installed to get the substring to work, i can't find any references on it

Branden
Reply With Quote
  #4 (permalink)  
Old February 8th, 2006, 09:46 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Kindler, FindFirst() and Substring() are not Access functions. Did you create those?

Branden, look up help for functions Left(), Right(), Mid(), and InStr(). A combo of those will help you parse this.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
  #5 (permalink)  
Old February 8th, 2006, 10:05 AM
Registered User
 
Join Date: Feb 2006
Location: hillsboro, Ohio, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to medix_911
Default

I also posted this on the message boards at Utter Access and have since found a solution with the help of an individual named ace. He made it look simple and me look stupid. Thanks for all the help though. If anyone would like to see the code from the completed forms, let me know, it ended up being a couple modules and then the code for the after_update event to fire it off in the form.

Branden
Reply With Quote
  #6 (permalink)  
Old February 8th, 2006, 12:19 PM
Friend of Wrox
 
Join Date: Dec 2005
Location: , , .
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Serrano: FindFirst() actually is an Access function.. it just doesn't do what I thought. I meant to be using InStr(temp, inputText, searchChar) instead. Substring was just a pseudocode interpretation since I couldn't remember the exact syntax for Mid() and it's a pretty common function in other languages. I didn't actually create the function although.. it would be relatively easy to define using Mid().

Reply With Quote
  #7 (permalink)  
Old February 8th, 2006, 12:32 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Yes, I can understand when using many programs that the syntax is different for all. Heh, heh...

To clarify using Access terms, FindFirst is actually a method (with an action), not a function that is called with arguments like a procedure. For example,

Dim rs as Object
Set rs = Me.RecordSetClone
rs.FindFirst "[strState] = 'Michigan'"

vs

strState = FindFirst("Michigan")

The latter is a function (which doesn't exist in Access unless you create it). The former is using the FindFirst method which Access supports assuming you have the correct library referenced.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
  #8 (permalink)  
Old February 8th, 2006, 01:05 PM
Registered User
 
Join Date: Feb 2006
Location: hillsboro, Ohio, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to medix_911
Default

In all actuality fellas, the left$, right& and mid$ methods were somewhat helpful but in of themselves would not work simply for the fact that the text is dynamic, no 2 peoples address or name are the same length. The end result used limited left and mid methods but the 2 most useful mesthods were instr and split. But thanks for your help none the less.

Branden
Reply With Quote
  #9 (permalink)  
Old November 21st, 2006, 01:37 PM
Authorized User
 
Join Date: Oct 2006
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to DeMoNN
Default

hey branden

paste like 10 to 20 records here and ill look for a way to get it for u

I was here but now I'm gone.
I leave this messege to be carried on.
Whoever knew me, knew me well.
Whoever didn't, go to hell.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Parsing from a text string jroxit Classic ASP Basics 5 November 18th, 2008 05:08 PM
Parsing of String jmss66 VB How-To 9 May 1st, 2008 12:47 AM
Parsing a String? MBowen SQL Server 2000 9 December 1st, 2006 01:17 PM
Parsing Large Delimited Text String Coop64 SQL Language 1 February 16th, 2004 03:19 PM
Parsing a string of numbers tp194 Classic ASP Databases 1 January 5th, 2004 10:28 AM



All times are GMT -4. The time now is 08:50 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.